Dynamic SQL

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Dynamic SQL

Dynamic SQL

2006-04-06 - By ora_forum

Reply: 1 2 3 4 5 6 7 8 9 10

Sorry.

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

"Reidy, Ron" <Ron.Reidy@(protected)> 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@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of ora_forum
Sent: Thursday, April 06, 2006 9:53 AM
To: oracle-l@(protected)
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.
<DIV>Sorry.</DIV> <DIV> </DIV> <DIV>      
             
              *
 ERROR at line 9: ORA-06550 (See ORA-06550.ora-code.com): line 9, column 34: PL/SQL: ORA-00942 (See ORA-00942.ora-code.com):
table or view does not exist ORA-06550 (See ORA-06550.ora-code.com): line 9, column 14: PL/SQL: SQL
Statement ignored ORA-06550 (See ORA-06550.ora-code.com): line 11, column 8: PLS-00204: function or
pseudo-column 'ROWID' may be used inside a SQL statement only ORA-06550 (See ORA-06550.ora-code.com):
line 11, column 5: PL/SQL: Statement ignored "Reidy, Ron" &lt
;Ron.Reidy@(protected)> wrote:</DIV> <BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<META content="Microsoft Word 10 (filtered)" name=Generator> <STYLE>  </STYLE> <DIV class=Section1> <DIV class
=MsoNormal>You cannot test for NULL with the ?=, <>
, !=?, etc. operators. The correct syntax is ?? IS NOT NULL?.</DIV
> <DIV class=MsoNormal> </DIV>
<DIV class=MsoNormal>Also, what is not working?</DIV> <DIV class=MsoNormal> </DIV>
<DIV class=MsoNormal> </DIV> <DIV class
=MsoNormal style="MARGIN-LEFT: 0.5in">-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto
:oracle-l-bounce@(protected)] On Behalf Of
ora_forum Sent:
Thursday, April 06, 2006 9:53 AM To: oracle-l@(protected) Subject: Dynamic SQL </DIV>
<DIV class=MsoNormal style="MARGIN-LEFT: 0.5in"> </DIV> <DIV> <DIV
class=MsoNormal style="MARGIN-LEFT: 0.5in">
Hi All:</DIV></DIV> <DIV> <DIV
class=MsoNormal style="MARGIN-LEFT: 0.5in">
Could you tell me what I'm doing wrong?</DIV></DIV> <DIV> <DIV class=MsoNormal style="MARGIN-LEFT: 0.5in">I need pass
different schema owner in SQL, also in some tables there are multiple records
will be returned. </DIV></DIV> <DIV> <DIV class=MsoNormal style
="MARGIN-LEFT: 0.5in"> </DIV></DIV> <DIV>
<DIV> <DIV class=MsoNormal style="MARGIN-LEFT: 0.5in">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
; /</DIV></DIV> <DIV> <DIV class=MsoNormal style="MARGIN-LEFT: 0.5in
"> </DIV></DIV> <DIV> <DIV class=MsoNormal
style="MARGIN-LEFT: 0.5in">
Thanks.</DIV></DIV><
/DIV> <DIV class=MsoNormal style="MARGIN-LEFT: 0.5in; TEXT-ALIGN: center" align
=center> <HR
align=center width="100%" SIZE=1> </DIV> <DIV class=MsoNormal
style="MARGIN-LEFT: 0.5in">Talk is cheap. Use Yahoo! Messenger to
make PC-to-Phone calls. <A href="http://us.rd.yahoo.com/mail_us/taglines
/postman7/*http:/us.rd.yahoo.com/evt=39666/*http:/beta.messenger.yahoo.com"
>Great rates starting at 1?/min.</A></DIV></DIV> <DIV style=
"MARGIN-RIGHT: 0px" align=left>
<HR> 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. <DIV></DIV></DIV></BLOCKQUOTE> 
<hr size=1>How low will we go? Check out Yahoo! Messenger?s low <a href="http
://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/
*http://voice.yahoo.com"> PC-to-Phone call rates.