Oracle Package - Error

[timmoser]

I created a package that contained one SP "s_user_authenticate" I call this procedure from a web page, everything works fine. Now I added another SP and the package compiled fine. But now the web page that worked fine with the first procedure is throwing the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 7: PLS-00703: multiple instances of named argument in list ORA-06550: line 1, column 7: PL/SQL: Statement ignored


I looked for the error on Google and it looks like the error is for duplicate arguments in the package. I don't see any though.

Please let me know what you think the issue may be. The package is below...



CREATE OR REPLACE PACKAGE czcs_pkg
AS
-- s_user_authenticate
TYPE userid IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE user_name IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE upassword IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE first_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE last_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE usergroups IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;

-- s_get_customer_data
TYPE custid IS TABLE OF NUMBER(18) INDEX BY BINARY_INTEGER;
TYPE customer_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE addr1 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE addr2 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE city IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE state IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
TYPE zipcode IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE logo_path IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
TYPE npc IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
TYPE update_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE create_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE isactive IS TABLE OF CHAR(3) INDEX BY BINARY_INTEGER;
TYPE update_userid IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;


PROCEDURE s_user_authenticate(
o_userid OUT userid,
o_user_name IN OUT user_name,
o_upassword IN OUT upassword,
o_first_name OUT first_name,
o_last_name OUT last_name,
o_usergroups OUT usergroups);

PROCEDURE s_get_customer_data(
o_custid IN custid,
o_customer_name OUT customer_name,
o_addr1 OUT addr1,
o_addr2 OUT addr2,
o_city OUT city,
o_state OUT state,
o_zipcode OUT zipcode,
o_logo_path OUT logo_path,
o_npc OUT npc,
o_update_date OUT update_date,
o_create_date OUT create_date,
o_isactive OUT isactive,
o_update_userid OUT update_userid);

END czcs_pkg;
/


CREATE OR REPLACE PACKAGE BODY czcs_pkg
AS

PROCEDURE s_user_authenticate(
o_userid OUT userid,
o_user_name IN OUT user_name,
o_upassword IN OUT upassword,
o_first_name OUT first_name,
o_last_name OUT last_name,
o_usergroups OUT usergroups)
IS
CURSOR user_cur IS
SELECT a.USERID, a.USER_NAME, a.UPASSWORD, a.FIRST_NAME, a.LAST_NAME, b.GID AS usergroups
FROM TMOSER.CZCS_USERS a, TMOSER.CZCS_USER_GROUPS b
WHERE a.USERID=b.USERID AND a.USER_NAME=user_name AND a.UPASSWORD=upassword;

recCount NUMBER DEFAULT 0;
BEGIN
FOR UserRec IN user_cur LOOP

recCount:= recCount + 1;

o_userid(recCount):= UserRec.userid;
o_user_name(recCount):= UserRec.user_name;
o_upassword(recCount):= UserRec.upassword;
o_first_name(recCount):= UserRec.first_name;
o_last_name(recCount):= UserRec.last_name;
o_usergroups(recCount):= UserRec.usergroups;

END LOOP;

END s_user_authenticate;

PROCEDURE s_get_customer_data(
o_custid IN custid,
o_customer_name OUT customer_name,
o_addr1 OUT addr1,
o_addr2 OUT addr2,
o_city OUT city,
o_state OUT state,
o_zipcode OUT zipcode,
o_logo_path OUT logo_path,
o_npc OUT npc,
o_update_date OUT update_date,
o_create_date OUT create_date,
o_isactive OUT isactive,
o_update_userid OUT update_userid)

IS
CURSOR customer_cur IS
SELECT CUSTID, CUSTOMER_NAME, ADDR1, ADDR2,
CITY, STATE, ZIPCODE, LOGO_PATH,
NPC, UPDATE_DATE, CREATE_DATE,
ISACTIVE, UPDATE_USERID
FROM TMOSER.CZCS_CUSTOMERS
WHERE CUSTID=custid;

recCount NUMBER DEFAULT 0;

BEGIN
FOR CustRec IN customer_cur LOOP

recCount:= recCount + 1;

o_customer_name(recCount):= CustRec.customer_name;
o_addr1(recCount):= CustRec.addr1;
o_addr2(recCount):= CustRec.addr2;
o_city(recCount):= CustRec.city;
o_state(recCount):= CustRec.state;
o_zipcode(recCount):= CustRec.zipcode;
o_logo_path(recCount):= CustRec.logo_path;
o_npc(recCount):= CustRec.npc;
o_update_date(recCount):= CustRec.update_date;
o_create_date(recCount):= CustRec.create_date;
o_isactive(recCount):= CustRec.isactive;
o_update_userid(recCount):= CustRec.update_userid;

END LOOP;

END s_get_customer_data;

END czcs_pkg;
/



Thanks in advance for your help.

 

[sem]

I suppose that you should look for error in your CALL rather than in package.

Regards, Dima

 

[timmoser]

I'll checkout the call, but the original SP and call have not changed. The only thing that has changed it the additional SP in the package.

Oracle is completely new to me so I could easily be missing something in the call that didn't matter when I only had one SP in the package.


Here's the call (being passed from ASP):

"{call czcs_pkg.s_user_authenticate(?,?,{resultset 10,o_userid, o_first_name, o_last_name, o_usergroups})}"


Thanks for your help.

 

[Turkbear]

Hi, Here are some details about the error and it appears the answer is what Dima mentioned, an improper call.

PLS-00703 multiple instances of named argument in list

Cause: Two or more actual parameters in a subprogram call refer to the same formal parameter.

Action: Remove the duplicate actual parameter.

The 6550 error just means that a Pl/Sql error occured.