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 Freeman, Donald

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

Why don't you post the error you are getting.

-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of ora_forum
Sent: Thursday, April 06, 2006 11: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>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1528" name=GENERATOR></HEAD>
<BODY>
<DIV>Why
don't you post the error you are getting.  </DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>-- --Original Message-- -- From:
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On
Behalf Of ora_forum Sent: Thursday, April 06, 2006 11:53
AM To: oracle-l@(protected) Subject: Dynamic SQL
 </DIV>
<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 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>

<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">Great
rates starting at 1¢/min.</A></BLOCKQUOTE></BODY></HTML>