How to call a stored procedure having blob datatype as input paramater

[syammerc]

Hi All,

I created a stored procedure having Blob datatype as an input parameter.

Ex: CREATE OR REPLACE procedure myproc(name in varchar2, blob_data in blob.

I am trying to call this procedure in Mercator using DBLOOKUP function with call syntax, but I am getting error as datatype of parameter is invalid.

Can anyone help me how to call the procedure having Blob as datatypes using DBLOOKUP function.

I am sucessfull using Type Tree for the stored procedure, but I want to get this sucessfull using DBLOOKUP function
or help me how to execute the above procedure on sqlplus window.

Thanks in advance.
Syam.

 

[janhes]

You'll need to post the rule you are using.

 

[syammerc]

Hi Janhes,

I am using the DBLOOKUP function as follows:

DBLOOKUP ("CALL myproc('"+Name:Record+ "', '" + text(SIZE(Blob:Record))+ "','"+Blob:Record+"' )",
"-DBTYPE ORACLE -CONNECT oracle8i -USER scott -PASSWORD tiger -T"
)

Please let me know if you require more information.

I am able to call the other procedures whose datatypes are varchar. I am getting failed only when I use BLOB datatypes in the stored procedure.

I hope I am doing some mistake while passing the parameter to BLOB type datatypes.
Please let me know my mistake.

Thanks
Syam.

 

[janhes]

I don't think you can turn a blob into text as it's binary. But I don't know the solution. One for Boca I think.

 

[janhes]

Just a thought. Can you use a hex representation of the BLOB? I do a lot of DBLOOKUPs which have a RAW data item as a parameter (not stored procedure though). I have to turn the RAW value into hex pairs using a map to do a lookup in a table where each binary character is turned into its hex character equivalent.

 

[BocaBurger]

Blob is binary, not sure what your SP does with it. Can you run the same SQL outside of DS TX?



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[syammerc]

Hi Janhes,

I am trying to insert Blob datatype into database using Stored procedure. (Stored procedure will do the business logic and insert data into the table, for your information there is no problem with the table)

I converted the Blob text into binary and placed in the parameter while calling the stored procedure. Please help me how to pass the Blob datatype parameter.

Atleast can you please help me how you have used raw data item. (Both for inserting and selecting data from table).
I am having a Blob as a text item input.
I am thinking I should use STREAMTOHEXTEXT function during inserting data into table having RAW as a datatype and HEXTEXTTOSTREAM function after selecting data.

 

[syammerc]

Hi BocaBurger,

I am trying to call the same procedure through sqlplus command prompt. But I am not sucessfull. I am trying to call through sqlplus. Please give your ideas or suggestions on this.

Thanks in advance
Syam.

 

[janhes]

If you're using text in the database why isn't is a CLOB?

 

[janhes]

Ok I had a play with this and it seems the data to be passed to the BLOB needs to be HEX caracters. Therefore you need to convert your text data to a hex string using STREAMTOHEX?
So I think you need something like
DBLOOKUP ("CALL ?=myproc('" + streamtohex(Blob:Record) + "')" ,
"-DBTYPE ORACLE -CONNECT oracle8i -USER scott -PASSWORD tiger -T"
)

 

[syammerc]

Hi janhes,

I tried by calling function as well as with procedure also, but I am not sucessfull.
I did not find the function STREAMTOHEX in my version 6.7, So I tried with STREAMTOHEXTEXT function.

If I use the above function I am not able to compile the map itself. When I Build the map it is giving me the error unable to open the MDQ file. I am really confused why map is expecting the MDQ file if I use the -DBTYPE command.

Please let me know how you made it sucessfull.

Thanks and Regards
SyamPrasad U

 

[janhes]

I think you need to make the call to the stored procedure in a -STMT command setting but I've never used it. See Oracle adapter manual chapter 4 GET function Without Database/Query file.
You are right streamtohex should have been streamtohextext.

 

[syammerc]

Hi Janhes,

In the above case, the procedure which Iam calling is not having out parameter, it is having BLOB datatype as in parameter.
So GET function will not help me. Because I have to parse the text item to stored procedure as BLOB parameter.

So please help me how to call the stored procedure which is having BLOB datatype as in parameter.

Thanks and Regards
Syam..

 

[janhes]

I meant get as in get the data with dblookup.

 

[eyetry]

OK, I have soooo little experience with stored procs I'm not sure I'll be any help but....

Did you define your tree(s) using DBID? I'd have thought it would have defined the blob appropriately, not not neccessarily the tree itself.

 

[BocaBurger]

If it doesn't work through SQL Plus, it won't work in a map. Get your DBA to help with your SQL.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!