Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate? (Oracle 9i (9.2.0.7.0))
execute immediate? [message #361834] Fri, 28 November 2008 03:47 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I wonder if someone could point me in the right direction as I seem to be having a brainfart with this one.

I have a number of fields in a cursor (selected from a table) that have a number as part of their name
e.g
tl_dob_1
tl_dob_2
tl_dob_3
tl_golf_1
tl_golf_2
tl_golf_3


There are 19 seperate fields with 10 occurences of each.

I need to insert each set of fields into another table depending on the number that is passed in as a parameter (The number comes from a loop based on the number of people - so if there is 1 person you'll just get 1, if there are three people you'll get 1, then 2, then 3).

So if the parameter is 1 then I need to insert tl_dob_1 and tl_golf_1 into my table, if it is 2 then I need to insert tl_dob_2 and tl_golf_2 into my table (thereby creating 2 seperate records).

My collegue has attempted to do it with execute immediate which hasn't worked because it doesn't know what tl_golf_1 is at this point
execute immediate ('select tl_golf'||pers_num||' into :outval1 from dual') using out t_golf;

although I can see what he was trying to do.

Is there an elegant solution I can use to deal with this. I'm sure there was something similar posted a while ago but despite searching for execute immediate I couldn't find anything that matched my requirements.

Thanks in advance for any help.
My little test script that I'm working on (if its any use) is
declare

   tl_dob_1 date;
   tl_dob_2 date;
   tl_dob_3 date;

   my_var date;
   
begin

   tl_dob_1 := sysdate;
   tl_dob_2 := sysdate - 1;
   tl_dob_3 := sysdate - 2;
   
   for i in 1..3
   loop
   
      execute immediate 'select tl_dob'||i||' from dual' into my_var;
      dbms_output.put_line('Date is ' || my_var);
      
   end loop;
   
end;
Re: execute immediate? [message #361838 is a reply to message #361834] Fri, 28 November 2008 04:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No possible in an easy way. See http://www.orafaq.com/forum/m/360545/96705/#msg_360545 or http://www.orafaq.com/forum/m/361001/96705/#msg_361001.
The same problem goes with cursor variables; the only way could be "unpivotting" columns into rows: http://www.orafaq.com/wiki/UNPIVOT. However I doubt it will be as performant as the non-dynamic query.
Re: execute immediate? [message #361847 is a reply to message #361834] Fri, 28 November 2008 04:40 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Thanks for the quick reply, much appreciated.
Looks like I'll just have to get on with some typing Laughing

Re: execute immediate? [message #361851 is a reply to message #361834] Fri, 28 November 2008 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We had a question very similar to this about a week ago, and I'm afraid I've got some bad news for you - we could find no way of using dynamic sql to refer to the values that didn't still involve referencing each individual value one at a time.

Re: execute immediate? [message #361853 is a reply to message #361834] Fri, 28 November 2008 04:50 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Thanks JRowbottom, I knew I'd seen a post asking something similar, but I couldn't remember if it could be done.
Re: execute immediate? [message #361917 is a reply to message #361834] Fri, 28 November 2008 09:53 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Just as an update :-

I'm not sure I explained my scenario correctly but I have found a way to do it in my case.

Firstly I moved my record definition to the package specification, then I was able to use this command
execute immediate 'begin :out_val := tl_download.full_curs.tl_golf_'||pers_num||'; end;' using out t_golf;

to reference the variable in question (where pers_num is 1 to 10, tl_download is my package and full_curs is my record).
Re: execute immediate? [message #362016 is a reply to message #361834] Sat, 29 November 2008 23:49 Go to previous message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
scorpio_biker wrote on Fri, 28 November 2008 03:47

So if the parameter is 1 then I need to insert tl_dob_1 and tl_golf_1 into my table, if it is 2 then I need to insert tl_dob_2 and tl_golf_2 into my table (thereby creating 2 seperate records).
[/code]


Hi,
I tried with this table.
SQL> select * from test;

COL1
----------
t1_dob_1
t1_dob_2
t1_dob_3
t1_golf_1
t1_golf_2
t1_golf_3

6 rows selected.

I think you can try this.
declare 
i number;
begin
for i in 1..3
loop
insert into <table_name>
select col1 from test where substr(col1,-1)=to_char(i);
end loop;
end;


Previous Topic: What is different between Oracle regular function & oracle function(LIKE,SUBSTR,INSTR)?
Next Topic: Help regarding how to find length of chain in SQL
Goto Forum:
  


Current Time: Sun Dec 11 02:22:56 CST 2016

Total time taken to generate the page: 0.05687 seconds