Re: oo4o stored procedure return

<obyrne_at_my-deja.com> wrote in message news:7v2l9i$rdv$1_at_nnrp1.deja.com...
> I am trying to use oo4o (Oracle Objects for OLE) via ASP (and VB) to
> return values from a stored procedure.
>
> Unfortunately, I can't find a way to set the size of an output varchar2
> parameter, so I'm getting the error:
>
> ORA-20001: ORA-06502: PL/SQL: numeric or value error: character string
> buffer too small
>
> Has anyone encountered this before? Here's the code (in VB):
>
> ------------------------
> Sub test()
>
> Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
> Set empdb = OO4OSession.OpenDatabase("db1", "user/pw", 0)
>
> empdb.Parameters.Add "p_cust_cd", 39, ORAPARM_INPUT
> empdb.Parameters("p_cust_cd").serverType = ORATYPE_NUMBER
> empdb.Parameters("p_cust_cd").Value = 39
>
> empdb.Parameters.Add "p_cc_ct_cd", 3, ORAPARM_INPUT
> empdb.Parameters("p_cc_ct_cd").serverType = ORATYPE_NUMBER
> empdb.Parameters("p_cust_cd").Value = 3
>
> 'here's the output varchar2.. how do we set its size?
> empdb.Parameters.Add "p_cc_contact_desc", Null, ORAPARM_OUTPUT
> empdb.Parameters("p_cc_contact_desc").serverType = ORATYPE_VARCHAR2
> empdb.Parameters("p_cc_contact_desc").MinimumSize = 180

Yes. It should be a bug.
You can add one line after declaring MinimumSize to sidestep the error:

    empdb.Parameters("p_cc_contact_desc").value = Space(180)

> 'fails here...
> Set PlSqlStmt = empdb.ExecuteSQL("Declare p_cust_cd number(8);
> p_cc_ct_cd number(8); p_cc_contact_desc varchar2(80);Begin
> get_cust_contacts(:p_cust_cd, :p_cc_ct_cd, :p_cc_contact_desc); end;")
>
> MsgBox empdb.Parameters("p_cc_contact_desc")
>
> End Sub
Received on Tue Oct 26 1999 - 21:49:21 CDT