Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Retrieve Oracle LONG from a stored procedure with ADO

Posted on 2004-09-05
5
Medium Priority
?
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 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