Discussion :

[fortlow]

Voilà, j'ai une application web en SSJS hébergé sur un serveur netscape entreprise 3.63 et qui communique avec une base oracle 9i.
La précédente version de la base oracle était la 7.4.3. Après migration de la base oracle de la version 7.4.3 vers la 9.2, j'ai des soucis avec mon IHM notamment avec le liveWire.
Je vous donne ici un condensé des erreurs que j'obtiens quand j'interroge la base oracle 9i depuis les IHM :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
[SERVERERROR] Error from Server: ORA-06550: line 2, column 1: PLS-00306: wrong number or types of arguments in call to 'REMOVETENANT' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

accompagné au niveau des traces du serveur web netscape de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
Database connection 'database' : StoredProc function: SQL statement = "scs_nt.RemoveTenant" 
 
Error in database services:
LiveWire Error: Specified stored procedure does not exist 
 
Error in database services:
Vendor error 1: 15 
 
Error in database services:
Vendor error 2: 0

ou parfois

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Error in database services:
[SERVERERROR] Error from Server: ORA-06550: line 2, column 1: PLS-00306: wrong number or types of arguments in call to 'REMOVEISP' ORA-06550: line 2, column 1: PL/SQL: Statement ignored

La question est de savoir si le problème provient de la base ou du serveur netscape notamment le fameux service LiveWire.

Pour ma part, je pense que la base n'est pas en cause car les requêtes qui plante depuis l'IHM fonctionnent bien depuis PL/SQL Developer par exemple.

Le plus étrange c'est que les mêmes requêtes qui plantent fonctionne une fois sur deux

Donc si quelqu'un a une quelconque expérience avec Netscape server/Oralce et a eu des soucis similaire, je serai ravi d'avoir son point de vue. Moi j'ai fait le tour et je comprends toujours pas

Merci d'avance.
Cordialement,
fortlow[/code]

[LeoAnderson]

Merci d'éditer votre sujet pour lui donner un titre décrivant sommairement votre problème ! ;-)

[bouyao]

un peut de lécture sur metalink

Troubleshooting PLS-306/ORA-6550 From Microsoft APIs
=========================================================

Error: PLS-306
Text: wrong number or types of arguments in call to '%s'
---------------------------------------------------------------------------
Cause: This error occurs when the named subprogram call cannot be matched
to any declaration for that subprogram name.


The Microsoft ODBC specification does not provide a data type mapping for
REF CURSORS. Hence, you should not bind a placeholder in your application
for REF CURSORS returned from a stored procedure. Special
handling is provided by the ODBC Driver or OLEDB provider to handle a
REF CURSOR returned from a stored procedure. The following information
may be helpful in understanding what is occurring when a PLS-306 followed by
an ORA-6550 is thrown by the PL/SQL runtime engine inside the database.

Microsoft's OLEDB Provider/ODBC Driver for Oracle and Oracle's OLEDB
Provider/ODBC driver implement some features differently. Depending on the
the driver being used, the PLS-306 followed by a ORA-6550 may be propagated
to the application or you may notice these errors in LEVEL 16 Net trace when
no error has been reported to the application.

Consider the following simple stored procedure as an example:

PL/SQL
=======
create or replace procedure SomeDependency is
begin
null;
end;
/
create or replace package adotst as
type empcur is ref cursor;
end;
/
create or replace procedure GetEmps(ecur in out adotst.empcur) is
begin
somedependency();
open ecur for select * from emp;
end;
/


Using ActiveX Data Objects (ADO), you could call the stored procedure
with the following code:

Visual Basic
=============
Private Sub Command1_Click()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim results As String
strCnn = "provider=oraoledb.oracle;data source=orcl;user id=scott;password=tiger;plsqlrset=1"
con.Open strCnn
cmd.CommandText = "{call getemps()}"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = con
Set rst = cmd.Execute
results = "enames found: "
While Not rst.EOF
results = results & rst.Fields(1).Value & ", "
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
MsgBox results
End Sub

Note: The code does not bind a data type for the REF CURSOR parameter in the call
to the GetEmps stored procedure.

Executing the call to the stored procedure with the exact SQL statement
supplied in the above code will result in an error from the database, as
the "wrong number or types of arguments" is received. The Oracle Call Interface
(OCI) requires that the REF CURSOR parameter be bound. The implementation of
of this feature within the provider or driver determines how an application
should handle a REF CURSOR.



Oracle's OLEDB Provider and ODBC driver
=======================================
With Result Sets support enabled within the DSN configuration or by setting the
PLSQLRSET property on the command object when using the OLEDB Provider, Oracle
will first make a round trip to the database to describe the stored procedure
in order to derive the parameter list, to determine if a REF CURSOR is one of
the parameters being returned. The driver performs the binding of the
REF CURSOR in the underlying OCI code. This results in an additional round
trip to the database, which introduces additional overhead.

In order to help minimize additional overhead, Oracle's OLEDB Provider and Oracle's
ODBC driver require that you declare that the REF CURSOR is going to
be returned. With ODBC, this is accomplished by setting the "Enable Result Sets"
option in the DSN, and with the OLEDB Provider by setting the PLSQLRSET
property to TRUE. The Oracle Provider for OLEDB has REF CURSOR support
"turned off" by default, whereas ODBC has it "turned on" by default.

With the OLEDB Provider, you can avoid the extra round trip that would occur in
cases where there are no REF CURSORS returned by setting the option on or off
at the Command object or statement level, as well as the Connection object.
With ODBC, you can only enable it at the Connection level.

Failure to set the appropriate option when A REF CURSOR is returned will result
in the driver/provider calling the stored procedure with exactly the SQL
statement passed to it, resulting in an error.

As both the ODBC driver and OLEDB Provider first describe the procedure, then execute
it to obtain the REF CURSOR, this generally results in increased efficiency as only
two round trips are required to the database in the case of REF CURSOR procedures. However,
this can also generate an undesirable side effect of propagating the PLS-306 followed by an
ORA-6550 out to the application if the procedure is indescribable, perhaps due
to a dependency of the procedure having become invalid. This is expected
behavior in the database, and can be illustrated with the following example in
SQL*Plus:

SQL> desc GetEmps
PROCEDURE GetEmps
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ECUR REF CURSOR IN/OUT

SQL> alter procedure SomeDependency compile;

Procedure altered.

SQL> desc GetEmps
ERROR:
ORA-24372: invalid object for describe


SQL> execute GetEmps();
BEGIN GetEmps(); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETEMPS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> desc GetEmps;
PROCEDURE GetEmps
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ECUR REF CURSOR IN/OUT

SQL>


As shown above, the procedure cannot be described because its dependencies have
become invalid. Attempting to execute the procedure forces recompiliation and thus
the object is in a valid state.

This is the behavior for both the Oracle OLEDB Provider and ODBC drivers: they are not
able to describe the procedure in order to determine the correct parameter info
and will try to execute the procedure with the parameter information supplied
by the code (in procedures that do not return REF CURSORS, this will result in a
recompilation, so the execution succeeds) and as such an error is propagated out
to the application. However, executing the procedure forces the procedure to be
recompiled even if the execution errors, so the second attempt to execute the
procedure will succeed. This will often be the case for an intermittent
PLS-306 followed by an ORA-6550.



Microsoft's OLEDB Provider and ODBC Driver
============================================
Microsoft's OLEDB Provider and ODBC driver for Oracle do not perform the
round trip at the beginning to determine if the procedure will return a
REF CURSOR. Instead, the provider/driver attempts to execute the procedure
with the information supplied in the code.

As expected, the database returns the PLS-306 followed by an ORA-6550 to the
provider/driver. The provider/driver then performs a round trip to the database
and executes DBMS_DESCRIBE to return the parameter information. Once the parameter
information is known, the procedure is executed again with the correct parameter
signature.

If the execute on the DBMS_DESCRIBE package fails, an error will be returned
back to the application layer.

Microsoft's OLEDB Provider and ODBC Driver for Oracle will not fail because of
invalid dependencies issues since the driver will first try to execute the
procedure followed by a describe. However, this methodology also results in 3
round trips to the database for a procedure that returns a REF CURSOR, as
compared to 2 with the Oracle drivers.