Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Stored Procedure w/ binary data parameter problems in Visual Basic

422557Jun 30 2004 — edited Jul 1 2004
Howdy all.

I am having a problem calling stored procedures with a BLOB parameter. I have tried changing the paramater other data types to see if it would work, but with no success. I am calling the stored procedure from Visual Basic using ADO. I am using the Oracle ODBC Driver, Release 9.2.0.4.0. I have tried changing the setup of the ODBC a good bit because that has fixed several problems for me in the past; however, it did not fix my current problem.

Here is what I am trying to do. I have a function like the folowing:

<BEGIN --------------------------------------->
CREATE OR REPLACE FUNCTION PAGEFORMATSINSERT(
p_ObjectFormatCode_ID IN RAW DEFAULT NULL,
p_PA_ID IN RAW DEFAULT NULL,
p_Name IN VARCHAR2 DEFAULT NULL,
p_FormatData IN BLOB DEFAULT NULL,
p_PF_ID IN OUT RAW )
RETURN INTEGER
AS

...
BEGIN
...
INSERT INTO PAGEFORMATS (PF_ID, ObjectFormatCode_ID, PA_ID, Name, FormatData) /* <---- this FormatData column is a BLOB column */
VALUES (p_PF_ID, p_ObjectFormatCode_ID, p_PA_ID, p_Name, p_FormatData)
...
END PAGEFORMATSINSERT;
<END ----------------------------------------->

The FormatData parameter has a data type of BLOB. In my Visual Basic, I have my ADODB.Command object. I am setting the CommandText of the Command object to "{? = call PageFormatsInsert(?, ?, ?, ?, ?)}". In order to set the parameter value for the BLOB data type, I am calling the AppendChunk function of the Command object - passing it a Byte array.

I am getting the folling error:
ERROR: -2147467259 [Oracle][ODBC][Ora]ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'PAGEFORMATSINSERT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If I change the FormatData parameter to a LONG RAW parameter, I get the following error:
ERROR: -2147467259 [Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 1


I am at a loss as to how to get binary data into by Oracle database. I need to do it using stored procedures. How can I set up my stored procedure or table to do what I want it to do? Should I change my table definition? Are there some settings in the ODBC connection I can tweak? How can I get the stored procedure to accept my call from VB ADO?

Any help would be appreciated.
wally
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 29 2004
Added on Jun 30 2004
2 comments
1,102 views