how to pass blob type in procedures

Discussion in 'Oracle' started by prashantSum, Jun 12, 2006.

  1. prashantSum

    prashantSum New Member

    Joined:
    Oct 24, 2005
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    0
    hi all,
    I have written a procedure which is as follows:
    Code:
    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?
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    To insert a blob you need to execute the following SQL
    Code:
    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.
     
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice