Solved

Retrieve Oracle LONG from a stored procedure with ADO

Posted on 2004-09-05
5
199 Views
Last Modified: 2013-12-25
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
Comment
Question by:sroz
5 Comments
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
Perhaps the example at http://www.learnasp.com/learn/oraclerecordsetsado.asp can help you on your way.
Retrieving the data in a recordset !
0
 
LVL 3

Expert Comment

by:Informative
Comment Utility
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
 

Author Comment

by:sroz
Comment Utility
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now