4 Replies Latest reply: Feb 11, 2013 6:28 AM by 936351 RSS

    Bind variable in PL/SQL

    936351
      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
          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
            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
              Billy~Verreynne
              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
                Thanks a lot for your help!