Stored Proc and UseOCI7

[Eric]

Hello,

I'm uing ODAC 5.50.0.15 for Delphi 6 and i have a question about Stored Proc.

A procedure seems to be compiled but when I call it, the arguments are
reversed. Then i obtains ORA-06550 error.

If UseOCI7 = true, then the call of the stored proc is correct.

Could you explain me why ?

[Alex]

We couldn't reproduce the problem. Please send us complete sample to demonstrate it and include script
to create server objects to ODAC support address.

[Eric]

I cannot provide you examples since this problem occur by chance.

When I look at the traces in DBMonitor, it appears that with the prepration and the execution of the procedure, the arguments are reversed compared to the declaration of the procedure. ODAC then does not say anything to the preparation but generates an error at the time of the execution.
Therefore, I succeeded in using the procedure with a program using BDE, or by using SQL+ for example.

Lastly, it seems that this error occur when the procedures were modified then compiled.

Here the solutions which currently enable me to correct these problems:
I open the procedure, I add a space, and I compile it...
Another solution consists in starting again the database.

NB: I'm using TOAD to create stored proc, with debug option...

[Alex]

Please specify your full Oracle Client/Server versions (if you have several clients then specify all of them), OS version, and also specify if you use Net option.

[Eric]

Oracle Server: 9.2.0.4
Oracle Client: 9i for all clients
Server OS: AIX 5.3
Client OS: 2000 and XP

I'm not using Net Option, only OCI calls

[Alex]

We couldn't reproduce your problem. We discover such problem on 8i servers, but not in older Oracle versions.
Pls. specify script of your StoredProc and if it is possible try to create StoredProc using ODAC (for example TOraScript component).

[Eric]

1/
In fact, the first time that I encountered this problem, I used indeed a database on a 8i Server.
On the other hand I do not understand what you want to say by "but not in older Oracle versions" because I actually use a most recent server version (9.2.0.4).

The first time that I encoutered this problem, I already put the question to you to know how to solve the problem, and Paul had answered me this:

If you receive pName of the last procedure argument for i = 1, it is
certainly OCI bug. Odac cannot deterimine the right parameters sequense for
constructing stored procedure call. Parameters are bound by names.
You can use TOraQuery to execute PL/SQL block. Plese try execyte stored
procedure using Oracle Client of different version.
OCI73 and OCI8 has different set of functions. OCI8 was developed by Oracle
to support new types and was extended in Oracle Server 9. With basic types
OCI73 and OCI8 must work similar. See Oracle documentation for more details.

But I do not find this answer complete because how to explain whereas other tools such as the BDE, SQL+ or TOAD function correctly. There is obligatorily a difference by using ODAC !!!

And I be still not explained why if I disabled UseOCI7, the arguements are reversed.
I also find strange to have to use OCI7 calls with a 9.x Oracle Version !

I want to believe well that the problem comes from Oracle but I do not find any explanation of it, on Metalink for example.

2/
I do not undestand why I should create stored proc with a component like TOraScript.
Which would be the differences with a stored procedure creates with TOAD for example ?

Here the script of one of the stored procedures for which I had the problem:

CREATE OR REPLACE FUNCTION GET_NEXTCODE(TheTableName VARCHAR2, ThePrefixe VARCHAR2) RETURN NUMBER IS
NextNumber NUMBER(10);
NvlPrefixe VARCHAR2(40);
BEGIN
NvlPrefixe := NVL(UPPER(ThePrefixe), 'PREFIXE');

-- Get the current number
BEGIN
SELECT SequenceNumber INTO NextNumber
FROM Ref_CII
WHERE TableName = UPPER(TheTableName)
AND Prefixe = NvlPrefixe
FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LOCK TABLE REF_CII IN Exclusive MODE;
NextNumber := 0;
END;

-- Get the next number
NextNumber := NextNumber + 1;

-- update table
UPDATE Ref_CII
SET SequenceNumber = SequenceNumber + 1
WHERE TableName= UPPER(TheTableName)
AND Prefixe = NvlPrefixe;
IF SQL%NOTFOUND THEN
INSERT INTO Ref_CII(TableName, Prefixe, SequenceNumber)
VALUES (UPPER(TheTableName), NvlPrefixe, 1);
END IF;
RETURN NextNumber;
END;
/

When I used this storec proc, "ThePrefixe" argument can be NULL.
In Ref_CII table, "TableName" can't be NULL.

If the arguments are reversed, I have a big problem !

[Alex]

Sorry "but not in older Oracle versions" was my misprint. I mean - all oracle version since 9i. We have tested your storedproc on 9.2.0.1, 9.2.0.4 and 9.2.0.6 Oracle servers with 9.2.0.1 and 9.2.0.6 client versions and always got right param order. If you can create sample that stable reproduces this issue pls.send it to our support address and we will do our best.