Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Dynamic SQL

RE: Dynamic SQL

From: Freeman, Donald <dofreeman_at_state.pa.us>
Date: Thu, 6 Apr 2006 13:10:06 -0400
Message-ID: <51327ABA927BEF4B96590554CEA7832C060B949C@enhbgpri05.backup>


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

	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum
	Sent: Thursday, April 06, 2006 11:53 AM
	To: oracle-l_at_freelists.org
	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>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 06 2006 - 12:10:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US