Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dynamic SQL

RE: Dynamic SQL

From: ora_forum <ora_forum_at_yahoo.com>
Date: Thu, 6 Apr 2006 10:18:41 -0700 (PDT)
Message-ID: <20060406171841.67691.qmail@web36710.mail.mud.yahoo.com>


Sorry.    

                                   *

ERROR at line 9:
ORA-06550: line 9, column 34:
PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 9, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 8:
PLS-00204: function or pseudo-column 'ROWID' may be used inside a SQL statement only
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored

"Reidy, Ron" <Ron.Reidy_at_arraybiopharma.com> wrote:

                You cannot test for NULL with the ‘=, <>, !=’, etc. operators. The correct syntax is “… IS NOT NULL”.
   

  Also, what is not working?        

  -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum Sent: Thursday, April 06, 2006 9:53 AM
To: oracle-l_at_freelists.org
Subject: Dynamic SQL    

    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.     



  This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.                 

How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 06 2006 - 12:18:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US