Home » SQL & PL/SQL » SQL & PL/SQL » Execute Immediate and SQL not properly ended.
Execute Immediate and SQL not properly ended. [message #219512] Wed, 14 February 2007 12:53 Go to next message
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 #219513 is a reply to message #219512] Wed, 14 February 2007 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Here is a free clue.
If/when you insist on abusing EXECUTE IMMEDIATE (rarely appropriate for DML), form the SQL statement in a VARCHR2 variable.
Use DBMS_OUTPUT to output the SQL, then cut & paste it into SQL*Plus BEFORE attempting to pass it to EXECUTE IMMEDIATE.
Re: Execute Immediate and SQL not properly ended. [message #219518 is a reply to message #219512] Wed, 14 February 2007 13:25 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Using your idea

UPDATE test.test1 SET flag ='C' WHERE key='32504700GRRN-JSP'

was created via dbms output

placed it in sqlplus

SQL> UPDATE test.test1 SET flag ='C' WHERE key='32504700GRRN-JSP';

0 rows updated.

so the query worked, now what could be the issue?
Re: Execute Immediate and SQL not properly ended. [message #219519 is a reply to message #219512] Wed, 14 February 2007 13:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>so the query worked, now what could be the issue?
I have NO idea because you insist on only sharing what you think is important rather than simply showing a complete cut & paste of what is actually happening.
You expect us (TINU) to debug your malformed code but yet rarely do you ever actually post the complete interaction.
I'm tired of playing your guessing games.
You're On Your Own (YOYO)!
Re: Execute Immediate and SQL not properly ended. [message #219521 is a reply to message #219519] Wed, 14 February 2007 13:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #219528 is a reply to message #219512] Wed, 14 February 2007 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I need to use(abuse) execute immediate to have a table name as a variable.
IMO, any application where tables which require DML are not known until run time should be sent to /dev/null & redesigned by someone who knows more than just how to spell "SQL".
I know such code won't scale & I wonder how it will pass SOX audit.
Anyone who uses Oracle Reserved Words as field names is only asking for ongoing problems.

  1  select keyword from V_$RESERVED_WORDS
  2  where keyword like 'K%'
  3* order by 1
SQL> /

KEYWORD
------------------------------
K
KEEP
KERBEROS
KEY
KEYS
KEYSIZE
KEY_LENGTH
KILL

8 rows selected.
Re: Execute Immediate and SQL not properly ended. [message #219529 is a reply to message #219528] Wed, 14 February 2007 14:51 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I am not using reserved words, i truncated them to generic terms instead of using real table names, columns, etc as I am not allowed to post such things on a public forum due to SOP....

just for curiosity sake though I scrolled through some of your other posts to other people and you rip on others the same as you do me so I now consider the source when reading your replys Smile

In the future if you have nothing to contribute that helps the question/situation, please refrain from posting. Thank you.
Re: Execute Immediate and SQL not properly ended. [message #219532 is a reply to message #219512] Wed, 14 February 2007 15:00 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #219636 is a reply to message #219536] Thu, 15 February 2007 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I noticed a couple of problems in the initial posting -

1) There is no space between the 'C' and the WHERE clause
2) There is an extra ' after the v_key

Re: Execute Immediate and SQL not properly ended. [message #219652 is a reply to message #219512] Thu, 15 February 2007 05:08 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #219698 is a reply to message #219690] Thu, 15 February 2007 10:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That (and performance, scalability, sql-injection) is a good reason to use bind-variables instead of pasting in a value.
Re: Execute Immediate and SQL not properly ended. [message #219715 is a reply to message #219690] Thu, 15 February 2007 11:28 Go to previous messageGo to next message
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;
Re: Execute Immediate and SQL not properly ended. [message #219737 is a reply to message #219512] Thu, 15 February 2007 15:16 Go to previous message
biohazardbill
Messages: 57
Registered: March 2006
Member
That worked awesome thanks.... makes it to where I dont have to worry about the single quotes contained in the fields and/or passed values.
Previous Topic: PLS-00103: ERROR
Next Topic: simple program taking 2 hrs to execute..plz help
Goto Forum:
  


Current Time: Sun Dec 04 04:43:59 CST 2016

Total time taken to generate the page: 0.13730 seconds