Re: Dynamic SQL
- From: Nigel Thomas <nigel@xxxxxxxxxxxxxx>
- To: ora_forum@xxxxxxxxx
- Date: Thu, 6 Apr 2006 11:52:03 -0700 (PDT)
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
Other related posts:
