Dear Sirs,
in the net, in Groups and in books, i don't find the answer for this
problem. I have the folling code. When i run inSQL*Plus, i get the
error message also when i start the code from vba via ADO. I was reading
some news and some tipps in the net. But i didn't found a tipp, that
really build a help for the problem. Whats wrong?
- VBA Code in Excel 97:
Sub main()
Dim OraConn As New ADODB.Connection
Dim RecSet As New ADODB.Record
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter
Dim ArtNummer As Long
Dim objError As ADODB.Error
On Error GoTo err_test
Let ArtNummer = 14114
OraConn.ConnectionString = DB.ThisDatabase
OraConn.Open
cmd.ActiveConnection = OraConn
Set param1 = cmd.CreateParameter("Artikel", adInteger, adParamInput, ,
ArtNummer)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("Fehler", adInteger, adParamOutput)
cmd.Parameters.Append param2
cmd.CommandText = "{CALL pa_kalkulation.GetKalkRecords(?,?)}"
Set RecSet = cmd.Execute
Do While Not RecSet.EOF
MsgBox RecSet.Fields(0)
Loop
Exit Sub
err_test:
MsgBox Error$
For Each objError In OraConn.Errors
MsgBox objError.Description
Next
OraConn.Errors.Clear
Resume Next
End Sub
- VBA Code End --------------------------
PL/SQL in ORACLE 8I:
- Header
CREATE or REPLACE PACKAGE pa_kalkulation as
TYPE kalkcur IS REF CURSOR;
PROCEDURE GetKalkRecords(p_cursor OUT kalkcur,
inArtNr IN NUMBER,
p_errorcode OUT NUMBER);
FUNCTION GetArtikel(inArtNr IN NUMBER,
p_errorcode OUT NUMBER)
RETURN kalkcur;
END pa_kalkulation
------------------------------------------------ Body
CREATE or REPLACE PACKAGE BODY pa_kalkulation AS
PROCEDURE GetKalkRecords(p_cursor OUT kalkcur,
inArtNr IN NUMBER,
p_errorcode OUT NUMBER) IS
BEGIN
p_errorcode := 0;
OPEN p_cursor FOR
select kalk.ARTIKEL,art.AR_ARTName, kalk.KALKULATIONSDATUM,
(kalk.Wareneinstand_2 - kalk.Verbrauchssteuer) as Wert1,
Wareneinstand_2, Verbrauchssteuer
from tbl_kalkulation kalk, tbl_artikel art
where kalk.artikel = inArtNr
and kalk.artikel = art.ar_artnr
and kalk.kalkulationsdatum = (select max(kalkulationsdatum)
from tbl_kalkulation
where artikel = inArtNr);
EXCEPTION WHEN OTHERS THEN
p_errorcode:= SQLCODE;
END getKalkRecords;
FUNCTION GetArtikel(inArtNr IN NUMBER,
p_errorcode OUT NUMBER)
RETURN kalkcur IS
p_cursor kalkcur;
BEGIN
p_errorcode := 0;
OPEN p_cursor FOR
SELECT ar_artname
FROM tbl_artikel
WHERE ar_artnr = inArtNr;
RETURN (p_cursor);
EXCEPTION
WHEN OTHERS THEN
p_errorcode:= SQLCODE;
END GetArtikel;
END pa_kalkulation;
---------------------------------------Error message
FEHLER in Zeile 1:
ORA-06550: Zeile 1, Spalte 7:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von
'GETKALKRECORDS'
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored
-------- End ----------------
I don't know any answer. Cann anybody help?
--
Gerd Schuckar
Received on Mon Mar 18 2002 - 14:59:20 CST
