Re: Dynamic SQL

From: ora_forum <ora_forum_at_yahoo.com>
Date: Thu, 6 Apr 2006 13:05:37 -0700 (PDT)
Message-ID: <20060406200537.89252.qmail@web36711.mail.mud.yahoo.com>

Thank you all for help!    

  Greg

Jared Still <jkstill_at_gmail.com> 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_at_yahoo.com> 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.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 06 2006 - 15:05:37 CDT