OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Cursor Values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Cursor Values [message #51799 is a reply to message #51798]

Thu, 13 June 2002 16:02

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Previous Topic:	Enterprise edition & PO
Next Topic:	transfer datafiles across o/s platform

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 01:29:47 CDT 2024