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)
execute immediate results in an ORA-00936 error [message #645341] Thu, 03 December 2015 14:42 Go to next message
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.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
Junior Member
Here's a test case...

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...
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3312
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

  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:

  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:

  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:

  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

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


To

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



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 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Or, simply:

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
Junior Member
Update... if I make the last 'update' a PL/SQL block it works...

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...

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3312
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jul 09 11:44:31 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.