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 !