RE: pl/sql error

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Tue, 21 Mar 2006 14:20:27 -0700
Message-ID: <7209E76DACFED9469D4F5169F9880C7A0C7ACF@mail01bldr.arraybp.com>

You cannot build dynamic SQL this way in PL/SQL.  

You will need to build the 2nd select before executing it. Look at the docs for 'EXECUTE IMMEDIATE'.  

--

Ron Reidy
Lead DBA
Array BioPharma, Inc.  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum Sent: Tuesday, March 21, 2006 1: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!

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.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 21 2006 - 15:20:27 CST