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: Maxim <mdemenko_at_gmail.com>
Date: Thu, 6 Apr 2006 20:08:11 +0200
Message-ID: <8dde6bbd0604061108i50307eddqbc825d3e7599308c@mail.gmail.com>


As i said before, your inner loop will not work, you need a ref_cursor to implement it (that is not only the wrong place in your code, but it cause actually your first error) .

Best regards

Maxim

On 4/6/06, ora_forum <ora_forum_at_yahoo.com> wrote:
>
> 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.<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.
>
>
> ------------------------------
> How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call
> rates.
> <http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 06 2006 - 13:08:11 CDT

Original text of this message

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