Home » Infrastructure » Windows » Call Stored Procedure and pass BLOB
Call Stored Procedure and pass BLOB [message #100830] Tue, 18 November 2003 09:20 Go to next message
Douglas L. Setzer, II
Messages: 1
Registered: November 2003
Junior Member
I'm using ASP (classic, not ASP.NET) and I can potentially use Visual Basic 6.

I'm trying to call a stored procedure that's defined as:
CREATE OR REPLACE PROCEDURE SP_DLS_TEST
( TEST_ID in NUMBER,
BLOB_VALUE in BLOB,
OUT_ID out NUMBER
)
IS
BEGIN
SELECT TEST_ID + 25 into out_id FROM dual;
END;
/

I'm trying to figure out how to pass the value for the "BLOB_VALUE" parameter.

I've found a boatload of documentation on how to update a recordset field with a binary value, but none that show how to pass a BLOB to a stored procedure.

I've tried both connection strings:
cCONNECT_STRING = "Provider=OraOLEDb.Oracle;Data Source=EPA_CDX;PwdChgDlg=0"
cCONNECT_STRING = "Provider=MSDAORA;Data Source=epa_cdx"

My ASP code, basically looks like:
Set oStream = Server.CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = adTypeBinary
oStream.LoadFromFile("C:myimage.gif") '*** I'm POSITIVE this exists on the web server ***'

Set oCmd = Server.CreateObject("ADODB.Command")

oCmd.ActiveConnection = oConn
oCmd.CommandText = "begin archive.sp_dls_test(?, ?, ?); end;"

oCmd.Parameters.Append oCmd.CreateParameter("TEST_ID", adInteger, adParamInput)
oCmd("TEST_ID").Value = iRandomId

oCmd.Parameters.Append oCmd.CreateParameter("BLOB_VALUE", adInteger, adParamInput)
oCmd("BLOB_VALUE").Value = oStream.Read *** ERROR HERE ***

oCmd.Parameters.Append oCmd.CreateParameter("OUT_ID", adInteger, adParamOutput)

oCmd.Execute

Response.Write "TEST ID = " & oCmd("TEST_ID") & "
----------------------------------------------------------------------
" & vbCrLf
Response.Write "OUT ID = " & oCmd("OUT_ID") & "
----------------------------------------------------------------------
" & vbCrLf

I DO have values for the ADO constants. I AM sure that the file exists. I get the error when I attempt to assign the command object's parameter for "BLOB_VALUE".

The error message is:
ADODB.Parameter error '800a0d5d'

Application uses a value of the wrong type for the current operation.

Any help is appreciated!

Thanks in advanced!
Re: Call Stored Procedure and pass BLOB [message #442714 is a reply to message #100830] Wed, 10 February 2010 02:16 Go to previous message
Dinesh_bec
Messages: 1
Registered: February 2010
Junior Member
Hi,

Did you get solution to your problem?I am facing same issue.Please let me know ASAP.

Thanks,
Dinesh
Previous Topic: disable oracle 10g service
Next Topic: Running a batch file from oracle procedure
Goto Forum:
  


Current Time: Fri Mar 29 06:01:20 CDT 2024