Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Cursor question
PL/SQL Cursor question [message #273808] Thu, 11 October 2007 14:31 Go to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Hi ,

Please can someone confirm that my understanding is correct

In a procedure or function it is not possible to pass a variable that is the table name and use it in a cursor as the table name is an identifier - bind variables can`t be used with identifiers eg

this is ok
select some_varchar2variable from dual

this is not
select * from some_varchar2variable

Is there any way around this am i talking rubbish ?

Thanks for your help
Phil
Re: PL/SQL Cursor question [message #273809 is a reply to message #273808] Thu, 11 October 2007 14:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE
but results in HARD PARSES & does NOT scale

[Updated on: Thu, 11 October 2007 14:33] by Moderator

Report message to a moderator

Re: PL/SQL Cursor question [message #273825 is a reply to message #273809] Thu, 11 October 2007 16:50 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Thanks for the reply but....

I dont understand the respone ...

anacedent wrote on Thu, 11 October 2007 15:32

EXECUTE IMMEDIATE
but results in HARD PARSES & does NOT scale


I am selecting values from a table in a cursor and constructing sql to "EXECUTE IMMEDIATE" . I want to pass the table of values as an input to the procedure so at run time i can specify the table i want the procedure to use

Does this make any sense to anyone ?

Regards
P


Re: PL/SQL Cursor question [message #273827 is a reply to message #273825] Thu, 11 October 2007 17:11 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member

This is not tested, but should give you the general idea.

procedure make_it(p_tblname  in varchar2, 
                  p_col1     in varchar2 ) is

  v_query   varchar2(4000);

begin

   v_query := ' select ' || p_col1 || ' from ' || p_tblname ;

   EXECUTE IMMEDIATE v_query;

end make_it;
Re: PL/SQL Cursor question [message #273828 is a reply to message #273827] Thu, 11 October 2007 17:21 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Thanks for your help.

I think i will post the full proc tomorrow and maybe it will make more sense than my insane ramblings..

Regards
P
Re: PL/SQL Cursor question [message #273865 is a reply to message #273828] Fri, 12 October 2007 01:17 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I would consider a ref cursor. A small example:
CREATE FUNCTION print_table( piv_tname IN VARCHAR2)
  RETURN SYS_REFCURSOR
IS
  v_cur SYS_REFCURSOR;
BEGIN
  
  OPEN v_cur FOR 'SELECT * FROM '||piv_tname||' WHERE ROWNUM < 3';
  
  RETURN v_cur;
END print_table;
/
sho err

VAR table_cur REFCURSOR

EXEC :table_cur := print_table('EMPLOYEES');

PRINT :table_cur

EXEC :table_cur := print_table('DEPARTMENTS');

PRINT :table_cur

DROP FUNCTION print_table
/
When I run it against the HR schema I get:
SQL> @orafaq

Function created.

No errors.

PL/SQL procedure successfully completed.


LOCATION_ID STREET_ADDRESS                           POSTAL_CODE
----------- ---------------------------------------- ------------
CITY                           STATE_PROVINCE            CO
------------------------------ ------------------------- --
       1000 1297 Via Cola di Rie                     00989
Roma                                                     IT

       1100 93091 Calle della Testa                  10934
Venice                                                   IT



PL/SQL procedure successfully completed.


DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800


Function dropped.

MHE
Previous Topic: Problem in Sql
Next Topic: Timestamp
Goto Forum:
  


Current Time: Sat Nov 09 17:12:37 CST 2024