| Execute Immediate and SQL not properly ended. [message #219512] |
Wed, 14 February 2007 12:53  |
biohazardbill
Messages: 57
Registered: March 2006
|
Member |
|
|
I have the following inside of a loop:
execute immediate 'UPDATE test.test1
SET flag =''C''
WHERE key='''||v_key||'''';
I get the error ORA-00933: SQL command not properly ended.
I have used this type of ataement before and even refernced it to be sure but it fails.
I substituted the execute immediate with dbms_output.put_line (v_key);
and it loops through and prints the variable on the screen so its not the variable that is the problem here that I can tell.
Any ideas?
TIA
|
|
|
|
|
|
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219521 is a reply to message #219519] |
Wed, 14 February 2007 13:45  |
biohazardbill
Messages: 57
Registered: March 2006
|
Member |
|
|
| anacedent wrote on Wed, 14 February 2007 13:36 |
You're On Your Own (YOYO)!
|
As I usually am with your responses...
the only thing failing is the execute immediate as demonstarted by replacing it with a dbms output command so why bore you with the rest of the details that are working?
Any way thanks for the reply as it at least gave me some useful info for future projects, I apologize for not being as SQL savvy as you are and needing a little bit of help when my boss says to automate a process where according to everything I have read I need to use(abuse) execute immediate to have a table name as a variable.
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219522 is a reply to message #219521] |
Wed, 14 February 2007 13:49 |
biohazardbill
Messages: 57
Registered: March 2006
|
Member |
|
|
create or replace procedure test2(
v_table_name varchar2) is
type tCsr is ref cursor;
vCsr tCsr;
vSQL varchar2(2000);
v_key varchar2(17);
begin
vSQL := 'select key from common.table1';
open vCsr for vSQL;
loop
fetch vCsr into v_key;
exit when vCsr%notfound;
execute immediate 'UPDATE '||v_table_name||' SET elim_flag =''C'' WHERE key='''||v_key||''';';
end loop;
close vCsr;
End;
|
|
|
|
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219532 is a reply to message #219512] |
Wed, 14 February 2007 15:00 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| biohazardbill wrote on Wed, 14 February 2007 19:53 | execute immediate 'UPDATE test.test1
SET flag =''C''
WHERE key='''||v_key||'''';
|
| biohazardbill wrote on Wed, 14 February 2007 20:49 | execute immediate 'UPDATE '||v_table_name||' SET elim_flag =''C'' WHERE key='''||v_key||''';';
|
Your execute immediate statement somehow grew with semicolon (';') which is not allowed as stated in documentation.
Also follow the link to learn how to BIND v_key (among others it works when v_key contains '''').
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219536 is a reply to message #219532] |
Wed, 14 February 2007 15:20 |
biohazardbill
Messages: 57
Registered: March 2006
|
Member |
|
|
Thanks so much Flyboy....
the semicolon in there was a mistake on my part, however it is not like that in my procedure.
The null value is what has made my procedure fail. The source table is not supposed to have null values in this column so you have hit on another problem which I have addressed with the dbas.
Thanks so much again.
|
|
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219652 is a reply to message #219512] |
Thu, 15 February 2007 05:08 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Seems I did not pick the cause.
| biohazardbill wrote on Wed, 14 February 2007 19:53 |
I substituted the execute immediate with dbms_output.put_line (v_key);
|
If you would output the whole sql statement before running it you would easily find the one which caused your error.
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219690 is a reply to message #219512] |
Thu, 15 February 2007 09:54 |
biohazardbill
Messages: 57
Registered: March 2006
|
Member |
|
|
Thanks to everyone for their input...
I found the problem, at last.
Even after the null issue I was having problems.
There are intances in the data where the data itself contained a single quote. This threw off the sql statement as it the string for the update was not created properly
|
|
|
|
|
|
| Re: Execute Immediate and SQL not properly ended. [message #219715 is a reply to message #219690] |
Thu, 15 February 2007 11:28 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
I changed your code. Try using this instead of what you're doing currently.
CREATE OR REPLACE PROCEDURE test2(v_table_name VARCHAR2) IS
-- TYPE tcsr IS REF CURSOR; NOT NEEDED
-- vcsr tcsr; NOT_NEEDED
vsql VARCHAR2(2000);
-- v_key VARCHAR2(17); NOT NEEDED
v_elim_flag VARCHAR2 (1) := 'C';
BEGIN
-- vsql := 'select key from common.table1'; NOT NEEDED This is a static query
/* YOUR CODE
OPEN vcsr FOR vsql;
LOOP
FETCH vcsr
INTO v_key;
EXIT WHEN vcsr%NOTFOUND;
EXECUTE IMMEDIATE 'UPDATE ' || v_table_name ||
' SET elim_flag =''C'' WHERE key=''' || v_key || ''';';
END LOOP;
CLOSE vcsr;
*/
-- MY CODE
FOR r_common IN (SELECT key
FROM common.table1
)
LOOP
vsql := 'UPDATE '||v_table_name ||
' SET elim_flag = :1'||
' WHERE key = :2';
EXECUTE IMMEDIATE vsql USING v_elim_flag,r_common.key;
END LOOP;
-- END OF MY CODE
END;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
