Dynamic Column name in a Cursor

pts.
Tags:
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Really, at the stmt construction :x isn’t bound.
Try

stmt := ‘update tmp_table set rvu=’ || ‘:x ‘;

hope this helps.
Serg

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • Kmarur
    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.
    0 pointsBadges:
    report
  • Welcome
    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
    0 pointsBadges:
    report
  • Kmarur
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

REGISTER or login:

Forgot Password?
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! We'll email you when relevant content is added and updated.

Following