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 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
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 #432428 is a reply to message #432425] Tue, 24 November 2009 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to DBMS_OUTPUT.PUT_LINE(v_insert_tab). You'll see how the INSERT statement looks like and you might see the culprit.
Re: ORA-00936 coming in execute immediate [message #432429 is a reply to message #432425] Tue, 24 November 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Following this the answer will immediate.

Regards
Michel
Re: ORA-00936 coming in execute immediate [message #432431 is a reply to message #432425] Tue, 24 November 2009 03:24 Go to previous messageGo to next message
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 Go to previous message
Kevin Meade
Messages: 2098
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
Previous Topic: Please hepl me Urgent
Next Topic: I want to Convert rows to columns (merged 3)
Goto Forum:
  


Current Time: Wed Sep 28 07:26:26 CDT 2016

Total time taken to generate the page: 0.07987 seconds