Jump to content

Recommended Posts

Hello,

I am trying to see what is in a column of type LONG RAW.  Is it possible?  I have tried a number of conversion options and substrings, etc... but I just keep getting errors like illegal use of LONG datatype.  The database is either 11g or 12c, I'm not sure.  I am trying to find a specific string of characters and I am wondering if it could be embedded in this datatype.

Thanks

Share this post


Link to post
Share on other sites

Looks great, but I get an error.  My code is:

Quote

declare
a varchar2(255);
b long raw;
cursor c1 is select doc_document from eul5_documents where doc_name = 'jtt-FNS';
begin
open c1;
loop
fetch c1 into b;
exit when c1%notfound;
a:= UTL_RAW.CAST_TO_VARCHAR2(b);
dbms_output.put_line(a);
end loop;
end;
/

 

and the error I get is:

Error at line 7

Ora-06502: PL/SQL: numeric or value error

Ora-06502: at line 7

Line 7 is the loop statement.  Thoughts?  Thanks!

Share this post


Link to post
Share on other sites

Hello emolloy17,

Can you try increasing the size the VARCHAR2 variable "a"? Please make it the max (32767) and then check it.

Mr. Burleson's script works totally fine for me.

The error "Numeric or Value Error" shows that the space in the variable isnt enough to store the value.

 

Thank you,

Boobal Ganesan

Share this post


Link to post
Share on other sites

Thanks for the advice.  Strange though... it gives me the same error.  I double checked the table description... doc_document is listed as LONG RAW.  

I am running this from and editor in Toad.  I cannot execute the statement "set long 32767" (gives an ORA-00922: missing or invalid option).  Can you explain what this is for?  I omitted it from my executions thinking it was part of the create/insert.

Thanks

Share this post


Link to post
Share on other sites

Toad is NOT supported for Oracle!

Can you run it in SQL*Plus?

Like Boobal says, you must be able to run the “set long” command, which is only available in sqlplus!

You may be able to run it in toad if you mash the F5 key (execute as script) instead of F9.

Also consider using sql developer, an Oracle supported product!

Share this post


Link to post
Share on other sites

Ok.  I will respond when I am able to run it.  I have to coordinate getting SQL*Plus installed on my computer.  I just have Toad and the OBIEE client installed so far.  Hopefully it won't take to long to coordinate.

Thanks.

Share this post


Link to post
Share on other sites

Did you find the “execute as script” option in toad?

Installimg a sqlplus client is super easy!

At least we know that the code works, if you run it in sqlplus!

Share this post


Link to post
Share on other sites

Sounds good.  I can't just download software and install it like I would on my laptop.  I have to coordinate getting the files from the internal network, etc.  It'll be a couple days to get access and get it installed. I'll check back.  Thanks!

Share this post


Link to post
Share on other sites

Well, I think I've just received access to load some more software... but in the meantime I learned a valuable little detail about Toad.  For those interested, if you query a table and it returns (BLOB) as the result, double click on this.  It will pop up a little window that allows you to convert the data to HEX and Text and either save the data to a file or open it in a viewer.  I did both, but opening it in the viewer made it much easier to read through (only about 20 characters per line).  I was able to verify the data that I was looking for is embedded in this value.  Thanks!!  (I'll try the code once I get SQL*Plus installed too, but this keeps me moving) 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×