You have to use dbms_sql ...=20 Raj -------------------------------------------------------------------------= ------- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----Original Message----- From: Ryan [mailto:ryan.gaffuri@xxxxxxx] Sent: Sunday, January 25, 2004 10:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: fetching long variable in PL/SQL convert to a lob and use a substr. That might fix the problem. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L@xxxxxxxxxxx> Sent: Sunday, January 25, 2004 10:24 AM > > > > > Hi all , > I have a problem about fetching long variable in PL/SQL. > I encountered with problem in following PL/SQL block. > When I try to fetch long variable , I receive " PL/SQL: numeric or value error ORA-06512". > As I searched in metalink, it is releated to size of long variable. Probably it is greather than > 32KB. > I could not find a solution. > Do you have any info or and advice that how I can fetch long variabl= es greather than 32KB. > May it can be divided into more than one variable but how. > Thanks > Arslan. > > > DECLARE > > USER_TRIGGERS_COUNT NUMBER :=3D 0; > USER_TRIGGERS_COUNTER NUMBER :=3D 0; > > CURSOR USER_TRIGGERS_CUR IS > SELECT TRIGGER_NAME ,DESCRIPTION , trigger_BODY FROM USER_TRIGGERS > where trigger_name =3D 'TRG_APPL_CONTRACTS_BIUD_R' ; > USER_TRIGGERS_R USER_TRIGGERS_CUR%ROWTYPE; > > BEGIN > select COUNT(*) INTO USER_TRIGGERS_COUNT from USER_OBJECTS WHERE OBJECT_TYPE =3D 'TRIGGER'; > OPEN USER_TRIGGERS_CUR; > LOOP > FETCH USER_TRIGGERS_CUR INTO l_TRIGGER_NAME ,l_DESCRIPTION ,l_BODY ; > EXIT WHEN(USER_TRIGGERS_CUR%NOTFOUND ); > NULL; > END LOOP; > CLOSE USER_TRIGGERS_CUR; > END; > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <A.Bahar@xxxxxxxxxxxxxxxxxxxxxx > INET: A.Bahar@xxxxxxxxxxxxxxxxxxxxxx > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.net --=20 Author: Ryan INET: ryan.gaffuri@xxxxxxx Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@xxxxxxxxxxx (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *************************************************************************= ************* This e-mail message is confidential, intended only for the named recipien= t(s) above and may contain information that is privileged, attorney work = product or exempt from disclosure under applicable law. If you have recei= ved this message in error, or are not the named recipient(s), please imme= diately notify corporate MIS at (860) 766-2000 and delete this e-mail mes= sage from your computer, Thank you. *************************************************************************= *************4