Re: AddTable method with OO4O

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1 Dec 1998 08:57:00 GMT
Message-ID: <740b0s$p9i$1@news00.btx.dtag.de>

HI Mark,

mark.panarusky_at_alliedsignal.com schrieb:
>
> Has anyone successfully used the AddTable method with Oracle Objects for OLE
> (OO4O)?
>

Yes I did.

> I am trying to pass a PL/SQL table to VB5 as a parameter, but keep getting
> "ORA-06512 SQL Execution Error". The stored procedure works fine within the
> Oracle environment. Also, the same procedure works if I remove the table from
> the parameter list.
>
> A partial VB code snippet:
>
> OraDatabase.Parameters.AddTable "sampleIDtable", ORAPARM_OUTPUT,
> ORATYPE_NUMBER, 50
> Set OraPArray1 = OraDatabase.Parameters("sampleIDtable")
>
> SQLstring = "BEGIN ops$mclims.get_denier_results(" & _
> merge & "," & machine & "," & sideNumber & "," & doff & _
> ",:submission_id,:submitter,:sampleIDtable,:return_status); END;"
> OraDatabase.ExecuteSQL (SQLstring)
>
> The stored procedure header is:
>
> Procedure GET_DENIER_RESULTS
> (merge IN NUMBER,
> machine IN NUMBER,
> side IN NUMBER,
> doff IN NUMBER,
> submission_id OUT NUMBER,
> submitter OUT VARCHAR2,
> sampleid OUT VB_TABLE_DECLARATIONS.LIMS_ID_TABLETYPE,
> return_status OUT NUMBER)
>
> Thank you.

Befor doing long explains just an fragment of our project:

        ...
    oLclOraDatabase.Parameters.AddTable "awArt", ORAPARM_INPUT, ORATYPE_NUMBER, wLclAnzahlPersonen, 22

    oLclOraDatabase.Parameters.AddTable "adwBezugsnummer", ORAPARM_INPUT, ORATYPE_NUMBER, wLclAnzahlPersonen, 22

    Set oLclOraPArray1 = oLclOraDatabase.Parameters("awArt")     Set oLclOraPArray2 = oLclOraDatabase.Parameters("adwBezugsnummer")

     
    For wLclIndex = 0 To wLclAnzahlPersonen - 1	'paramarrays start with
index 0 !!!!!!!!
        oLclOraPArray1.put_Value awArt(wLclIndex + 1), wLclIndex
        oLclOraPArray2.put_Value adwBezugsperson(wLclIndex + 1),

wLclIndex

    Next wLclIndex

        ...

The Call:

    szLclSQL = "Begin T1.PACK_DBACCESS.SpeicherNachweis(:ArraySize ,"
    szLclSQL = szLclSQL & ":awArt ,"
    szLclSQL = szLclSQL & ":adwBezugsnummer ,"
    szLclSQL = szLclSQL & ":szInfo ,"
    szLclSQL = szLclSQL & ":szAktion ,"
    szLclSQL = szLclSQL & ":dAktionsDatum  ,"
    szLclSQL = szLclSQL & ":dAktionsZeit  ,"
    szLclSQL = szLclSQL & ":dWiedervorlageDatum  ,"
    szLclSQL = szLclSQL & ":dWiedervorlageZeit  ,"
    szLclSQL = szLclSQL & ":fWVErledigt ,"
    szLclSQL = szLclSQL & ":wKBNUMMER,"
    szLclSQL = szLclSQL & ":dwNWNUMMER"
    szLclSQL = szLclSQL & ");"
    szLclSQL = szLclSQL & " End;"

    oGblOraSession.DbBeginTrans
    fLclExecutingTrans = True
    DoEvents

     
        oLclOraDatabase.DbExecuteSQL (szLclSQL)
    

    oGblOraSession.DbCommitTrans
    fLclExecutingTrans = False
    DoEvents

For explanation:

wLclAnzahlPersonen is the upper bound of the arrays awArt() and adwBezugsperson().

awArt() is an array of integers and adwBezugsperson() is an array of longs. Entries with the same index number correspond to each other but since paramarrays can hold only scalar types I've got to go this way.

Ooh, the called SP:

	PROCEDURE SpeicherNachweis 	(wArraySize IN INTEGER, 
					awArt IN TYPEN.T_NUMARRAY, 
					adwBezugsnummer IN TYPEN.T_NUMARRAY, 
					szInfo IN  VARCHAR2,
					szAktion IN  VARCHAR2,
					dAktionsDatum IN  Date ,
					dAktionsZeit IN  Date ,
					dWiedervorlageDatum IN  Date ,
					dWiedervorlageZeit IN  Date ,
					fWVErledigt IN  NUMBER ,
					wKBNUMMER IN  NUMBER,
					dwNACHWEIS IN OUT NUMBER
				);

The type TYPEN.T_NUMMARRAY is defined in the package TYPEN as :

        TYPE T_NUMARRAY is table of NUMBER index by BINARY_INTEGER;

>
> Mark Panarusky
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Tue Dec 01 1998 - 02:57:00 CST