| Can one pass a column name as a parameter to a procedure [message #912] |
Tue, 12 March 2002 06:32  |
Dan
Messages: 61
Registered: February 2000
|
Member |
|
|
I am trying to make a 'dynamic' sql statement in Oracle 7.x as follows:
Procedure get_data(x IN varchar2) as
stmt varchar2(200);
cur integer;
Begin
cur := dbms_sql.open_cursor;
stmt := 'Select' ||x|| ' from table';
dbms_sql.parse(cur,stmt,dbms_sql.v7);
.
.
.
.
End get_data;
This procedure works fine if I pass '*', however if I pass a valid column name it fails.
Please help
|
|
|
|
| Re: Can one pass a column name as a parameter to a procedure [message #918 is a reply to message #912] |
Tue, 12 March 2002 20:55  |
Suparna Saha
Messages: 10
Registered: October 2001
|
Junior Member |
|
|
I am sending one Dynamic Sql. You run this sql and check in with your requirements.
/****************************************************************************************/
/* Dynamically Execute SQL
/****************************************************************************************/
set serveroutput on
declare
id number;
scolval varchar2(30);
sreturn integer;
scol varchar2(30);
begin
dbms_output.enable(40000);
sCol := 'area_name';
id := dbms_sql.open_cursor;
dbms_sql.parse(id, 'select ' || scol || ' from t_area_mast' , dbms_sql.v7);
dbms_sql.define_column(id, 1, scolval, 30);
sreturn := dbms_sql.execute(id);
loop
if dbms_sql.fetch_rows(id) > 0 then
dbms_sql.column_value(id, 1, scolval);
dbms_output.put_line('t_area_mast = ' || scolval);
else
exit;
end if;
end loop;
dbms_sql.close_cursor(id);
end;
/
I think it will help you.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
