Dynamic Column name in a Cursor

My table has columns named as C1,C2,C3 so on. Each department in the has values only for a specific number of columns. That is, not all columns are used by all departments. In the code below the second cursor gets all the columns associated with a department. Since the nu,ber of column is unknown I am using a variable i to concatenate it with C and get the column name.
When I compile this code I get an error 'ORA-01008: not all variables bound'
declare
i number := 0;
stmt long;
colname varchar(30);
cursor pivotc is select * from tmp_pivoted_data ;
cursor controlc (did number) is select * from tmp_control_table where deptid = did;
pivot_row tmp_pivoted_data%rowtype;
control_row tmp_control_table%rowtype;
begin
open pivotc;
loop
fetch pivotc into pivot_row;
exit when pivotc%notfound;
open controlc(pivot_row.ipd_number);
loop
i := i+1;
colname := 'pivot_row.c' || to_char(i);
fetch controlc into control_row;
exit when controlc%notfound;
stmt := 'update tmp_table set rvu=' || :x;
stmt := stmt || ' where ipd_number=' || pivot_row.ipd_number;
stmt := stmt || ' and seq_number=' || pivot_row.seq_number;
stmt := stmt || ' and cost_type=' || control_row.cost_type;
stmt := stmt || ' and category=' || control_row.category;
stmt := stmt || ' and cat_descr=' || control_row.cat_descr;
execute immediate stmt using colname;
dbms_output.put_line(stmt);
end loop;
close controlc;
i := 0;
end loop;
close pivotc;
end;
I am tryimg to update rows of a table that were pivoted into a temporary table.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 3 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Thanks for the reply.
I tried that but when I give ':x' in quotes, the string 'Pivot_row.c1', 'Pivot_row.c2', ... gets substituted, whereas I want the value of 'Pivot_row.c1' to get substituted. Then I get the error wrong datatype because the string is being inserted into the table.
I worked out another way of getting the value. I don't know if this is the best solution, but here it is-
I created a new string 'selstmt' which selects the value of of the required column into a variable.
The value in the cursor control_C.columnid is the same as the column name in the pivot_C cursor.( as I mentioned earlier this is a pivoted table)
declare
stmt long;
selstmt long;
colname varchar(30);
colvalue integer;
ipd tmp_tsi_pivoted_data.ipd_number%type;
seq tmp_tsi_pivoted_data.seq_number%type;
cursor pivotc is select ipd_number,seq_number from tmp_tsi_pivoted_data ;
cursor controlc (did number) is select * from tmp_tsi_control_table where deptid = did;
pivot_row tmp_tsi_pivoted_data%rowtype;
control_row tmp_tsi_control_table%rowtype;
begin
open pivotc;
loop
fetch pivotc into ipd, seq;
exit when pivotc%notfound;
open controlc(ipd);
loop
fetch controlc into control_row;
exit when controlc%notfound;
selstmt := 'select ' || control_row.columnid || ' from tmp_tsi_pivoted_data';
selstmt := selstmt || ' where ipd_number=' || ipd;
selstmt := selstmt || ' and seq_number=' || seq;
stmt := 'update tmp_tsi_standards_test set rvu=:x';
stmt := stmt || ' where ipd_number=' || ipd;
stmt := stmt || ' and seq_number=' || seq;
stmt := stmt || ' and cost_type=' || '''' || control_row.cost_type || '''';
stmt := stmt || ' and category=' || '''' || control_row.category || '''';
stmt := stmt || ' and costtype_cat_descr=' || '''' || control_row.cat_descr || '''';
execute immediate selstmt into colvalue;
execute immediate stmt using colvalue;
end loop;
close controlc;
end loop;
close pivotc;
end;
This code runs for 4.5 minutes( there are 17000 recors in the test table-more to come in production.) If anyone knows a better way to do this, please let me know.

Hi Kmarur,
If it comes to performance, then you should always consider
the fact that dynamic sql (using "execute immediate") is
extremely slow, because each statement is parsed on each
execution.
Therefore, try to use it only when you have no other choice.
As I see the problem, the COLUMN NAME whose value you want
to put in the :x bind variable is really unknown at compile time, so you should use some form of dynamic sql to get this value.
This is the select that you use in your last solution.
But in the UPDATE statement, you DO NOT need to use
dynamic sql.
The update statement itself is static, I mean, all the
COLUMN NAMES used in the statement are known at compile
time, so you may use a static sql statement, with several
bind variables for both your :x AND for the variables
used in the WHERE clause.
At most, what you may need to do is to take care of eventual data conversions on the value corresponding to
your :x, but, since you always put this value into the RVU
column, I suppose that a character will always work, so
you need to always put a readily converted character value
into :x.
Or, in case that you choose however to use dynamic sql,
then maybe you could think of using DBMS_SQL instead of
"execute immediate". It's true that it's a little bit more
laborious to write, but here you may parse the UPDATE
statement ONCE only, and then, for each new row, you need only to BIND the variables and EXECUTE it, which is much
faster.
Hope this could help.
Best rgds,
Iudith

Hi Ludith,
Thanks for the suggestion.
I tried you first suggestion. I gave an Update statement instead of the second Execute Immediate, but it didn't run any fast. Infact it look a couple of seconds more.
I haven't worked with dbms_sql statements so I didn't try it.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy