• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Retrieve Oracle LONG from a stored procedure with ADO

Hello,
I have a VB application which gets datas from an oracle database. I use an ADO connection. I want to execute an oracle stored procedure and get the result. The problem is that the stored procedure returns a TABLE OF LONG. Here is it :

CREATE OR REPLACE  PACKAGE "AP"."AP_SERV_PR"  AS

TYPE tDomaine IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
TYPE tNombre IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE tLbLib IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
TYPE tLbExpr IS TABLE OF LONG INDEX BY BINARY_INTEGER;
TYPE tBTN IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;

PROCEDURE ramene_adh(pARBD_ID IN NUMBER,pNOORD OUT tNombre,pLBLIB OUT tLBLIB,pLbExpr OUT tLBEXPR,pBTN OUT tBTN,pAGEMINADH OUT tNombre,pAGEMAXADH OUT tNombre,pTYAGE OUT tDomaine, pTYDEBADH OUT tDomaine,pNBDUREE OUT tNombre,pTYDUREE OUT tDomaine);

END;

Here is the call :

Private Sub s_load()

Dim lsCmd As String
Dim lnNb As Integer
Dim loAdoParam As ADODB.Parameter
Dim AdoCommand As New ADODB.Command

Set loAdoParam = AdoCommand.CreateParameter("pID", adNumeric, adParamInput, , 20111)
AdoCommand.Parameters.Append loAdoParam

lsCmd = "{call ap_serv_pr.get_adh(?,{resultset 100,pNOORD,pLBLIB,pLBEXPR,pBTN,pAGEMINADH,pAGEMAXADH,pTYAGE,pTYDEBADH,pNBDUREE,pTYDUREE})}"

Set AdoCommand = New ADODB.Command
Set AdoCommand.ActiveConnection = AdoConnection(IdRdo)
AdoCommand.CommandText = lsCmd
AdoCommand.CommandTimeout = 100000

AdoCommand.Execute
End Sub

On the "AdoCommand.Execute", i get an sql error : ORA-02005 :  nonvalid implicit length (-1) transferred to a definition function for the order { call ap_serv_pr.ramene_adh(?, {resultset 100, pNOORD, pLBLIB, pLBEXPR, pBTN, pAGEMINADH, pAGEMAXADH, pTYAGE, pTYDEBADH, pNBDUREE, pTYDUREE })}

I also do :
Private Sub s_loadPred()

Dim lsCmd As String
Dim lnNb As Integer
Dim loAdoParam As ADODB.Parameter
Dim AdoCommand As New ADODB.Command

Set loAdoParam = AdoCommand.CreateParameter("pID", adNumeric, adParamInput, , 20111)
AdoCommand.Parameters.Append loAdoParam

lsCmd = lsCmd = "SELECT CDNOM, LBLIB, TYCLSEVAL, TYETAT, DTETAT, TYVAL, PRAN_PRAN_ID, LBMETH, LBEXPR, LBDESC FROM OT_PRED WHERE PRAN_ID = " & mnPRAN_ID

Set AdoCommand = New ADODB.Command
Set AdoCommand.ActiveConnection = AdoConnection(IdRdo)
AdoCommand.CommandText = lsCmd
AdoCommand.CommandTimeout = 100000

AdoCommand.Execute
End Sub

where LBEXPR is a LONG column and it orks fine, I get all that I want.

Is there a problem between LONG and stored procedure ?
can you help me ?

thanks
0
sroz
Asked:
sroz
1 Solution
 
DhaestCommented:
Perhaps the example at http://www.learnasp.com/learn/oraclerecordsetsado.asp can help you on your way.
Retrieving the data in a recordset !
0
 
InformativeCommented:
I dont understand when you say

"problem is that the stored procedure returns a TABLE OF LONG"

Is the problem that the procedure returns a table?
Is the problem that the procedure returns data types of LONG and you want some other type?
It would help if you clarify the question a bit further.  Perhaps paste a couple of rows of your current table and tell us what is wrong with it.

0
 
srozAuthor Commented:
i found a solution, i cast long column into varchar2(32767) and it works fine.

i do that :

PROCEDURE get_adh(pID IN NUMBER,
                              pO_ID OUT tNombre,pP_ID OUT tNombre,pEXPR OUT tLbExpr) AS

  CURSOR c_cursor IS
    SELECT O.O_ID,P.P_ID,PR.EXPR
    FROM TABLE1 O,TABLE2 P,TABLE3 PR
    WHERE O.ID1 = pID
    AND O.ID1 = P.O_ID1(+)
    AND O.O_ID = P.O_ID(+)
    AND O.P_ID = PR.P_ID(+);

  c NUMBER DEFAULT 1;
  vl_expr LONG;

  BEGIN
    FOR cadh IN c_adhesion
    LOOP
      pO_ID(c) := cadh.O_ID;
      pP_ID(c) := cadh.P_ID;
      vl_expr := cadh.EXPR;
      pExpr(c) := SUBSTR(vl_expr,1,32676);
      c := c + 1;
    END LOOP;
  END;
0
 
Computer101Commented:
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now