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. > <http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://beta.messenger.yahoo.com> > > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist