Home » SQL & PL/SQL » SQL & PL/SQL » choose attribute from cursor dynamically (merged)
choose attribute from cursor dynamically (merged) [message #425554] Fri, 09 October 2009 11:29 Go to next message
bott
Messages: 5
Registered: October 2009
Junior Member
dear forum,

unfortunately, i'm anything but a pl/sql expert, but i have to solve a problem, using pl/sql - and it took me 2 days already, without success Confused

problem:
i have a table with the following columns ->
| A1 | B1 | C1 | A2 | B2 | C2 | ...
unfortunately the numbering is no joke the column names really are like: INTERNES_KNZ_1, INTERNES_KNZ_2, etc.

from this one i need to fill another table in the following way ->
| A1 | B1 | C1 |
| A2 | B2 | C2 |
...

so, i made a cursor, like the following:
cursor cur is
select distinct
A1, B1, C1, A2, B2, C2
from TABLE
where X = 123456
and Y = 100;


and instead of repeating the following insert-code for all the different attributes
for my_cursor in cur loop
A1 :=(my_cursor.A1);
B1 :=(my_cursor.B1);
C1 :=(my_cursor.C1);
insert into TABLE_NEW values (A1, B1, C1);

A2 :=(my_cursor.A2);
B2 :=(my_cursor.B2);
C2 :=(my_cursor.C2);
insert into TABLE_NEW values (A2, B2, C2);

...
end loop;

i wanted to create a loop ->

i := 2;
A := 'my_cursor.A';
B := 'my_cursor.B';
C := 'my_cursor.C';
loop
exit when i<1;

sql_stmt := 'insert into TABLE_NEW values (:1, :2, :3)';
i_string := to_char( i, '9');
Ax := A || to_char(i, '9');
Bx := B || to_char(i, '9');
Cx := C || to_char(i, '9');

execute immediate sql_stmt using Ax, Bx, Cx;

i := i-1;
end loop;

this of course doesn't work because TABLE_NEW expects varchar2 as attribute type and instead of evaluating the variables, 'A1', 'B1', 'C1', etc. is written into the table columns - even though i didn't put quotes (') around.

any ideas how i could solve this?
if it is possible to evaluate a string as a statement?
something like:
attribute := execute immediate Ax;
which doesn't work, of course.

any help would be much appreciated!!!
and please don't ask me why i really have to do this - there is a good reason for it, but explaining would be rather difficult Wink

Thanks,
Bianca


Re: choose attribute from cursor dynamically [message #425560 is a reply to message #425554] Fri, 09 October 2009 12:03 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. I would have to ask "Why do you have to to id in PL/SQL"

How about a

insert into TABLE_NEW 
select distinct A1, B1, C1
  from TABLE
 where X = 123456
   and Y = 100
union all
select distinct A2, B2, C2
  from TABLE
 where X = 123456
 and Y = 100;


And please read the Forum Guide on how to format your post.
Re: choose attribute from cursor dynamically [message #425567 is a reply to message #425560] Fri, 09 October 2009 12:07 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option, closer to what you have been trying, would be Associative Arrays.
Re: choose attribute from cursor dynamically (merged) [message #425568 is a reply to message #425554] Fri, 09 October 2009 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
have a table with the following columns ->
| A1 | B1 | C1 | A2 | B2 | C2 | ...
...
from this one i need to fill another table in the following way ->
| A1 | B1 | C1 |
| A2 | B2 | C2 |

insert all 
into table2 values (A1,B1,C1)
into table2 values (A2,B2,C2)
select A1, B1, C1, A2, B2, C2 from table1
/


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.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

And post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Fri, 09 October 2009 12:10]

Report message to a moderator

Re: choose attribute from cursor dynamically (merged) [message #425579 is a reply to message #425554] Fri, 09 October 2009 14:25 Go to previous messageGo to next message
bott
Messages: 5
Registered: October 2009
Junior Member
Thank you very much for your fast replies!
But I don't think I got my point explained very well...
I'll try again next week, when I'm at work - and then with a better format, layout and proper test cases Wink

So long, have a nice weekend Smile
Bianca
Re: choose attribute from cursor dynamically (merged) [message #425580 is a reply to message #425554] Fri, 09 October 2009 15:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, we need more of an explanation because why would you want to revert to dynamic sql when you have other alternatives that seem to do what you want just fine.

If you are not a fan of insert all, the you can go old school:

insert into t2 (a,b,c)
select case when a.c1 = 1 then a1
            when a.c1 = 2 then a2
       end
      ,case...
      ,case...
from (select 1 c1 from dual union all select 2 from dual) a
     ,select a1,b1,c1,a2,b2,c3 from t1
/

using dynamic sql should be a last resort. It is very inefficient from an overhead perspective.

Kevin
Re: choose attribute from cursor dynamically [message #425939 is a reply to message #425554] Tue, 13 October 2009 05:00 Go to previous messageGo to next message
bott
Messages: 5
Registered: October 2009
Junior Member
Dear forum,

in the attached PDF I tried to explain everything as detailed as I could and deemed necessary. Please take a minute (or two Wink) to read this and hopefully my question(s) will make more sense to you afterward.

Thank you very much!
Bianca
Re: choose attribute from cursor dynamically [message #425941 is a reply to message #425939] Tue, 13 October 2009 05:11 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Alternatively, you could follow the guidelines and post a test case here along with the required output and explanation.
Re: choose attribute from cursor dynamically [message #425962 is a reply to message #425941] Tue, 13 October 2009 07:23 Go to previous messageGo to next message
bott
Messages: 5
Registered: October 2009
Junior Member
I would have posted test cases if it were relevant.
But the whole explanation is just the theory and the context behind my question. It's not easy to understand why I'm doing what I'm doing and so I wanted to explain all that, but it would be of little use if you had all the select, create and insert statements when my basic question is:

How do I pass a value dynamically to a SQL-statement, when the value is stored in a cursor and not in a table?

For example this works:
sql_stmt := 'instert into table2values(:1, :2, :3)';
value1 := A_n
value2 := B_n
value3 := C_n
execute immediate sql_stmt using TABLE1.value1, TABLE1.value2, TABLE1.value3

But doing the same thing using a cursor doesn't work:
sql_stmt := 'instert into table2 values(:1, :2, :3)';
value1 := A_n
value2 := B_n
value3 := C_n
execute immediate sql_stmt using CURSOR1.value1, CURSOR1.value2, CURSOR1.value3

[Updated on: Tue, 13 October 2009 07:24]

Report message to a moderator

Re: choose attribute from cursor dynamically [message #425964 is a reply to message #425962] Tue, 13 October 2009 07:35 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
bott wrote on Tue, 13 October 2009 13:23
I would have posted test cases if it were relevant.
But the whole explanation is just the theory and the context behind my question. It's not easy to understand why I'm doing what I'm doing and so I wanted to explain all that, but it would be of little use if you had all the select, create and insert statements when my basic question is:

How do I pass a value dynamically to a SQL-statement, when the value is stored in a cursor and not in a table?

For example this works:
sql_stmt := 'instert into table2values(:1, :2, :3)';
value1 := A_n
value2 := B_n
value3 := C_n
execute immediate sql_stmt using TABLE1.value1, TABLE1.value2, TABLE1.value3

But doing the same thing using a cursor doesn't work:
sql_stmt := 'instert into table2 values(:1, :2, :3)';
value1 := A_n
value2 := B_n
value3 := C_n
execute immediate sql_stmt using CURSOR1.value1, CURSOR1.value2, CURSOR1.value3


Neither of those examples make any real sense. You've abreviated them to the point where it becomes impossible for us to work out what you've actually done.
Cursors don't store anything, they're basically just pointers.
Have you actually opened and fetched the cursor?

It'd help if you actually copied and pasted the code.
Re: choose attribute from cursor dynamically [message #426211 is a reply to message #425964] Wed, 14 October 2009 07:13 Go to previous messageGo to next message
bott
Messages: 5
Registered: October 2009
Junior Member
Thank you all for pointing out to me that I'm not complying with the forum guidelines - but obviously not reading the explanation I posted (PDF attachment).

But that's OK, of course, since nobody is forced to help or answer questions posted here - you all have enough work to do on your own. So, no harm taken, but I'm closing this issue, since the forum at Oracle has been helpful.
Re: choose attribute from cursor dynamically [message #426214 is a reply to message #426211] Wed, 14 October 2009 07:20 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so the final answer is?

Regards
Michel
Previous Topic: Explain Plan in pl/sql
Next Topic: Synonyms
Goto Forum:
  


Current Time: Thu Sep 29 19:15:50 CDT 2016

Total time taken to generate the page: 0.26485 seconds