OraFAQ Forum: SQL & PL/SQL » execute immediate results in an ORA-00936 error

Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate results in an ORA-00936 error (Oracle Enterprise Edition, 11.2.0.3.4, Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

execute immediate results in an ORA-00936 error [message #645341]

Thu, 03 December 2015 14:42

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

I've searched the forum for this and didn't find anything that helped so I'm posting.

I've got this PL/SQL that uses dynamic SQL - running it produces an ORA-00936.
If I do the same command without dynamic SQL it works. I just don't understand why the dynamic SQL doesn't work.

Code: [Select all] [Show/ hide]

set serveroutput on size unlimited

declare
  v_sql         varchar2(2000);
begin

  v_sql := 'select * from DMASS38_4.R_PROJECT_TYPE where PROJECT_TYPE = ''39413'' ';
  execute immediate v_sql into pull_data.pv_r_project_type;

  pull_data.pv_r_project_type.project_type := 'C'||pull_data.pv_r_project_type.project_type;
  dbms_output.put_line('value is: '||pull_data.pv_r_project_type.long_name);

  --
  -- THIS PRODUCES ORA-00936
  --
  v_sql := 'update DMASS38_99.r_project_type set row = pull_data.pv_r_project_type where PROJECT_TYPE = ''C39413'' ';
  execute immediate v_sql;

  --
  -- THIS WORKS
  --
  --update dmass38_99.r_project_type
  --set row = pull_data.pv_r_project_type
  --where project_type = 'C39413';

end;
/

Any thoughts?

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645344 is a reply to message #645341]

Thu, 03 December 2015 15:25

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I just don't understand why the dynamic SQL doesn't work.

Because the dynamic SQL text is wrong in the context it is executed.

Use SQL*Plus and copy and paste your session, the WHOLE session.
Provide a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Note: I don't see any reason to use a "execute immediate" for such a statement.

[Updated on: Thu, 03 December 2015 15:27]

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645345 is a reply to message #645341]

Thu, 03 December 2015 15:28

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

You'll have to point out where it's wrong because I don't see it, sorry.

EDIT... I just saw your edited post.
The whole thing is part of a dynamic update mechanism - I just boiled down to its simplest form what isn't working.
I'll stitch together a working/not-working example and post it shortly.

[Updated on: Thu, 03 December 2015 15:30]

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645347 is a reply to message #645345]

Thu, 03 December 2015 15:46

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

Here's a test case...

Code: [Select all] [Show/ hide]

drop table junk1;
drop table junk2;

create table junk1
(fld1   varchar2(20),
 fld2   varchar2(20));

insert into junk1 (fld1, fld2) values ('A','AAA');
insert into junk1 (fld1, fld2) values ('B','BBB');

create table junk2 as select * from junk1;

update junk2 set fld1 = 'C'||fld1;

update junk1 set fld2 = 'AAAA' where fld1 = 'A';

commit;

select * from junk1;
select * from junk2;

create or replace package testcase
is
  pv_junk1      junk1%rowtype;
end testcase;
/

set lines 200
set serveroutput on size unlimited
declare
  v_sql         varchar2(2000);
begin
  v_sql := 'select * from junk1 where fld1 = ''A'' ';
  execute immediate v_sql into testcase.pv_junk1;
  dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);

  testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
  dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);

  v_sql := 'update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1';
  dbms_output.put_line('v_sql is: '||v_sql);
  execute immediate v_sql;

end;
/

And the result...

Code: [Select all] [Show/ hide]

dbdev/oracle/KELDEV11> sqlplus / @testcase

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 3 14:45:15 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE@KELDEV11>
ORACLE@KELDEV11> drop table junk1;

Table dropped.

ORACLE@KELDEV11> drop table junk2;

Table dropped.

ORACLE@KELDEV11>
ORACLE@KELDEV11> create table junk1
  2  (fld1      varchar2(20),
  3   fld2      varchar2(20));

Table created.

ORACLE@KELDEV11>
ORACLE@KELDEV11> insert into junk1 (fld1, fld2) values ('A','AAA');

1 row created.

ORACLE@KELDEV11> insert into junk1 (fld1, fld2) values ('B','BBB');

1 row created.

ORACLE@KELDEV11>
ORACLE@KELDEV11> create table junk2 as select * from junk1;

Table created.

ORACLE@KELDEV11>
ORACLE@KELDEV11> update junk2 set fld1 = 'C'||fld1;

2 rows updated.

ORACLE@KELDEV11>
ORACLE@KELDEV11> update junk1 set fld2 = 'AAAA' where fld1 = 'A';

1 row updated.

ORACLE@KELDEV11>
ORACLE@KELDEV11> commit;

Commit complete.

ORACLE@KELDEV11>
ORACLE@KELDEV11> select * from junk1;

FLD1                 FLD2
-------------------- --------------------
A                    AAAA
B                    BBB

ORACLE@KELDEV11> select * from junk2;

FLD1                 FLD2
-------------------- --------------------
CA                   AAA
CB                   BBB

ORACLE@KELDEV11>
ORACLE@KELDEV11> create or replace package testcase
  2  is
  3    pv_junk1 junk1%rowtype;
  4  end testcase;
  5  /

Package created.

ORACLE@KELDEV11>
ORACLE@KELDEV11> set lines 200
ORACLE@KELDEV11> set serveroutput on size unlimited
ORACLE@KELDEV11> declare
  2    v_sql         varchar2(2000);
  3  begin
  4    v_sql := 'select * from junk1 where fld1 = ''A'' ';
  5    execute immediate v_sql into testcase.pv_junk1;
  6    dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  7    dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);
  8
  9    testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
 10    dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);
 11
 12
 13    v_sql := 'update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1';
 14    dbms_output.put_line('v_sql is: '||v_sql);
 15    execute immediate v_sql;
 16
 17  end;
 18  /
testcase.pv_junk1.fld1 is: A
testcase.pv_junk1.fld2 is: AAAA
testcase.pv_junk1.fld1 (now) is: CA
v_sql is: update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1
declare
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at line 15

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645348 is a reply to message #645347]

Thu, 03 December 2015 16:31

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

I've figured out how to make this work.
It appears to be a 'state' problem of some kind? because if I do everything in one dynamic SQL statement it works.

Code: [Select all] [Show/ hide]

declare
  v_sql         varchar2(2000);
  v_sep         varchar2(2):=chr(10);
begin

/*
  v_sql := 'select * from junk1 where fld1 = ''A'' ';
  execute immediate v_sql into testcase.pv_junk1;
  dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);

  testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
  dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);

  v_sql := 'update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1';
  dbms_output.put_line('v_sql is: '||v_sql);
  execute immediate v_sql;
*/

  v_sql := 'begin '||v_sep||
           '  select * into testcase.pv_junk1 from junk1 where fld1 = ''A'' ;'||v_sep||
           '  testcase.pv_junk1.fld1 := ''C''||testcase.pv_junk1.fld1; '||v_sep||
           '  update junk2 '||v_sep||
           '  set row = testcase.pv_junk1 '||v_sep||
           '  where fld1 = testcase.pv_junk1.fld1; '||v_sep||
           'end;';

  dbms_output.put_line('v_sql is: '||v_sql);

  execute immediate v_sql;

end;
/

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645357 is a reply to message #645348]

Fri, 04 December 2015 05:16

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Can I ask, why are you using Dynamic SQL here? From what I can see, you don't need to.

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645361 is a reply to message #645348]

Fri, 04 December 2015 06:31

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

markhooper99 wrote on Thu, 03 December 2015 17:31

It appears to be a 'state' problem of some kind?

Dynamic SQL is executed is a separate context. It has no idea about PL/SQL variables declared in calling context. That's why

Code: [Select all] [Show/ hide]

  v_sql := 'update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1';
  execute immediate v_sql;

fails. For the where clause you could use bind variable:

Code: [Select all] [Show/ hide]

  v_sql := 'update junk2 set ... where fld1 = :1';
  execute immediate v_sql using testcase.pv_junk1.fld1;

But you can't use bind variable for set row:

Code: [Select all] [Show/ hide]

  v_sql := 'update junk2 set row = :1 where fld1 = :2';
  execute immediate v_sql using testcase.pv_junk1,testcase.pv_junk1.fld1;

Why? Record is PL/SQL type and can't be passed to SQL. So you have to list each record attribute:

Code: [Select all] [Show/ hide]

  v_sql := 'update junk2 set fld1 = :1,fld2 = :2 where fld1 = :3';
  execute immediate v_sql
   using testcase.pv_junk1.fld1,testcase.pv_junk1.fld2,testcase.pv_junk1.fld1;

And you should use bind variables in dynamic SQL, otherwise you increase hard parsing and shared pool content which makes any parsing more time consuming. So also change

Code: [Select all] [Show/ hide]

  v_sql := 'select * from junk1 where fld1 = ''A'' ';
  execute immediate v_sql into testcase.pv_junk1;

Code: [Select all] [Show/ hide]

  v_sql := 'select * from junk1 where fld1 = :1';
  execute immediate v_sql into testcase.pv_junk1 using 'A';

Code: [Select all] [Show/ hide]

SQL> set serveroutput on
SQL> drop table junk1 purge;

Table dropped.

SQL> drop table junk2 purge;

Table dropped.

SQL> 
SQL> create table junk1
  2  (fld1   varchar2(20),
  3   fld2   varchar2(20));

Table created.

SQL> 
SQL> insert into junk1 (fld1, fld2) values ('A','AAA');

1 row created.

SQL> insert into junk1 (fld1, fld2) values ('B','BBB');

1 row created.

SQL> 
SQL> create table junk2 as select * from junk1;

Table created.

SQL> 
SQL> update junk2 set fld1 = 'C'||fld1;

2 rows updated.

SQL> 
SQL> update junk1 set fld2 = 'AAAA' where fld1 = 'A';

1 row updated.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from junk1;

FLD1                 FLD2
-------------------- --------------------
A                    AAAA
B                    BBB

SQL> select * from junk2;

FLD1                 FLD2
-------------------- --------------------
CA                   AAA
CB                   BBB

SQL> 
SQL> create or replace package testcase
  2  is
  3    pv_junk1      junk1%rowtype;
  4  end testcase;
  5  /

Package created.

SQL> 
SQL> set lines 200
SQL> set serveroutput on size unlimited
SQL> declare
  2    v_sql         varchar2(2000);
  3  begin
  4    v_sql := 'select * from junk1 where fld1 = :1';
  5    execute immediate v_sql into testcase.pv_junk1 using 'A';
  6    dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  7    dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);
  8  
  9    testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
 10    dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);
 11  
 12    v_sql := 'update junk2 set fld1 = :1,fld2 = :2 where fld1 = :3';
 13    dbms_output.put_line('v_sql is: '|| v_sql);
 14    execute immediate v_sql
 15     using testcase.pv_junk1.fld1,testcase.pv_junk1.fld2,testcase.pv_junk1.fld1;
 16  
 17  end;
 18  /
testcase.pv_junk1.fld1 is: A
testcase.pv_junk1.fld2 is: AAAA
testcase.pv_junk1.fld1 (now) is: CA
v_sql is: update junk2 set fld1 = :1,fld2 = :2 where fld1 = :3

PL/SQL procedure successfully completed.

SQL> select  *
  2    from  junk2
  3  /

FLD1                 FLD2
-------------------- --------------------
CA                   AAAA
CB                   BBB

SQL>

SY.

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645367 is a reply to message #645361]

Fri, 04 December 2015 07:07

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Or, simply:

Code: [Select all] [Show/ hide]

declare
  v_sql         varchar2(2000);
begin

  select * 
  into testcase.pv_junk1
  from junk1 where fld1 = 'A';
  dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);
  
  testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
  dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);
 
  update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1;

end;
/

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645372 is a reply to message #645367]

Fri, 04 December 2015 08:52

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

Thanks for the responses - I appreciate the input.

For those who asked, please understand that the test case code is an oversimplified example. The real issue has to do with a complex database-to-database ETL/synchronization process. Essentially, it's one-way replication with ETL during the replication. There are hundreds of tables in the mix - as such, I was looking for something dynamic so I didn't have to hard-code anything.

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645381 is a reply to message #645372]

Fri, 04 December 2015 09:41

markhooper99
Messages: 19
Registered: October 2013
Location: Calgary

Junior Member

Update... if I make the last 'update' a PL/SQL block it works...

Code: [Select all] [Show/ hide]

drop table junk1;
drop table junk2;

create table junk1
(fld1   varchar2(20),
 fld2   varchar2(20));

insert into junk1 (fld1, fld2) values ('A','AAA');
insert into junk1 (fld1, fld2) values ('B','BBB');

create table junk2 as select * from junk1;
update junk2 set fld1 = 'C'||fld1;
update junk1 set fld2 = 'AAAA' where fld1 = 'A';
commit;

select * from junk1;
select * from junk2;

create or replace package testcase
is
  pv_junk1      junk1%rowtype;
end testcase;
/

set lines 200
set serveroutput on size unlimited
declare
  v_sql         varchar2(2000);
begin
  v_sql := 'select * from junk1 where fld1 = ''A'' ';
  execute immediate v_sql into testcase.pv_junk1;
  dbms_output.put_line('testcase.pv_junk1.fld1 is: '||testcase.pv_junk1.fld1);
  dbms_output.put_line('testcase.pv_junk1.fld2 is: '||testcase.pv_junk1.fld2);

  testcase.pv_junk1.fld1 := 'C'||testcase.pv_junk1.fld1;
  dbms_output.put_line('testcase.pv_junk1.fld1 (now) is: '||testcase.pv_junk1.fld1);

  v_sql := 'begin update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1; end;';
  dbms_output.put_line('v_sql is: '||v_sql);
  execute immediate v_sql;

end;
/

The results...

Code: [Select all] [Show/ hide]

dbdev/oracle/KELDEV11> sqlplus / @testcase2

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 4 08:38:49 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Table dropped.


Table dropped.


Table created.


1 row created.


1 row created.


Table created.


2 rows updated.


1 row updated.


Commit complete.


FLD1                 FLD2
-------------------- --------------------
A                    AAAA
B                    BBB


FLD1                 FLD2
-------------------- --------------------
CA                   AAA
CB                   BBB


Package created.

testcase.pv_junk1.fld1 is: A
testcase.pv_junk1.fld2 is: AAAA
testcase.pv_junk1.fld1 (now) is: CA
v_sql is: begin update junk2 set row = testcase.pv_junk1 where fld1 = testcase.pv_junk1.fld1; end;

PL/SQL procedure successfully completed.

ORACLE@KELDEV11> select * from junk2;

FLD1                 FLD2
-------------------- --------------------
CA                   AAAA
CB                   BBB

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645383 is a reply to message #645381]

Fri, 04 December 2015 09:57

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if I make the last 'update' a PL/SQL block it works...

This is what Solomon explained:

Quote:

Dynamic SQL is executed is a separate context. It has no idea about PL/SQL variables declared in calling context.

But if you dynamic "SQL" is actually a PL/SQL block then it is no more SQL.

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645384 is a reply to message #645383]

Fri, 04 December 2015 10:39

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Fri, 04 December 2015 10:57

But if you dynamic "SQL" is actually a PL/SQL block then it is no more SQL.

Michel, dynamic SQL is executed is a separate context regardless of what is executed dynamically - SQL or PL/SQL. The reason why OP's code worked is record pv_junk1 is package global variable, so it is known globally within the session. I overlooked that first time.
That's why any context within the session can reference testcase.pv_junk1. The only difference is package global variable can't be directly referenced in SQL.

SY.

[Updated on: Fri, 04 December 2015 10:40]

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645385 is a reply to message #645384]

Fri, 04 December 2015 11:00

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is clearer written like that than in my post.

Report message to a moderator

Re: execute immediate results in an ORA-00936 error [message #645417 is a reply to message #645372]

Sat, 05 December 2015 06:12

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

markhooper99 wrote on Fri, 04 December 2015 14:52

. The real issue has to do with a complex database-to-database ETL/synchronization process. Essentially, it's one-way replication with ETL during the replication. There are hundreds of tables in the mix - as such, I was looking for something dynamic so I didn't have to hard-code anything.

So you're parameterising table and column names? Fair play, the test case did not imply any such requirement, hence the question. Normally a Test case, even when simplified, would represent what your actually trying to do, but so long as you got your solution. Smile

Report message to a moderator

Previous Topic:	Update multiple rows with multi join
Next Topic:	Query not processing by JDBC (Hanging on the executeQuery line)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 06:17:42 CDT 2025