Dynamic SQL

From: ora_forum <ora_forum_at_yahoo.com>
Date: Thu, 6 Apr 2006 08:52:42 -0700 (PDT)
Message-ID: <20060406155242.52573.qmail@web36709.mail.mud.yahoo.com>

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&cent;/min.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 06 2006 - 10:52:42 CDT