Home » SQL & PL/SQL » SQL & PL/SQL » help for pls-00306
help for pls-00306 [message #720] Thu, 28 February 2002 03:49 Go to next message
monir
Messages: 22
Registered: February 2002
Junior Member
THIS IS MY SECOND SOS CALL FOR THIS PROBLEM PLS HELP ME
this is my procedure in my package:
CREATE OR REPLACE PACKAGE qGetDosNAs_xJEtP_DMAJ
IS
CURSOR C1 IS
SELECT DISTINCT DATE_DEBUT, DOSSIER, SEQ, DUREE, TYPE_DUREE FROM ROLES;
TYPE P_CUR IS REF CURSOR RETURN C1%ROWTYPE;

PROCEDURE qGetDosNAs_xJEtP_DMAJ_PROC
(
pDate_Derniere_MAJ IN date,
pDuree in number,
P_CURSOR IN OUT P_CUR
);
END qGetDosNAs_xJEtP_DMAJ;
/

CREATE OR REPLACE PACKAGE BODY qGetDosNAs_xJEtP_DMAJ
IS

PROCEDURE qGetDosNAs_xJEtP_DMAJ_PROC
(
pDate_Derniere_MAJ IN date,
pDuree in number,
P_CURSOR IN OUT P_CUR
)
is
BEGIN
open P_CURSOR for
SELECT DISTINCT DATE_DEBUT, DOSSIER, SEQ, DUREE, TYPE_DUREE FROM ROLES
WHERE (DATE_CREATION >= pDate_Derniere_MAJ)
AND (DOSSIER In (SELECT DOSSIER FROM DOSSIERS WHERE cause_designe = 0 AND cause_continue = 0))
AND (DUREE >= pDuree) AND (TYPE_DUREE = 'J')
ORDER BY DATE_DEBUT, DOSSIER;

END qGetDosNAs_xJEtP_DMAJ_PROC;
END qGetDosNAs_xJEtP_DMAJ;
/

but when i call it from my vb programm like that :
Public Function DBqGetDossierNonAssigne_xJoursEtPlus_DateMAJ_ADO(ByRef poRecordset As ADODB.Recordset, ByVal piDuree As Integer, ByVal pdDate_Derniere_MAJ As Date) As Boolean
'===============================================
Const csNOM_ROUTINE = "DBqGetDossierNonAssigne_xJoursEtPlus_DateMAJ"

On Error GoTo ErrHandler
'===============================================
Dim cmd As ADODB.Command
Dim prmDuree As ADODB.Parameter
Dim prmDate_Derniere_MAJ As ADODB.Parameter

DBqGetDossierNonAssigne_xJoursEtPlus_DateMAJ_ADO = False
'faire la connection
FaireConnection

' Ouvre un objet de commande à un paramètre.
Set cmd = New ADODB.Command

'APPELLE DU PROCEDURE EN ORACLE
cmd.CommandText = "qGetDosNAs_xJEtP_DMAJ.qGetDosNAs_xJEtP_DMAJ_PROC"
cmd.CommandType = adCmdStoredProc

' Obtient la valeur du paramètre et l'ajoute.
Set prmDuree = cmd.CreateParameter("pDuree", adInteger, adParamInput)
cmd.Parameters.Append prmDuree
prmDuree.Value = piDuree

Set prmDate_Derniere_MAJ = cmd.CreateParameter("pDate_Derniere_MAJ", adDate, adParamInput)
cmd.Parameters.Append prmDate_Derniere_MAJ
prmDate_Derniere_MAJ.Value = pdDate_Derniere_MAJ

' Crée un jeu d'enregistrements par exécution de la commande
Set cmd.ActiveConnection = cn
Set poRecordset = cmd.Execute

DBqGetDossierNonAssigne_xJoursEtPlus_DateMAJ_ADO = True

'===============================================
FreeObjects:
FermerCommand cmd
Exit Function

ErrHandler:
InscriptionErreurApplication err.Number, err.Description, csNOM_ROUTINE, , True
GoTo FreeObjects
'===============================================
End Function

i have the famous message :
pls-00306
any one help me pls
thanks
Re: help for pls-00306 [message #722 is a reply to message #720] Thu, 28 February 2002 04:31 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
see you parameter list, it has got (date,number and cursor) , but u r passing(number,date)

so there is a data type mismatch pass all the parameter and in correct order.

do not ask me how to pass the parameters, as i am not comfortable with VB

.........

PROCEDURE qGetDosNAs_xJEtP_DMAJ_PROC
(
pDate_Derniere_MAJ IN date,
pDuree in number,
P_CURSOR IN OUT P_CUR
)
Re: help for pls-00306 [message #724 is a reply to message #720] Thu, 28 February 2002 05:27 Go to previous message
monir
Messages: 22
Registered: February 2002
Junior Member
thank you very much
i have made 51 packages in this way and the cursor in parametre has no problem because the cursor's parametre pass automaticly.
and this is the way how we pass our parametre in VB program.
and even oracle and VB program accepted all the other 50 packages except that one
in addition the error is an oracle error and come when calling the procedure which was created succefully by oracle
pls take an other look
thanks
monir
Previous Topic: Storing Image's..
Next Topic: Re: JOIN SQL QUERY
Goto Forum:
  


Current Time: Fri Mar 29 06:07:31 CDT 2024