 | | | Dynamic SQL | Dynamic SQL 2006-04-06 - By Nigel Thomas
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
<html><head><style type="text/css"><!-- DIV {margin:0px} --></style></head>
<body><div style="font-family:times new roman, new york, times, serif;font-size
:12pt"><DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york,
times, serif">
<DIV>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</DIV>
<DIV> </DIV>
<DIV>Regards Nigel</DIV>
<DIV>-- ---- ---- ---- ---- -----</DIV>
<DIV> </DIV>
<DIV>DECLARE <BR>m_output VARCHAR2(4000);<BR>TYPE curtype IS REF CURSOR;<BR>m
_cursor curtype;<BR>BEGIN<BR> -- ensure output is enabled<BR> -- you
could use SQL*Plus "set serverout on size 1000000" instead<BR> DBMS
_OUTPUT.ENABLE(100000);<BR> DBMS_OUTPUT.PUT_LINE('Start:');<BR> <BR>
FOR r IN<BR> (SELECT owner<BR>
FROM all_tables<BR> WHERE TABLE_NAME = UPPER('JOBS')<BR
> ORDER BY owner)<BR> LOOP<BR>
DBMS_OUTPUT.PUT_LINE('Owner = '||r.owner);</DIV>
<DIV><BR> -- I've put concatenation into the dynamic
statement<BR> -- you could instead query any N columns here,
fetching into N pl/sql variables<BR> -- and then concatenate
in the call to DBMS_OUTPUT.PUT_LINE below</DIV>
<DIV><BR> OPEN m_cursor FOR <BR>  
; 'SELECT JOB_ID||'':''||JOB_TITLE||'':''||MAX_SALARY FROM ' || r.owner ||
'.JOBS where min_salary = 4000';<BR> LOOP<BR>
FETCH m_cursor<BR> INTO m
_output;<BR> EXIT WHEN m_cursor % NOTFOUND;</DIV>
<DIV> -- dbms_output.put_line takes a single
parameter<BR> DBMS_OUTPUT.PUT_LINE(m_output);<BR>
END LOOP;<BR> END LOOP;<BR>END;<BR>/</DIV>
<DIV> </DIV>
<DIV>anonymous block completed<BR>Start:<BR>Owner = HR<BR>IT_PROG:Programmer
:10000<BR>MK_REP:Marketing Representative:9000<BR>HR_REP:Human Resources
Representative:9000<BR><BR></DIV></DIV></div></body></html>
|
|
|