RE: pl/sql error
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: <ora_forum@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Mar 2006 14:20:27 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ora_forum
Sent: Tuesday, March 21, 2006 1: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!
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.
Other related posts:
