RE: pl/sql error

From: Ken Naim <kennaim_at_gmail.com>
Date: Tue, 21 Mar 2006 15:27:04 -0600
Message-ID: <01fa01c64d2e$33f90530$fd346f44@KenHome>

You cannot use a variable (n.owner) as the schema name unless you use dynamic sql.  

Ken  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum
Sent: Tuesday, March 21, 2006 2: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
<http://us.lrd.yahoo.com/_ylc=X3oDMTFscDlocTFiBF9TAzMyOTc1MDIEX3MDMjcxOTQ4MQ Rwb3MDMgRzZWMDbWFpbC1mb290ZXIEc2xrA3l0LXR0/SIG=12hqieud9/**http%3a/leisure.t ravelocity.com/Promotions/0,,YHOE%7c1381%7cvacs_main,00.html> great deals to the top 10 hottest destinations!

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

Received on Tue Mar 21 2006 - 15:27:04 CST