RE: Dynamic SQL

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Thu, 6 Apr 2006 11:10:10 -0600
Message-ID: <7209E76DACFED9469D4F5169F9880C7A0C7B7B@mail01bldr.arraybp.com>

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. <http://us.rd.yahoo.com/mail_us/taglines/postman7/*http:/us.rd.yahoo.com/evt=39666/*http:/beta.messenger.yahoo.com>

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.

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

Received on Thu Apr 06 2006 - 12:10:10 CDT