Home » SQL & PL/SQL » SQL & PL/SQL » put results of dynamic sql into pl/sql statements (oracle 10g)
put results of dynamic sql into pl/sql statements [message #430323] Tue, 10 November 2009 01:45 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Hi.
I tried to "google up" solution to my problem but in vain, so if possible could you give me a hint?
The test code which I will paste here is somewhat akward, it is part of bigger code and I tried to simplify and adjust it.

The problem is:
I have sql statement which I construct on the run, and I want to put results of this statement into collection.
With present code I get "ORA-00932: inconsistent datatypes".

The collection must be global, since later on I need to do selects from this collection using:
select col_1, col_2, col_3 from table(cast(v_pole_final as t_pole)))

And I do not want put constructors into sql statements (which i believe would help).

The select will always return columns types varchar, number, number.
I.e. the same structure as in type "t_zaznam".

I want to use "execute immediate", or "ref cursor - fetch bulk collect into collection".

Thanks for help.
Jan

My code:
create or replace type t_zaznam is object (col_1 varchar2(64), col_2 number, col_3 number);
create or replace type t_pole is table of t_zaznam;


create or replace function sulc_experim return number
is
  s_sql_temp varchar2(6000):= 'select ''asdf asdf'', 7, 777 from dual '||
                             ' union '||
                             ' select ''yes boss'', 1, 111 from dual'||
                             ' union '||
                             ' select ''go home'', 9, 999 from dual';                                            

  v_pole       t_pole:= t_pole();
  v_pole_final t_pole:= t_pole();

begin

  execute immediate s_sql_temp into v_pole;

  return 0;
  
end sulc_experim;

Re: put results of dynamic sql into pl/sql statements [message #430343 is a reply to message #430323] Tue, 10 November 2009 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace type t_zaznam is object (col_1 varchar2(64), col_2 number, col_3 number);
  2  /

Type created.

SQL> create or replace type t_pole is table of t_zaznam;
  2  /

Type created.

SQL> create or replace function sulc_experim return number
  2  is
  3    s_sql_temp varchar2(6000):= 'select ''asdf asdf'', 7, 777 from dual '||
  4                               ' union '||
  5                               ' select ''yes boss'', 1, 111 from dual'||
  6                               ' union '||
  7                               ' select ''go home'', 9, 999 from dual';                                           
  8  
  9    v_pole       t_pole:= t_pole();
 10    v_pole_final t_pole:= t_pole();
 11  
 12  begin
 13  
 14    execute immediate s_sql_temp into v_pole;
 15  
 16    return 0;
 17    
 18  end sulc_experim;
 19  /

Function created.

SQL> declare i integer; begin i := sulc_experim; end;
  2  /
declare i integer; begin i := sulc_experim; end;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MICHEL.SULC_EXPERIM", line 14
ORA-06512: at line 1

3 modifications:
SQL> create or replace function sulc_experim return number
  2  is
  3    s_sql_temp varchar2(6000):= 'select t_zaznam (''asdf asdf'', 7, 777) from dual '||
  4                               ' union all'||
  5                               ' select t_zaznam (''yes boss'', 1, 111) from dual'||
  6                               ' union all'||
  7                               ' select t_zaznam (''go home'', 9, 999) from dual';             
  8  
  9    v_pole       t_pole:= t_pole();
 10    v_pole_final t_pole:= t_pole();
 11  
 12  begin
 13  
 14    execute immediate s_sql_temp bulk collect into v_pole;
 15  
 16    return 0;
 17    
 18  end sulc_experim;
 19  /

Function created.

SQL> declare i integer; begin i := sulc_experim; end;
  2  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: put results of dynamic sql into pl/sql statements [message #430344 is a reply to message #430343] Tue, 10 November 2009 02:58 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Michel Can you please explain Why?? or give some links to the documentation
Also both queries are not fulfilling the same functionality as with replacing UNION with UNION ALL

[Updated on: Tue, 10 November 2009 03:00]

Report message to a moderator

Re: put results of dynamic sql into pl/sql statements [message #430350 is a reply to message #430344] Tue, 10 November 2009 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why? The query result must be of the same type of the variable
2/ UNION ALL? because as I return t_zaznam data and this type has no ORDER or MAP method it does not support the sort UNION implies.

Regards
Michel
Re: put results of dynamic sql into pl/sql statements [message #430352 is a reply to message #430344] Tue, 10 November 2009 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If having the UNIONs is important, you can rewrite the query as:
  s_sql_temp varchar2(6000):= 'select t_zaznam (col_1,col_2,col_3) from ('||
                             'select ''asdf asdf'' col_1, 7 col_2, 777 col_3 from dual '||
                             ' union '||
                             ' select ''yes boss'', 1, 111 from dual'||
                             ' union '||
                             ' select ''go home'', 9, 999 from dual)';                                            


The explanation is as follows:
1) To return values into a table type, you need to use BULK COLLECT.

2) To BULK COLLECT values into a table, the type of the values you select needs to match the table type

3) If you wrap t_zaznam calls around the individual SELECT statements (as in Michel's example) then you can't use UNION, as this requires the ability to tell whether two values are the same, which your Type does notprovide a method for. Thus you need to use UNION ALL
icon14.gif  Re: put results of dynamic sql into pl/sql statements [message #430364 is a reply to message #430352] Tue, 10 November 2009 03:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Jrowbottom and Michel
Re: put results of dynamic sql into pl/sql statements [message #430368 is a reply to message #430343] Tue, 10 November 2009 03:42 Go to previous message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Thank you very much Michel - it works great.

Ad UNION vs UNION ALL:
I could use UNION, because I declared:
alter type t_zaznam add map member function m return number invalidate;

Sorry I did not mention it. A thank you - I took this "alter type" statement as a "magic" which makes it work - now I understand what it serves for.

Thanks once more,
have a nice day, jan
Previous Topic: Error while opening file(ORA-22288)
Next Topic: concatenate result strings
Goto Forum:
  


Current Time: Sun Sep 25 21:30:01 CDT 2016

Total time taken to generate the page: 0.07572 seconds