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 Reidy, Ron

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

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.

<html>

<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 10 (filtered)">

<style>

</style>

</head>

<body lang=EN-US link=blue vlink=blue>

<div class=Section1>

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 

 

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

<div class=MsoNormal align=center style='margin-left:.5in;text-align:center'>


<hr size=1 width="100%" align=center>

</div>

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>

</body>


<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.</html>