Assign field name to cursor variable at run time

This is a discussion on Assign field name to cursor variable at run time within the Oracle forums in Oracle Database category; Hi I am creating a table dynamically within a procedure, by dropping the existing table and recreating it with the same name but different columns as passed to the procedure. I have a cursor defined on this table which selects all the columns from it.In the logic of procedure I am trying to form an update statement where I need to use the value from the table. Code snippet shows how I wanna use it to assign to variable v_str Code snippet : v_col_nm conatins the name of the column passed declare cursor c1 is select * from ext_table_dataload; begin execute ...

Go Back   Database Forum > Oracle Database > Oracle

Database Forums

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 05-08-2008, 08:38 AM
Default Assign field name to cursor variable at run time

Hi

I am creating a table dynamically within a procedure, by dropping the existing table and recreating it with the same name but different columns as passed to the procedure.

I have a cursor defined on this table which selects all the columns from it.In the logic of procedure I am trying to form an update statement where I need to use the value from the table. Code snippet shows how I wanna use it to assign to variable v_str

Code snippet : v_col_nm conatins the name of the column passed
declare

cursor c1 is select * from ext_table_dataload;

begin
execute immediate 'drop table ext_table_dataload';
execute immediate 'create table ext_table_dataload'; -- columns are set by input parameters

for j in c1 loop
v_str := 'update emp set '|| v_col_nm || j.v_col_nm;
end loop;
end;

It gives complie time error saying 'v_col_nm' must be declared'
As v_col_nm is not column name in cursor but i need to use its value which is the column name and then execute the statement dynamically.

Can you please suggest how can i do this.
Reply With Quote
Reply


Thread Tools
Display Modes



All times are GMT -4. The time now is 03:24 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Integrated by bbpixel2008 :: jvbPlugin R1013.368.1

Search Engine Friendly URLs by vBSEO 3.1.0
vB Ad Management by =RedTyger=
In an effort to better serve ads to our visitors, cookies are used on Mydatabasesupport.com. For more information, check out our Privacy Policy.