Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936 coming in execute immediate (Oracle 9i)
| ORA-00936 coming in execute immediate [message #432425] |
Tue, 24 November 2009 03:18  |
hammad83
Messages: 46
Registered: June 2008
Location: Australia
|
Member |
|
|
Guys I'm stuck here. I run the following code and ORA-00936 Missing Expression comes up.I know it's coming in the INSERT statement, but I can't figure out why. Please reply if you know what I'm missing.
CREATE OR REPLACE PROCEDURE nbp_st_patch AS
cursor st_source is
SELECT ntn_no as "NTN",
nic_no as "NIC",
name, ltrim(rtrim(address_1))||', '||ltrim(rtrim(address_2)) as "ADDRESS",
NAME AS "BUSINESS",
NULL AS "BUSINESS_SERIAL",
REG_NO AS "SALES_TAX_NO",
N_CIRCLE AS "CIRCLE",
NULL AS "ZONEIT",
NULL AS "CIRCLEIT",
NULL AS "CATEGORY",
'A' AS "FLAG",
NULL AS "AF",
NULL AS "EMPLOYER_CATEGORY",
NULL AS "EMPLOYER_NTN"
FROM ods.gstt01
WHERE TO_CHAR(date_comm, 'YYYYMMDD') between to_char(sysdate-10,'YYYYMMDD') AND to_char(sysdate-3,'YYYYMMDD')
AND OPER_STS = '1';
tab_name varchar2(20) := null;
tab_name1 varchar2(20) := null;
tab_name2 varchar2(20) := null;
tab_name3 varchar2(20) := null;
v_create_tab varchar2(255);
v_insert_tab varchar2(1000);
v_create_tab1 varchar2(255);
v_insert_tab1 varchar2(1000);
v_ntn_src varchar2(7);
v_nic_src varchar2(15);
v_name_src varchar2(60);
v_address_src varchar2(120);
v_business_src varchar2(60);
v_business_serial_src varchar2(4);
v_sales_tax_no_src varchar2(13);
v_circle_src number(4);
v_zoneit_src number(2);
v_circleit_src number(2);
v_category_src number(2);
v_flag_src char(1);
v_af_src char(1);
v_employer_category_src varchar2(5);
v_employer_ntn_src varchar2(7);
BEGIN
tab_name1 := nbp_st_source_table(tab_name);
v_create_tab := 'create table '||tab_name1||' as select * from GSTT01_BANK_091121 where 1=2';
execute immediate v_create_tab;
open st_source;
loop
fetch st_source into v_ntn_src, v_nic_src, v_name_src, v_address_src, v_business_src,
v_business_serial_src, v_sales_tax_no_src, v_circle_src, v_zoneit_src, v_circleit_src,
v_category_src, v_flag_src, v_af_src, v_employer_category_src,v_employer_ntn_src;
exit when st_source%NOTFOUND;
v_insert_tab := 'insert into '||tab_name1||' values('||''''||v_ntn_src||''''||','||''''||
v_nic_src||''''||','||''''||v_name_src||''''||','||''''||v_address_src||''''||','||''''||
v_business_src||''''||','||''''||v_business_serial_src||''''||','||''''||
v_sales_tax_no_src||''''||','||v_circle_src||','||v_zoneit_src||','||v_circleit_src||','||
v_category_src||','||''''||v_flag_src||''''||','||''''||v_af_src||''''||','||''''||
v_employer_category_src||''''||','||''''||v_employer_ntn_src||''''||')';
execute immediate v_insert_tab;
commit;
end loop;
close st_source;
END;
|
|
|
|
|
|
|
|
| Re: ORA-00936 coming in execute immediate [message #432431 is a reply to message #432425] |
Tue, 24 November 2009 03:24  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You'd be better off creating and populating the table in one statement - it'll be much quicker.
You need to write your SQL statement to Dbms_Output and look at it to se where the missing quote or comma is
It doesn't help that you've picked an over-complicated way of handling the quotes.
Every time your code says : ||''''||','||
You could get away with : ||''','||
|
|
|
|
| Re: ORA-00936 coming in execute immediate [message #432526 is a reply to message #432425] |
Tue, 24 November 2009 16:37 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
when debugging dynamic sql, the first step is to make sure you are trying to execute a valid sql statement. Try this:
raise_application_error(-20999,v_insert_tab);
just before you execute immediate. This will raise an error and dump the insert statement so you can look at it.
This is really just an alternative to the DBMS_OUTPUT suggestion made earlier. The point being you need to see sql you are trying to execute.
Good luck, Kevin
|
|
|
|
Goto Forum:
Current Time: Sat Dec 14 13:31:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
