Stored Procedure w/ binary data parameter problems in Visual Basic
422557Jun 30 2004 — edited Jul 1 2004Howdy 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