Solved

Retrieve Oracle LONG from a stored procedure with ADO

Posted on 2004-09-05
5
206 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

735 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