Re: Dynamic SQL
- From: ora_forum <ora_forum@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Thu, 6 Apr 2006 13:05:37 -0700 (PDT)
Thank you all for help!
Greg
Jared Still <jkstill@xxxxxxxxx> wrote:
This is probably close to what you are trying to do:
create table audittrail
as
select created dated, object_name details, owner moduser, 8 action
from
dba_objects
where rownum <= 10
/
DECLARE
m_dated DATE;
m_count NUMBER(10);
m_details varchar2(4000);
m_moduser varchar2(250);
type weakCurTyp is ref cursor;
m_cursor weakCurTyp;
m_rowid rowid;
BEGIN
FOR r IN (SELECT owner FROM all_tables WHERE table_name=upper('Audittrail')
order by owner)
LOOP
open m_cursor for 'select Dated, Details, Moduser from ' || r.owner ||
'.audittrail where action = 8';
LOOP
fetch m_cursor into m_dated,m_details,m_moduser;
exit when m_cursor%notfound;
dbms_output.put_line ( m_dated || ':' || m_details || ':' ||
m_moduser);
END LOOP;
END LOOP;
END;
/
Some serious study of the PL/SQL, PL/SQL supplied packages and SQL manuals is
suggested
On 4/6/06, ora_forum <ora_forum@xxxxxxxxx> wrote: Hi All:
Could you tell me what I'm doing wrong?
I need pass different schema owner in SQL, also in some tables there are
multiple records will be returned.
DECLARE
m_dated DATE ;
m_count NUMBER(10);
m_details varchar2(4000);
m_moduser varchar2(250);
BEGIN
FOR r IN (SELECT owner FROM all_tables WHERE table_name= upper('Audittrail')
order by owner)
LOOP
FOR n IN (SELECT ROWID FROM r.Audittrail where action=8)
LOOP
IF ROWID<> 'NULL' or ROWID<>0 THEN
EXECUTE IMMEDIATE 'SELECT Dated, Details, Moduser FROM ' || R.owner
||'.Audittrail where rowid=' ||n||'.rowid' into m_dated, m_details, m_moduser;
dbms_output.put_line ( m_dated,m_details, m_moduser);
ELSE
NULL;
END IF;
END LOOP ;
END LOOP;
END ;
/
Thanks.
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates
starting at 1¢/min.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Other related posts:
