Re: Error ORA-06550 and PLS-00306

I'm not VB wiz but I suspect that the line: cmd.CommandText = "{CALL pa_kalkulation.GetKalkRecords(?,?)}" is setting up a call to GetKalkRecords with two parameters. But the GetKalkRecords procedure is supposed to have three parameters.

Gerd Schuckar <gerd.schuckar_at_t-online.de> wrote in news:3C965527.DCD05EDF_at_t-online.de:

>
> 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

-- 
Ken Denny
http://www.kendenny.com/

The nice thing about standards is there are so many to choose from.