| How to execute select query in procedure with table name as parameter [message #371883] |
Mon, 18 December 2000 05:24  |
sagar
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
How to execute select query in procude with table name as parameter.
This parameterised table name is given in select query
So how to view names from multiple tables by passing table name as parameter to a procedure.
Need urgently!!
Example below gives an error for tabname
e.g-
create or replace procedure TempDisp(tabname in varchar2)
mname varchar2(20);
as
begin
select name into mname from tabname;
end TempDisp;
|
|
|
|
|
|
| Re: How to execute select query in procedure with table name as parameter--I have got the solution f [message #371902 is a reply to message #371883] |
Tue, 19 December 2000 01:14  |
sagar
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
Before any one reply to this questions,I would like to give an answer to this.
I have got the solution for this.
This is the way you should implement dynamic SQL -
create or replace procedure GetPointBal (tabname in varchar2,bal out number)
as
pointbal number(10);
cursor_name integer;
rows_processed integer;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name,'select sum(cr_points)-sum(dr_points) into pointbal from :x',dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name,':x',tabname);
rows_processed := dbms_sql.execute(cursor_name);
bal := DBMS_SQL.VARIABLE_VALUE;
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
end GetPointBal;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
