Why don't you use the select string
declare
mm varchar2(200);
begin
select substr(text,1,200) into mm from om_sqltext_info where id=3D15972;
end;
>>> "A. Bardeen" <abardeen1_at_yahoo.com> 06/26/00 08:54AM >>>
Mohamed,
This can happen if the longraw is null, the starting
position is greater than the length of the long raw,
or if the length for the substr is greater than the
length of the long raw.
You need to write the error trapping routines into
your procedure to account for these possibilities.
HTH,
- Anita
- Mohamed Gad <mgad_at_itsoft.com.eg> wrote:
>=20
> Dear all
> I have a problem and I do not what to do.
> my problem is:
> I have a table of 2 columns id number, text(contains
> text) long raw
> I want to select a specific text from the table and
> convert it to varchar2
> and write it to file
> so I create the following procedure.
>=20
> procedure test1 is
> mm long raw;
> xx long raw;
> I UTL_FILE.FILE_TYPE;
> begin
> I :=3D UTL_FILE.FOPEN ( 'c:\bassem\','test.txt' ,'w');
> select text into mm from om_sqltext_info where
> id=3D15972;
> /* I WANT TO TAKE A PART OF THE LONG RAW BECAUSE IT
> IS GREATER THAN 32760 */
> xx:=3Dutl_raw.SUBSTR (mm,1,200);
> UTL_FILE.PUT(i,utl_raw.cast_to_varchar2(xx));
> UTL_FILE.FCLOSE(i);
> end;
>=20
> but when I run this procedure I get the next error
>=20
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "SYS.UTL_RAW", line 299
> ORA-06512: at "WORKBENCH.TEST1", line 8
> ORA-06512: at line 1
>=20
> I do not what is cause if any one know please replay
> as soon as possible.
>=20
> note:
> when I run this procedure on smaller text (the text
> size is smaller than
> 32760) so there is no need to xx:=3Dutl_raw.SUBSTR
> (mm,1,200);
> in this case the procedure succeeded and write to
> file.
>=20
> Best Regards
> Mohamed Gad
> ITSoft, The software Factory of International
> Turnkey Systems (ITS)
>=20
> 16 Lebanon st., Mohandeseen, Giza, Egypt
> Tel: +20-2-3048561/2/3
> Fax: +20-2-3048564
> email: mgad_at_itsoft.com.eg=20
> URL: www.itsoft.com.eg=20
>=20
> --=20
> Author: Mohamed Gad
> INET: mgad_at_itsoft.com.eg=20
>=20
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (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).
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/=20
--=20
Author: A. Bardeen
INET: abardeen1_at_yahoo.com=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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
Received on Mon Jun 26 2000 - 07:38:25 CDT
