RE: pl/sql error

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 21 Mar 2006 16:35:57 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410506C062D2@usahm236.amer.corp.eds.com>

The SQL within pl/sql is static and cannot be modified. To use dynamic SQL you will need to use the EXECUTE IMMEDIATE statement or the DBMS_SQL package. See the PL/SQL Manual for EXECUTE IMMEDIATE.  

HTH -- Mark D Powell --    

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum Sent: Tuesday, March 21, 2006 3:57 PM
To: oracle-l_at_freelists.org
Subject: pl/sql error

	Hi All:
	My procedure must list only tables where max(length

(formula))>150. I have about 100 schemas and each has FIELDCUSTOM table.          

	CREATE OR REPLACE PROCEDURE data_count is
	owner varchar2 (40);
	i number (10);
	BEGIN
	FOR n IN (SELECT owner FROM dba_tables WHERE table_name
='FIELDCUSTOM')
	LOOP
	    SELECT max(length (formula)) INTO i FROM
n.owner.FIELDCUSTOM;
	   IF i>=150 THEN
	   dbms_output.put_line (owner);
	     ELSE
	      null;
	    END IF;
	    END LOOP;
	END;
	 
	Problem in line:
	SELECT max(length (formula)) INTO i FROM  n.owner.FIELDCUSTOM
	error sql command not properly ended.
	 
	Thanks.

	


________________________________


	Yahoo! Travel
	Find great deals

<http://us.lrd.yahoo.com/_ylc=X3oDMTFscDlocTFiBF9TAzMyOTc1MDIEX3MDMjcxOT Q4MQRwb3MDMgRzZWMDbWFpbC1mb290ZXIEc2xrA3l0LXR0/SIG=12hqieud9/**http%3a// leisure.travelocity.com/Promotions/0,,YHOE%7c1381%7cvacs_main,00.html> to the top 10 hottest destinations!

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 21 2006 - 15:35:57 CST