Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   how to pass blob type in procedures (http://www.go4expert.com/forums/pass-blob-type-procedures-t879/)

prashantSum 12Jun2006 11:29

how to pass blob type in procedures
 
hi all,
I have written a procedure which is as follows:
Code: SQL

CREATE procedure insemp (tid IN varchar2,tdocout IN blob)
 AS
begin
   INSERT INTO emp (id,docout) VALUES (tid,tdocout);
end insemp;

so second parameter(docout) to this procedure is of type blob, and docout which is in table emp is of type blob, so I am trying to insert tdocout into docout field of emp table.

but when I am trying execute this procedure from sql* plus as

exec insemp('1','ab');
I am getting the error

PLS-00306: wrong number or types of arguments in call to 'INSEMP'
ORA-06550: line 1, column 7:

and even from java program I am getting the same error.

when the data type is not blob I am able to execute the procedure properly.

So I want to know how to pass blob type to procedure, Is there is any other way or pl/sql does not support blob type?

shabbir 14Jun2006 10:37

Re: how to pass blob type in procedures
 
To insert a blob you need to execute the following SQL
Code: CPP

query="insert into emp(id,name,photo) values(" + txtid.Text + "," + "'" + txtname.Text + "'," + " :BlobParameter )";
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleType = OracleType.Blob;
blobParameter.ParameterName = "BlobParameter";

blobParameter.Value = blob; // Image or any blob data read into this variable
cmnd=new OracleCommand(query,conn);
cmnd.Parameters.Add(blobParameter);
cmnd.ExecuteNonQuery();

and so for the procedure also you need to be passing the blob in similar fashion.

shabbir 14Jun2006 10:39

Re: how to pass blob type in procedures
 
Also you can look at How to pass LOBs as parameters from Java to a Database Stored Procedure


All times are GMT +5.5. The time now is 13:35.