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

Thank you all for help!

Greg

Jared Still <jkstill@(protected)> wrote:
This is probably close to what you are trying to do:

create table audittrail
as
select created dated, object_name details, owner moduser, 8 action
from
dba_objects
where rownum <= 10
/

DECLARE
m_dated DATE;
m_count NUMBER(10);
m_details varchar2(4000);
m_moduser varchar2(250);
type weakCurTyp is ref cursor;
m_cursor weakCurTyp;
m_rowid rowid;
BEGIN
FOR r IN (SELECT owner FROM all_tables WHERE table_name=upper('Audittrail')
order by owner)
LOOP
open m_cursor for 'select Dated, Details, Moduser from ' || r.owner || '
.audittrail where action = 8';
LOOP
fetch m_cursor into m_dated,m_details,m_moduser;
exit when m_cursor%notfound;
dbms_output.put_line ( m_dated || ':' || m_details || ':' || m_moduser
);
END LOOP;
END LOOP;
END;
/

Some serious study of the PL/SQL, PL/SQL supplied packages and SQL manuals is
suggested

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.

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

-- ---- ---- ---- ---- ---- -----
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
<div>Thank you all for help!</div> <div> </div> <div>Greg Jared Still <jkstill@(protected)> wrote:</div> <BLOCKQUOTE class
=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px
solid">This is probably close to what you are trying to do: create table
audittrail as select created dated, object_name details, owner moduser, 8
action from dba_objects where rownum <= 10 / DECLARE    m_dated DATE;    m_count NUMBER(10); 
   m_details varchar2(4000);    m_moduser varchar2(250);
    type weakCurTyp is ref cursor;    m_cursor
weakCurTyp;    m_rowid rowid; BEGIN    FOR r IN
(SELECT owner FROM all_tables WHERE table_name=upper('Audittrail') order by
owner)    LOOP       open m_cursor
for 'select Dated, Details, Moduser from ' || r.owner ||
'.audittrail where action = 8';       LOOP 
         fetch m_cursor into m_dated,m
_details,m_moduser;          exit
when m_cursor%notfound;         
dbms_output.put_line ( m_dated || ':' || m_details || ':' || m_moduser); 
      END LOOP;    END LOOP; END; /
 Some serious study of the PL/SQL, PL/SQL supplied packages and SQL
manuals is suggested <DIV>On 4/6/06, ora_forum <<A href="mailto:ora_forum@(protected)"
>ora_forum@(protected)</A>> wrote: <BLOCKQUOTE class=gmail_quote style=
"PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px
solid"> <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"> <div></div> <HR SIZE=1> Talk is cheap. Use
Yahoo! Messenger to make PC-to-Phone calls. <A onclick="return top.js
.OpenExtLink(window,event,this)" href="http://us.rd.yahoo.com/mail_us/taglines
/postman7/*http://us.rd.yahoo.com/evt=39666/*http://beta.messenger.yahoo.com"
target=_blank>Great rates starting at 1?/min. </A> <div></div></DIV><
/BLOCKQUOTE></DIV> -- Jared Still Certifiable Oracle
DBA and Part Time Perl Evangelist </BLOCKQUOTE> 
<hr size=1>New Yahoo! Messenger with Voice. <a href="http://us.rd.yahoo.com
/mail_us/taglines/postman5/*http://us.rd.yahoo.com/evt=39666/*http://beta
.messenger.yahoo.com">Call regular phones from your PC</a> and save big.