RE: pl/sql error

You should use dynamic sql to get what you want.
 
Igor

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ora_forum
Sent: Tuesday, March 21, 2006 3:57 PM
To: oracle-l@xxxxxxxxxxxxx
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!

Other related posts: