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

Sure, but you have to show us how it breaks.

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

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

Sure, but you have to show us how it breaks. <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>Hi All:</div> <div>Could you tell me what I
'm doing wrong?</div> <div>I need pass different schema owner in SQL, also in
some tables there are multiple records will be returned. </div>
<div> </div> <div> <div>DECLARE</div> 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>Thanks.</div></div><div style="direction: ltr;">

<hr size="1">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></blockquote></div> -- Jared
Still Certifiable Oracle DBA and Part Time Perl Evangelist