Solved

Retrieve Oracle LONG from a stored procedure with ADO

Posted on 2004-09-05
5
205 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
ID: 11989369
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
ID: 11997728
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
ID: 12003633
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
ID: 12279854
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

808 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