Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic sql
Dynamic sql [message #299705] Wed, 13 February 2008 00:10 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,

Am having a list which is in parameter.I want to select some value from the list depends upon some condition.
The column which am selecting should be dynamic.How to execute my req.Kindly advice.Here with am attaching the code.

create or replace type weo_rec_strings10 as object
(
stringval1 varchar2(1000),
stringval2 varchar2(1000),
stringval3 varchar2(1000),
stringval4 varchar2(1000),
stringval5 varchar2(1000),
);
/
create or replace type weo_rec_strings10_list as table of weo_rec_strings10 ;
/
declare
p_list weo_rec_strings10_List;
p_obj weo_rec_strings10;
var number default 1;
gen_var varchar2(100);
begin
p_obj :=weo_rec_strings10(null,NULL,NULL,NULL,NULL);
p_list :=weo_rec_strings10_List();
p_list.extend;
p_list(1):=weo_rec_strings10('Oracle','C','C++',NULL,NULL);
if var=1 then
gen_var :='stringval1';
end if;
execute immediate 'select ' || gen_var || ' from table(p_list)' ;
-- -- dbms_output.put_line(p_obj.stringval3);
END;

But this is not working.Kindly advise.


Re: Dynamic sql [message #299716 is a reply to message #299705] Wed, 13 February 2008 00:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Just dynamically executing a select statement doesn't do anything. You need to either select it into something or dynamically open a ref cursor for it or some such thing. Also, you need to either concatenate or bind the p_list. Binding is much better. Please see the example below.

SCOTT@orcl_11g>  create or replace type weo_rec_strings10 as object
  2  (
  3  stringval1 varchar2(1000),
  4  stringval2 varchar2(1000),
  5  stringval3 varchar2(1000),
  6  stringval4 varchar2(1000),
  7  stringval5 varchar2(1000)
  8  );
  9  /

Type created.

SCOTT@orcl_11g> create or replace type weo_rec_strings10_list as table of weo_rec_strings10 ;
  2  /

Type created.

SCOTT@orcl_11g> variable g_ref refcursor
SCOTT@orcl_11g> declare
  2    p_list	weo_rec_strings10_List;
  3    p_obj	weo_rec_strings10;
  4    var	number default 1;
  5    gen_var	varchar2(100);
  6  begin
  7    p_obj := weo_rec_strings10 (null,NULL,NULL,NULL,NULL);
  8    p_list := weo_rec_strings10_List();
  9    p_list.extend;
 10    p_list(1) := weo_rec_strings10 ('Oracle','C','C++',NULL,NULL);
 11    if var = 1 then
 12  	 gen_var := 'stringval1';
 13    end if;
 14    open :g_ref for 'select ' || gen_var || ' from table(:b_list)' using p_list;
 15    dbms_output.put_line('value of p_list(1).stringval3 is:	' || p_list(1).stringval3);
 16  END;
 17  /
value of p_list(1).stringval3 is:  C++

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> print g_ref

STRINGVAL1
--------------------------------------------------------------------------------
Oracle

SCOTT@orcl_11g> 


Re: Dynamic sql [message #299726 is a reply to message #299716] Wed, 13 February 2008 00:49 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Thank you very much.Its working fine.
Re: Dynamic sql [message #299733 is a reply to message #299705] Wed, 13 February 2008 01:00 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 13 February 2008 01:00]

Report message to a moderator

Previous Topic: ORA-00907: missing right parenthesis
Next Topic: Stored Procedure Output Advice?
Goto Forum:
  


Current Time: Sat Dec 10 13:07:03 CST 2016

Total time taken to generate the page: 0.09269 seconds