ORA-06550

[cr]

I get the following error in asp.net when trying to execute a stored procedure in a package using the OracleDataSource Component:
Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand are valid.

ORA-06550: Line 2, column 3:
PLS_00306: wrong number or types of arguements in call to 'SEL_TAX"
ORA-06550: line 2, column 3:
PL/SQL: Statement Ignored

The funny things is that I can compile the stored procedure, the package independently using the Database Explorer. I can also execute the sql statement inside the OracleDataSource in text mode. It only fails when I try to call the stored procedure inside the package.


create or replace PACKAGE "JANTZEN_TAX_PCK" IS
TYPE t_cursor is REF CURSOR;
PROCEDURE SEL_TAX (cur_select_tax OUT t_cursor) ;

END "JANTZEN_TAX_PCK";

create or replace PACKAGE BODY "JANTZEN_TAX_PCK" IS

-- Returns all Tax States

PROCEDURE SEL_TAX (cur_select_tax OUT t_cursor) IS

BEGIN -- executable part starts here

OPEN cur_select_tax FOR
SELECT TAX.TAXID, COUNTRY.COUNTRYDESC, STATE.STATEDESC, TAX.TAXRATE FROM COUNTRY INNER JOIN
STATE ON COUNTRY.COUNTRYID = STATE.COUNTRYID INNER JOIN
TAX ON STATE.STATEID = TAX.STATEID;

END SEL_TAX;



END "JANTZEN_TAX_PCK";

create or replace
PACKAGE "JANTZEN_TAX_PCK" IS
TYPE t_cursor is REF CURSOR;
PROCEDURE SEL_TAX (cur_select_tax OUT t_cursor) ;

END "JANTZEN_TAX_PCK";


Thanks,
Christian

[Alexey]

Please provide the definition of all needed database objects.

[cr]

CREATE TABLE "ECOMJZ"."TAX"
( "TAXID" NUMBER(*,0) NOT NULL ENABLE,
"TAXRATE" NUMBER(9,4) NOT NULL ENABLE,
"STATEID" NUMBER,
CONSTRAINT "TAX_PK" PRIMARY KEY ("TAXID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ;

CREATE OR REPLACE TRIGGER "ECOMJZ"."TAX_TRIG_TAXID_INCRE"
BEFORE
INSERT
ON "ECOMJZ"."TAX"
FOR EACH ROW
DECLARE

BEGIN -- executable part starts here

for c1 in (select TAXID.nextval new_id from dual)
loop
:new.TAXID :=c1.new_id;
end loop;


END;
/
ALTER TRIGGER "ECOMJZ"."TAX_TRIG_TAXID_INCRE" ENABLE;



CREATE TABLE "ECOMJZ"."STATE"
( "STATEID" NUMBER(*,0) NOT NULL ENABLE,
"COUNTRYID" NUMBER(*,0) NOT NULL ENABLE,
"STATEDESC" VARCHAR2(50 BYTE),
CONSTRAINT "STATE_PK" PRIMARY KEY ("STATEID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ;

CREATE OR REPLACE TRIGGER "ECOMJZ"."STATE_TRIGGER1"
BEFORE
INSERT
ON "ECOMJZ"."STATE"
FOR EACH ROW
DECLARE


BEGIN -- executable part starts here


for c1 in (select STATEID.nextval new_id from dual)
loop
:new.STATEID :=c1.new_id;
end loop;

END;
/
ALTER TRIGGER "ECOMJZ"."STATE_TRIGGER1" ENABLE;



CREATE TABLE "ECOMJZ"."COUNTRY"
( "COUNTRYID" NUMBER NOT NULL ENABLE,
"COUNTRYDESC" VARCHAR2(50 BYTE) NOT NULL ENABLE,
CONSTRAINT "COUNTRY_PK" PRIMARY KEY ("COUNTRYID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ECOMDATA" ;

CREATE OR REPLACE TRIGGER "ECOMJZ"."COUNTRY_TRIGGER1"
BEFORE
INSERT
ON "ECOMJZ"."COUNTRY"
FOR EACH ROW
DECLARE



BEGIN -- executable part starts here

for c1 in (select COUNTRYID.nextval new_id from dual)
loop
:new.COUNTRYID :=c1.new_id;
end loop;

END;
/
ALTER TRIGGER "ECOMJZ"."COUNTRY_TRIGGER1" ENABLE;




CREATE OR REPLACE PACKAGE "ECOMJZ"."JANTZEN_TAX_PCK" AS
TYPE t_cursor IS REF CURSOR;
PROCEDURE SEL_TAX (next_cur OUT t_cursor) ;

END JANTZEN_TAX_PCK;
/
CREATE OR REPLACE PACKAGE BODY "ECOMJZ"."JANTZEN_TAX_PCK" AS

-- Returns all Tax States

PROCEDURE SEL_TAX (next_cur OUT t_cursor) IS

new_cur t_cursor;

BEGIN -- executable part starts here

OPEN new_cur FOR
SELECT TAX.TAXID, COUNTRY.COUNTRYDESC, STATE.STATEDESC, TAX.TAXRATE FROM COUNTRY INNER JOIN
STATE ON COUNTRY.COUNTRYID = STATE.COUNTRYID INNER JOIN
TAX ON STATE.STATEID = TAX.STATEID;

next_cur := new_cur;

END SEL_TAX;
END JANTZEN_TAX_PCK;
/

[Alexey]

I've reproduced the problem. Now we are investigating it.
Look forward to hearing from me again.