This discussion is archived
4 Replies Latest reply: Feb 11, 2013 4:28 AM by 936351 RSS

Bind variable in PL/SQL

936351 Newbie
Currently Being Moderated
Hi,

PL/SQL newbie here, trying to put, inside a for loop, the name of a server to a variable and then use that variable in the where clause of a cursor.



FOR SRV_NUMBER IN SRV_NUMBER_low .. SRV_NUMBER_high
LOOP
EXECUTE IMMEDIATE 'select upper(substr(hostname, 1, nvl(nullif(instr(HOSTNAME, ''.'', 1),0),length(hostname)))) srv_name, SRV_MEMORY_GB from memrepserver where srv_order='||srv_number into :server_mem_tbl, memory_mem_tbl;

cursor get_dbs is select db_link from dba_db_links where db_link not like ('SAP_%') and db_link not in
('IPDP.EAME.SYNGENTA.ORG', 'PUMP.EAME.SYNGENTA.ORG','REXP.EAME.SYNGENTA.ORG','ORCL_STANDBY.EAME.SYNGENTA.ORG')
and
upper(substr(db_link, 1, nvl(nullif(instr(db_link, '.', 1)-1,0),instr(db_link, '@', 1)-1)))=:server_mem_tbl
order by 1;
open get_dbs;

...


With this I get: SP2-0552: Bind variable "SERVER_MEM_TBL" not declared. on line
upper(substr(db_link, 1, nvl(nullif(instr(db_link, '.', 1)-1,0),instr(db_link, '@', 1)-1)))=:server_mem_tbl

Any ideas are welcomed.

Thank you!
  • 1. Re: Bind variable in PL/SQL
    rp0428 Guru
    Currently Being Moderated
    Cursor declarations go in the DECLARE section - not in the BEGIN/END block. And they don't use bind variables.
  • 2. Re: Bind variable in PL/SQL
    636309 Newbie
    Currently Being Moderated
    933348 wrote:
    Hi,

    PL/SQL newbie here, trying to put, inside a for loop, the name of a server to a variable and then use that variable in the where clause of a cursor.



    FOR SRV_NUMBER IN SRV_NUMBER_low .. SRV_NUMBER_high
    LOOP
    EXECUTE IMMEDIATE 'select upper(substr(hostname, 1, nvl(nullif(instr(HOSTNAME, ''.'', 1),0),length(hostname)))) srv_name, SRV_MEMORY_GB from memrepserver where srv_order='||srv_number into :server_mem_tbl, memory_mem_tbl;

    cursor get_dbs is select db_link from dba_db_links where db_link not like ('SAP_%') and db_link not in
    ('IPDP.EAME.SYNGENTA.ORG', 'PUMP.EAME.SYNGENTA.ORG','REXP.EAME.SYNGENTA.ORG','ORCL_STANDBY.EAME.SYNGENTA.ORG')
    and
    upper(substr(db_link, 1, nvl(nullif(instr(db_link, '.', 1)-1,0),instr(db_link, '@', 1)-1)))=:server_mem_tbl
    order by 1;
    open get_dbs;

    ...


    With this I get: SP2-0552: Bind variable "SERVER_MEM_TBL" not declared. on line
    upper(substr(db_link, 1, nvl(nullif(instr(db_link, '.', 1)-1,0),instr(db_link, '@', 1)-1)))=:server_mem_tbl

    Any ideas are welcomed.

    Thank you!
    Hi, check out the example in this link and make sure your EXECUTE IMMEDIATE syntax is consistent with the example.

    http://stackoverflow.com/questions/7816402/using-bind-variables-with-dynamic-select-into-clause-in-pl-sql
  • 3. Re: Bind variable in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    As a PL/SQL newbie, take the following 2 fundamental concepts to heart.

    Maximise SQL and minimise PL/SQL.

    Think you need dynamic SQL in your PL/SQL? Chances are 99% in favour of you being totally wrong (and horribly wrong).

    As for the code you've posted. Where does the low and high server numbers come from? Why a loop to step through these numbers, and fire a SQL per loop iteration? That results in an expensive context switch between PL/SQL and SQL engines.

    Such loop iterations are also called row by row, and slow by slow, processing.

    Why is the code not modularised?
  • 4. Re: Bind variable in PL/SQL
    936351 Newbie
    Currently Being Moderated
    Thanks a lot for your help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points