RE: pl/sql error

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 21 Mar 2006 16:28:57 -0500
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2C4CD4B@EXCHANGE.corp.perceptron.com>

You should use dynamic sql to get what you want.  

Igor

---

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:28:57 CST