Re: Dynamic SQL

From: Nigel Thomas <nigel_at_preferisco.com>
Date: Thu, 6 Apr 2006 11:52:03 -0700 (PDT)
Message-ID: <20060406185203.19248.qmail@web54715.mail.yahoo.com>

To summarise and clarify previous advice, here's how to make the anonymous block work. I've changed the table and column names to work with tables in the HR schema that comes with SQL*Developer (or was it JDeveloper...) but you can easily change it to fit your requirements once you've understood the concepts  

Regards Nigel

---

 

DECLARE
m_output VARCHAR2(4000);
TYPE curtype IS REF CURSOR;
m_cursor curtype;
BEGIN

• ensure output is enabled
• you could use SQL*Plus "set serverout on size 1000000" instead DBMS_OUTPUT.ENABLE(100000); DBMS_OUTPUT.PUT_LINE('Start:');

  FOR r IN
    (SELECT owner

     FROM all_tables
     WHERE TABLE_NAME = UPPER('JOBS')
     ORDER BY owner)

  LOOP
    DBMS_OUTPUT.PUT_LINE('Owner = '||r.owner);

• I've put concatenation into the dynamic statement
• you could instead query any N columns here, fetching into N pl/sql variables
• and then concatenate in the call to DBMS_OUTPUT.PUT_LINE below

    OPEN m_cursor FOR

       'SELECT JOB_ID||'':''||JOB_TITLE||'':''||MAX_SALARY FROM ' || r.owner || '.JOBS where min_salary = 4000';     LOOP

      FETCH m_cursor
      INTO m_output;
      EXIT WHEN m_cursor % NOTFOUND;
      -- dbms_output.put_line takes a single parameter
      DBMS_OUTPUT.PUT_LINE(m_output);

    END LOOP;
  END LOOP;
END;
/  

anonymous block completed
Start:
Owner = HR

IT_PROG:Programmer:10000
MK_REP:Marketing Representative:9000
HR_REP:Human Resources Representative:9000

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 06 2006 - 13:52:03 CDT