| Cursor Values [message #51798] |
Thu, 13 June 2002 15:07  |
Suresh Natarajan
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
I have a select privilege on a remote table through a role. When i do "select * from remote_table" it works fine. But when i refer from a cursor in PL/SQL saying "cursor c1 is select * from remote_table". It says "Table not found".
Thanks in advance for your help.
Suresh
|
|
|
|
| Re: Cursor Values [message #51799 is a reply to message #51798] |
Thu, 13 June 2002 16:02  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
i beleive, you cannot use cursor clause directly inside pl/sql.
use DBMS_SQL.
something like this
SQL> get cur
1 CREATE OR REPLACE PROCEDURE dynamic_query (table_name IN varchar2) AS
2 v_cur integer;
3 v_ename emp.ename%TYPE;
4 v_cur_tmp integer;
5 begin
6 v_cur := dbms_sql.open_cursor;
7 dbms_sql.parse(v_cur,'select ENAME from '||table_name,dbms_sql.v7);
8 dbms_sql.define_column(v_cur,1,v_ename,10);
9 v_cur_tmp := dbms_sql.execute(v_cur);
10 while dbms_sql.fetch_rows(v_cur) > 0
11 loop
12 dbms_sql.column_value(v_cur,1,v_ename);
13 dbms_output.put_line(v_ename );
14 end loop;
15 dbms_sql.close_cursor(v_cur);
16* end;
17 /
Procedure created.
SQL> exec dynamic_query('emp');
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
