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 Jared Still

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

rowid is a reserved word.

put it double quotes in uppercase, or alias the name to
something else in the SQL

eg. select rowid my_rowid from table_name

On 4/6/06, ora_forum <ora_forum@(protected)> wrote:
>
> 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.<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>
>
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

rowid is a reserved word. put it double quotes in uppercase, or alias
the name to something else in the SQL eg. select rowid my_rowid from
table_name <div>On 4/6/06, 
ora_forum <<a href="mailto:ora_forum@(protected)">ora_forum@(protected)</a>
> wrote:<blockquote class="gmail_quote" style="border-left: 1px solid
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="direction: ltr;"><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</div>
<div style="direction: ltr;"> 
"Reidy, Ron" <<a href="mailto:Ron.Reidy@(protected)"
target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
Ron.Reidy@(protected)</a>> wrote:</div><div style=
"direction: ltr;"></div></div><div style="direction: ltr;"> <blockquote style="border-left: 2px solid rgb(16, 16, 255
); padding-left: 5px; margin-left: 5px;">
<div> <div>You cannot test for NULL with the
'=, <>, !=', etc. operators. The correct syntax is "? IS NOT NULL".
</div> <div> </div>
<div>Also, what is not working?</div
> <div>
 </div> <div> <
/div> <div style="margin-left: 0.5in;">
-- --Original Message-- -- From: <a href="mailto:oracle-l-bounce@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">oracle-l-bounce@(protected)
</a> [mailto:<a href="mailto:oracle-l-bounce@(protected)" target="_blank"
onclick="return top.js.OpenExtLink(window,event,this)">oracle-l-bounce
@(protected)</a>] On Behalf Of 
ora_forum Sent: Thursday,
April 06, 2006 9:53 AM To: <a
href="mailto:oracle-l@(protected)" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">
oracle-l@(protected)</a> Subject: Dynamic SQL </div> <div style="margin-left: 0.5in;">

 </div> <div> <div style="margin-left: 0.5in;">Hi All:<
/div></div> <div> <div style="margin-left: 0.5in;">
Could you tell me what I'm doing wrong?</div></div> <div> <div 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 style="margin-left: 0.5in;"> <
/div></div> <div>
<div> <div 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 style="margin-left: 0.5in;">
 </div></div> <div> <div style="margin-left: 0.5in;">Thanks.</div></div></div> <div style="margin-left: 0.5in
; text-align: center;" align="center">
 <hr
align="center" size="1" width="100%"> </div> <div 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"
target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
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> </div><div style=
"direction: ltr;">
<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" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">
PC-to-Phone call rates.
</a></div></blockquote></div> -- Jared
Still Certifiable Oracle DBA and Part Time Perl Evangelist