Passing Parameters

Hi,

I posted earlier about some permission issue. I somehow solved that,
but here is an interesting error. I've created a simple test
procedure that returns a different error. Please see below. I think
this has something to do with passing arrays. Funny thing though, it
works fine if both procedures are in the same schema. But if the
objects are in different schemas, then we get the error about
parameters........

CUSTOMER SCHEMA
----------------------------------
CREATE OR REPLACE TYPE
IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
/

CREATE OR REPLACE PROCEDURE test (
p_portfolio_id NUMBER DEFAULT NULL,
p_tickers IN_STR_ARR) AS

BEGIN
FOR v_rec IN 1..p_tickers.count LOOP
DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
END LOOP;
END;
/


NEW_CUSTOMER SCHEMA
--------------------------------------------
CREATE OR REPLACE TYPE
IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
/

CREATE OR REPLACE PROCEDURE X AS
arr IN_STR_ARR := IN_STR_ARR(0);

BEGIN
arr.EXTEND;
arr(1) := ABC';
arr.EXTEND;
arr(2) := 'XYZ';
test(12345,arr);
END;
/

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.08
SQL>
SQL>
SQL> show errors
Errors for PROCEDURE X:

LINE/COL ERROR
--------
-----------------------------------------------------------------
19/3 PL/SQL: Statement ignored
19/3 PLS-00306: wrong number or types of arguments in call to
'TEST'

On Aug 26, 10:21*am, Mtek wrote:
> Hi,
>
> I posted earlier about some permission issue. *I somehow solved that,
> but here is an interesting error. *I've created a simple test
> procedure that returns a different error. *Please see below. *I think
> this has something to do with passing arrays. *Funny thing though, it
> works fine if both procedures are in the same schema. *But if the
> objects are in different schemas, then we get the error about
> parameters........
>
> CUSTOMER SCHEMA
> ----------------------------------
> CREATE OR REPLACE TYPE
> IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> /
>
> CREATE OR REPLACE PROCEDURE test (
> * p_portfolio_id *NUMBER DEFAULT NULL,
> * p_tickers * * * IN_STR_ARR) AS
>
> BEGIN
> * FOR v_rec IN 1..p_tickers.count LOOP
> * * DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> * END LOOP;
> END;
> /
>
> NEW_CUSTOMER SCHEMA
> --------------------------------------------
> CREATE OR REPLACE TYPE
> IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> /
>
> CREATE OR REPLACE PROCEDURE X AS
> * arr * * * * * * *IN_STR_ARR := IN_STR_ARR(0);
>
> BEGIN
> * * arr.EXTEND;
> * * arr(1) := ABC';
> * * arr.EXTEND;
> * * arr(2) := 'XYZ';
> * test(12345,arr);
> END;
> /
>
> Warning: Procedure created with compilation errors.
>
> Elapsed: 00:00:00.08
> SQL>
> SQL>
> SQL> show errors
> Errors for PROCEDURE X:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 19/3 * * PL/SQL: Statement ignored
> 19/3 * * PLS-00306: wrong number or types of arguments in call to
> 'TEST'

I would be describing TEST in the NEW_CUSTOMER schema. I see no
grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
created to allow access without using the schema owner. So I expect
you have a TEST procedure under NEW_CUSTOMER which isn't the same as
the TEST procedure you just created under CUSTOMER.


David Fitzjarrell

On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
> On Aug 26, 10:21 am, Mtek wrote:
>
>
>
> > Hi,
>
> > I posted earlier about some permission issue. I somehow solved that,
> > but here is an interesting error. I've created a simple test
> > procedure that returns a different error. Please see below. I think
> > this has something to do with passing arrays. Funny thing though, it
> > works fine if both procedures are in the same schema. But if the
> > objects are in different schemas, then we get the error about
> > parameters........
>
> > CUSTOMER SCHEMA
> > ----------------------------------
> > CREATE OR REPLACE TYPE
> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> > /
>
> > CREATE OR REPLACE PROCEDURE test (
> > p_portfolio_id NUMBER DEFAULT NULL,
> > p_tickers IN_STR_ARR) AS
>
> > BEGIN
> > FOR v_rec IN 1..p_tickers.count LOOP
> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> > END LOOP;
> > END;
> > /
>
> > NEW_CUSTOMER SCHEMA
> > --------------------------------------------
> > CREATE OR REPLACE TYPE
> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> > /
>
> > CREATE OR REPLACE PROCEDURE X AS
> > arr IN_STR_ARR := IN_STR_ARR(0);
>
> > BEGIN
> > arr.EXTEND;
> > arr(1) := ABC';
> > arr.EXTEND;
> > arr(2) := 'XYZ';
> > test(12345,arr);
> > END;
> > /
>
> > Warning: Procedure created with compilation errors.
>
> > Elapsed: 00:00:00.08
> > SQL>
> > SQL>
> > SQL> show errors
> > Errors for PROCEDURE X:
>
> > LINE/COL ERROR
> > --------
> > -----------------------------------------------------------------
> > 19/3 PL/SQL: Statement ignored
> > 19/3 PLS-00306: wrong number or types of arguments in call to
> > 'TEST'
>
> I would be describing TEST in the NEW_CUSTOMER schema. I see no
> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
> created to allow access without using the schema owner. So I expect
> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
> the TEST procedure you just created under CUSTOMER.
>
> David Fitzjarrell

Hi David,

I think it has something to do with the fact that I'm trying to pass a
VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
both schemas. And, if I run both procedures in the same schema, it
works fine.

Since it says wrong number of parameters, then it must be finding the
other procedure, or it would say it cannot find it. So, I'm thinking
it is the array passing. Maybe my format is not correct or
something........

Thanks,

"Mtek" schreef in bericht
news:4d6e8daf-ac70-46c8-bca8-96b74711556d-at-i20g2000prf.googlegroups.com...
> On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
>> On Aug 26, 10:21 am, Mtek wrote:
>>
>>
>>
>> > Hi,
>>
>> > I posted earlier about some permission issue. I somehow solved that,
>> > but here is an interesting error. I've created a simple test
>> > procedure that returns a different error. Please see below. I think
>> > this has something to do with passing arrays. Funny thing though, it
>> > works fine if both procedures are in the same schema. But if the
>> > objects are in different schemas, then we get the error about
>> > parameters........
>>
>> > CUSTOMER SCHEMA
>> > ----------------------------------
>> > CREATE OR REPLACE TYPE
>> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> > /
>>
>> > CREATE OR REPLACE PROCEDURE test (
>> > p_portfolio_id NUMBER DEFAULT NULL,
>> > p_tickers IN_STR_ARR) AS
>>
>> > BEGIN
>> > FOR v_rec IN 1..p_tickers.count LOOP
>> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
>> > END LOOP;
>> > END;
>> > /
>>
>> > NEW_CUSTOMER SCHEMA
>> > --------------------------------------------
>> > CREATE OR REPLACE TYPE
>> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> > /
>>
>> > CREATE OR REPLACE PROCEDURE X AS
>> > arr IN_STR_ARR := IN_STR_ARR(0);
>>
>> > BEGIN
>> > arr.EXTEND;
>> > arr(1) := ABC';
>> > arr.EXTEND;
>> > arr(2) := 'XYZ';
>> > test(12345,arr);
>> > END;
>> > /
>>
>> > Warning: Procedure created with compilation errors.
>>
>> > Elapsed: 00:00:00.08
>> > SQL>
>> > SQL>
>> > SQL> show errors
>> > Errors for PROCEDURE X:
>>
>> > LINE/COL ERROR
>> > --------
>> > -----------------------------------------------------------------
>> > 19/3 PL/SQL: Statement ignored
>> > 19/3 PLS-00306: wrong number or types of arguments in call to
>> > 'TEST'
>>
>> I would be describing TEST in the NEW_CUSTOMER schema. I see no
>> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
>> created to allow access without using the schema owner. So I expect
>> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
>> the TEST procedure you just created under CUSTOMER.
>>
>> David Fitzjarrell
>
> Hi David,
>
> I think it has something to do with the fact that I'm trying to pass a
> VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
> both schemas. And, if I run both procedures in the same schema, it
> works fine.
>
> Since it says wrong number of parameters, then it must be finding the
> other procedure, or it would say it cannot find it. So, I'm thinking
> it is the array passing. Maybe my format is not correct or
> something........
>
> Thanks,


Defining a type in two schema's does not make them the same type. Define the
type in a package and refere to this type as packagename.

Maybe referring to schema.type will work as well, no time to test this.

Shakespeare

On Aug 26, 12:00 pm, "Shakespeare" wrote:
> "Mtek" schreef in berichtnews:4d6e8daf-ac70-46c8-bca8-96b74711556d-at-i20g2000prf.googlegroups.com...
>
>
>
> > On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
> >> On Aug 26, 10:21 am, Mtek wrote:
>
> >> > Hi,
>
> >> > I posted earlier about some permission issue. I somehow solved that,
> >> > but here is an interesting error. I've created a simple test
> >> > procedure that returns a different error. Please see below. I think
> >> > this has something to do with passing arrays. Funny thing though, it
> >> > works fine if both procedures are in the same schema. But if the
> >> > objects are in different schemas, then we get the error about
> >> > parameters........
>
> >> > CUSTOMER SCHEMA
> >> > ----------------------------------
> >> > CREATE OR REPLACE TYPE
> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> >> > /
>
> >> > CREATE OR REPLACE PROCEDURE test (
> >> > p_portfolio_id NUMBER DEFAULT NULL,
> >> > p_tickers IN_STR_ARR) AS
>
> >> > BEGIN
> >> > FOR v_rec IN 1..p_tickers.count LOOP
> >> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> >> > END LOOP;
> >> > END;
> >> > /
>
> >> > NEW_CUSTOMER SCHEMA
> >> > --------------------------------------------
> >> > CREATE OR REPLACE TYPE
> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> >> > /
>
> >> > CREATE OR REPLACE PROCEDURE X AS
> >> > arr IN_STR_ARR := IN_STR_ARR(0);
>
> >> > BEGIN
> >> > arr.EXTEND;
> >> > arr(1) := ABC';
> >> > arr.EXTEND;
> >> > arr(2) := 'XYZ';
> >> > test(12345,arr);
> >> > END;
> >> > /
>
> >> > Warning: Procedure created with compilation errors.
>
> >> > Elapsed: 00:00:00.08
> >> > SQL>
> >> > SQL>
> >> > SQL> show errors
> >> > Errors for PROCEDURE X:
>
> >> > LINE/COL ERROR
> >> > --------
> >> > -----------------------------------------------------------------
> >> > 19/3 PL/SQL: Statement ignored
> >> > 19/3 PLS-00306: wrong number or types of arguments in call to
> >> > 'TEST'
>
> >> I would be describing TEST in the NEW_CUSTOMER schema. I see no
> >> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
> >> created to allow access without using the schema owner. So I expect
> >> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
> >> the TEST procedure you just created under CUSTOMER.
>
> >> David Fitzjarrell
>
> > Hi David,
>
> > I think it has something to do with the fact that I'm trying to pass a
> > VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
> > both schemas. And, if I run both procedures in the same schema, it
> > works fine.
>
> > Since it says wrong number of parameters, then it must be finding the
> > other procedure, or it would say it cannot find it. So, I'm thinking
> > it is the array passing. Maybe my format is not correct or
> > something........
>
> > Thanks,
>
> Defining a type in two schema's does not make them the same type. Define the
> type in a package and refere to this type as packagename.
>
> Maybe referring to schema.type will work as well, no time to test this.
>
> Shakespeare


WTF?? So, I remove the IN_STR_ARR types in the other schemas and
leave it in 1 schema. Then grant execute on that type to public and
create a public synonym and everything works?????

How is that different than having the type in each schema and granting
access to the user needed access????

"Mtek" schreef in bericht
news:e9785572-b39c-4873-a687-c54c8fc2c075-at-z6g2000pre.googlegroups.com...
> On Aug 26, 12:00 pm, "Shakespeare" wrote:
>> "Mtek" schreef in
>> berichtnews:4d6e8daf-ac70-46c8-bca8-96b74711556d-at-i20g2000prf.googlegroups.com...
>>
>>
>>
>> > On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
>> >> On Aug 26, 10:21 am, Mtek wrote:
>>
>> >> > Hi,
>>
>> >> > I posted earlier about some permission issue. I somehow solved
>> >> > that,
>> >> > but here is an interesting error. I've created a simple test
>> >> > procedure that returns a different error. Please see below. I
>> >> > think
>> >> > this has something to do with passing arrays. Funny thing though,
>> >> > it
>> >> > works fine if both procedures are in the same schema. But if the
>> >> > objects are in different schemas, then we get the error about
>> >> > parameters........
>>
>> >> > CUSTOMER SCHEMA
>> >> > ----------------------------------
>> >> > CREATE OR REPLACE TYPE
>> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> >> > /
>>
>> >> > CREATE OR REPLACE PROCEDURE test (
>> >> > p_portfolio_id NUMBER DEFAULT NULL,
>> >> > p_tickers IN_STR_ARR) AS
>>
>> >> > BEGIN
>> >> > FOR v_rec IN 1..p_tickers.count LOOP
>> >> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
>> >> > END LOOP;
>> >> > END;
>> >> > /
>>
>> >> > NEW_CUSTOMER SCHEMA
>> >> > --------------------------------------------
>> >> > CREATE OR REPLACE TYPE
>> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> >> > /
>>
>> >> > CREATE OR REPLACE PROCEDURE X AS
>> >> > arr IN_STR_ARR := IN_STR_ARR(0);
>>
>> >> > BEGIN
>> >> > arr.EXTEND;
>> >> > arr(1) := ABC';
>> >> > arr.EXTEND;
>> >> > arr(2) := 'XYZ';
>> >> > test(12345,arr);
>> >> > END;
>> >> > /
>>
>> >> > Warning: Procedure created with compilation errors.
>>
>> >> > Elapsed: 00:00:00.08
>> >> > SQL>
>> >> > SQL>
>> >> > SQL> show errors
>> >> > Errors for PROCEDURE X:
>>
>> >> > LINE/COL ERROR
>> >> > --------
>> >> > -----------------------------------------------------------------
>> >> > 19/3 PL/SQL: Statement ignored
>> >> > 19/3 PLS-00306: wrong number or types of arguments in call to
>> >> > 'TEST'
>>
>> >> I would be describing TEST in the NEW_CUSTOMER schema. I see no
>> >> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
>> >> created to allow access without using the schema owner. So I expect
>> >> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
>> >> the TEST procedure you just created under CUSTOMER.
>>
>> >> David Fitzjarrell
>>
>> > Hi David,
>>
>> > I think it has something to do with the fact that I'm trying to pass a
>> > VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
>> > both schemas. And, if I run both procedures in the same schema, it
>> > works fine.
>>
>> > Since it says wrong number of parameters, then it must be finding the
>> > other procedure, or it would say it cannot find it. So, I'm thinking
>> > it is the array passing. Maybe my format is not correct or
>> > something........
>>
>> > Thanks,
>>
>> Defining a type in two schema's does not make them the same type. Define
>> the
>> type in a package and refere to this type as packagename.
>>
>> Maybe referring to schema.type will work as well, no time to test this.
>>
>> Shakespeare
>
>
> WTF?? So, I remove the IN_STR_ARR types in the other schemas and
> leave it in 1 schema. Then grant execute on that type to public and
> create a public synonym and everything works?????
>
> How is that different than having the type in each schema and granting
> access to the user needed access????

Hush hush...

This is not uncommon, there are other programming languages that behave the
same way (Pascal, Delphi, Fortran, Algol61, -- man I'm getting old...).
Imagine an other user defining a new type with the same name... what would
happen then?
Or two schemas define the same datatype and one changes its definition....
It is all a matter of scope. You leave the type DEFINITION in one schema
(e.g. in a package definition, and grant the package to other users), and
define variables of this type (schema.packagename.typename) in your own
schema. What is the problem?

Shakespeare
(What's in a type?)

On Aug 26, 2:33 pm, "Shakespeare" wrote:
> "Mtek" schreef in berichtnews:e9785572-b39c-4873-a687-c54c8fc2c075-at-z6g2000pre.googlegroups.com...
>
>
>
> > On Aug 26, 12:00 pm, "Shakespeare" wrote:
> >> "Mtek" schreef in
> >> berichtnews:4d6e8daf-ac70-46c8-bca8-96b74711556d-at-i20g2000prf.googlegroups.com...
>
> >> > On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
> >> >> On Aug 26, 10:21 am, Mtek wrote:
>
> >> >> > Hi,
>
> >> >> > I posted earlier about some permission issue. I somehow solved
> >> >> > that,
> >> >> > but here is an interesting error. I've created a simple test
> >> >> > procedure that returns a different error. Please see below. I
> >> >> > think
> >> >> > this has something to do with passing arrays. Funny thing though,
> >> >> > it
> >> >> > works fine if both procedures are in the same schema. But if the
> >> >> > objects are in different schemas, then we get the error about
> >> >> > parameters........
>
> >> >> > CUSTOMER SCHEMA
> >> >> > ----------------------------------
> >> >> > CREATE OR REPLACE TYPE
> >> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> >> >> > /
>
> >> >> > CREATE OR REPLACE PROCEDURE test (
> >> >> > p_portfolio_id NUMBER DEFAULT NULL,
> >> >> > p_tickers IN_STR_ARR) AS
>
> >> >> > BEGIN
> >> >> > FOR v_rec IN 1..p_tickers.count LOOP
> >> >> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> >> >> > END LOOP;
> >> >> > END;
> >> >> > /
>
> >> >> > NEW_CUSTOMER SCHEMA
> >> >> > --------------------------------------------
> >> >> > CREATE OR REPLACE TYPE
> >> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> >> >> > /
>
> >> >> > CREATE OR REPLACE PROCEDURE X AS
> >> >> > arr IN_STR_ARR := IN_STR_ARR(0);
>
> >> >> > BEGIN
> >> >> > arr.EXTEND;
> >> >> > arr(1) := ABC';
> >> >> > arr.EXTEND;
> >> >> > arr(2) := 'XYZ';
> >> >> > test(12345,arr);
> >> >> > END;
> >> >> > /
>
> >> >> > Warning: Procedure created with compilation errors.
>
> >> >> > Elapsed: 00:00:00.08
> >> >> > SQL>
> >> >> > SQL>
> >> >> > SQL> show errors
> >> >> > Errors for PROCEDURE X:
>
> >> >> > LINE/COL ERROR
> >> >> > --------
> >> >> > -----------------------------------------------------------------
> >> >> > 19/3 PL/SQL: Statement ignored
> >> >> > 19/3 PLS-00306: wrong number or types of arguments in call to
> >> >> > 'TEST'
>
> >> >> I would be describing TEST in the NEW_CUSTOMER schema. I see no
> >> >> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
> >> >> created to allow access without using the schema owner. So I expect
> >> >> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
> >> >> the TEST procedure you just created under CUSTOMER.
>
> >> >> David Fitzjarrell
>
> >> > Hi David,
>
> >> > I think it has something to do with the fact that I'm trying to pass a
> >> > VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
> >> > both schemas. And, if I run both procedures in the same schema, it
> >> > works fine.
>
> >> > Since it says wrong number of parameters, then it must be finding the
> >> > other procedure, or it would say it cannot find it. So, I'm thinking
> >> > it is the array passing. Maybe my format is not correct or
> >> > something........
>
> >> > Thanks,
>
> >> Defining a type in two schema's does not make them the same type. Define
> >> the
> >> type in a package and refere to this type as packagename.
>
> >> Maybe referring to schema.type will work as well, no time to test this.
>
> >> Shakespeare
>
> > WTF?? So, I remove the IN_STR_ARR types in the other schemas and
> > leave it in 1 schema. Then grant execute on that type to public and
> > create a public synonym and everything works?????
>
> > How is that different than having the type in each schema and granting
> > access to the user needed access????
>
> Hush hush...
>
> This is not uncommon, there are other programming languages that behave the
> same way (Pascal, Delphi, Fortran, Algol61, -- man I'm getting old...).
> Imagine an other user defining a new type with the same name... what would
> happen then?
> Or two schemas define the same datatype and one changes its definition....
> It is all a matter of scope. You leave the type DEFINITION in one schema
> (e.g. in a package definition, and grant the package to other users), and
> define variables of this type (schema.packagename.typename) in your own
> schema. What is the problem?
>
> Shakespeare
> (What's in a type?)

It just seems totally f**ked up. I mean, I should be able to create
a type in every schema and have the database be smart and use the
local type when needed, and when arrays are passed, they use the type
in the receiving schema.......seems pretty straight forward to
me.......

"Mtek" wrote in message
news:13cfb17c-fc05-49e3-9281-7919f9073f49-at-m73g2000hsh.googlegroups.com...
> On Aug 26, 2:33 pm, "Shakespeare" wrote:
>> "Mtek" schreef in
>> berichtnews:e9785572-b39c-4873-a687-c54c8fc2c075-at-z6g2000pre.googlegroups.com...
>>
>>
>>
>> > On Aug 26, 12:00 pm, "Shakespeare" wrote:
>> >> "Mtek" schreef in
>> >> berichtnews:4d6e8daf-ac70-46c8-bca8-96b74711556d-at-i20g2000prf.googlegroups.com...
>>
>> >> > On Aug 26, 11:06 am, "fitzjarr...@cox.net" wrote:
>> >> >> On Aug 26, 10:21 am, Mtek wrote:
>>
>> >> >> > Hi,
>>
>> >> >> > I posted earlier about some permission issue. I somehow solved
>> >> >> > that,
>> >> >> > but here is an interesting error. I've created a simple test
>> >> >> > procedure that returns a different error. Please see below. I
>> >> >> > think
>> >> >> > this has something to do with passing arrays. Funny thing
>> >> >> > though,
>> >> >> > it
>> >> >> > works fine if both procedures are in the same schema. But if the
>> >> >> > objects are in different schemas, then we get the error about
>> >> >> > parameters........
>>
>> >> >> > CUSTOMER SCHEMA
>> >> >> > ----------------------------------
>> >> >> > CREATE OR REPLACE TYPE
>> >> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> >> >> > /
>>
>> >> >> > CREATE OR REPLACE PROCEDURE test (
>> >> >> > p_portfolio_id NUMBER DEFAULT NULL,
>> >> >> > p_tickers IN_STR_ARR) AS
>>
>> >> >> > BEGIN
>> >> >> > FOR v_rec IN 1..p_tickers.count LOOP
>> >> >> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
>> >> >> > END LOOP;
>> >> >> > END;
>> >> >> > /
>>
>> >> >> > NEW_CUSTOMER SCHEMA
>> >> >> > --------------------------------------------
>> >> >> > CREATE OR REPLACE TYPE
>> >> >> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
>> >> >> > /
>>
>> >> >> > CREATE OR REPLACE PROCEDURE X AS
>> >> >> > arr IN_STR_ARR := IN_STR_ARR(0);
>>
>> >> >> > BEGIN
>> >> >> > arr.EXTEND;
>> >> >> > arr(1) := ABC';
>> >> >> > arr.EXTEND;
>> >> >> > arr(2) := 'XYZ';
>> >> >> > test(12345,arr);
>> >> >> > END;
>> >> >> > /
>>
>> >> >> > Warning: Procedure created with compilation errors.
>>
>> >> >> > Elapsed: 00:00:00.08
>> >> >> > SQL>
>> >> >> > SQL>
>> >> >> > SQL> show errors
>> >> >> > Errors for PROCEDURE X:
>>
>> >> >> > LINE/COL ERROR
>> >> >> > --------
>> >> >> > -----------------------------------------------------------------
>> >> >> > 19/3 PL/SQL: Statement ignored
>> >> >> > 19/3 PLS-00306: wrong number or types of arguments in call to
>> >> >> > 'TEST'
>>
>> >> >> I would be describing TEST in the NEW_CUSTOMER schema. I see no
>> >> >> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
>> >> >> created to allow access without using the schema owner. So I
>> >> >> expect
>> >> >> you have a TEST procedure under NEW_CUSTOMER which isn't the same
>> >> >> as
>> >> >> the TEST procedure you just created under CUSTOMER.
>>
>> >> >> David Fitzjarrell
>>
>> >> > Hi David,
>>
>> >> > I think it has something to do with the fact that I'm trying to pass
>> >> > a
>> >> > VARRAY.......it looks correct, as the IN_STR_ARR type is defined in
>> >> > both schemas. And, if I run both procedures in the same schema, it
>> >> > works fine.
>>
>> >> > Since it says wrong number of parameters, then it must be finding
>> >> > the
>> >> > other procedure, or it would say it cannot find it. So, I'm
>> >> > thinking
>> >> > it is the array passing. Maybe my format is not correct or
>> >> > something........
>>
>> >> > Thanks,
>>
>> >> Defining a type in two schema's does not make them the same type.
>> >> Define
>> >> the
>> >> type in a package and refere to this type as packagename.
>>
>> >> Maybe referring to schema.type will work as well, no time to test
>> >> this.
>>
>> >> Shakespeare
>>
>> > WTF?? So, I remove the IN_STR_ARR types in the other schemas and
>> > leave it in 1 schema. Then grant execute on that type to public and
>> > create a public synonym and everything works?????
>>
>> > How is that different than having the type in each schema and granting
>> > access to the user needed access????
>>
>> Hush hush...
>>
>> This is not uncommon, there are other programming languages that behave
>> the
>> same way (Pascal, Delphi, Fortran, Algol61, -- man I'm getting old...).
>> Imagine an other user defining a new type with the same name... what
>> would
>> happen then?
>> Or two schemas define the same datatype and one changes its
>> definition....
>> It is all a matter of scope. You leave the type DEFINITION in one schema
>> (e.g. in a package definition, and grant the package to other users), and
>> define variables of this type (schema.packagename.typename) in your own
>> schema. What is the problem?
>>
>> Shakespeare
>> (What's in a type?)
>
> It just seems totally f**ked up. I mean, I should be able to create
> a type in every schema and have the database be smart and use the
> local type when needed, and when arrays are passed, they use the type
> in the receiving schema.......seems pretty straight forward to
> me.......
>
You didn't listen to Shakespear's explanation. You may wish for something
to work a particular way, but it doesn't work that way. Again Oracle is
following name scoping conventions that a lot of other languages also
follow. Just because you don't like it doesn't mean Oracle is wrong. That
is how it works. You can create a type in schema A and grant execute on a
to schema b and then create a synonym in b for a. Then yo don't have to
specify the schema name.
Jim

On Tue, 26 Aug 2008 13:14:29 -0700 (PDT), Mtek
wrote:

>It just seems totally f**ked up. I mean, I should be able to create
>a type in every schema and have the database be smart and use the
>local type when needed, and when arrays are passed, they use the type
>in the receiving schema.......seems pretty straight forward to
>me.......

What you 'think' you should be able to do is not important. What is
implemented and *described* in the documentation, you refuse to read
,is.
Now when you have two identically named types, one 'local' as you call
it, and one 'global' you are definitely going to create havoc.
Due to precedence rules the type name is likely resolved only once
within he procedure.

So it is not f**ked up. You just scr**wed up the whole thing by taking
the shortcut to the keyboard, without reading and understanding.

--


Sybrand Bakker
Senior Oracle DBA

Mtek writes:

> Hi,
>
> I posted earlier about some permission issue. I somehow solved that,
> but here is an interesting error. I've created a simple test
> procedure that returns a different error. Please see below. I think
> this has something to do with passing arrays. Funny thing though, it
> works fine if both procedures are in the same schema. But if the
> objects are in different schemas, then we get the error about
> parameters........
>
> CUSTOMER SCHEMA
> ----------------------------------
> CREATE OR REPLACE TYPE
> IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> /
>
> CREATE OR REPLACE PROCEDURE test (
> p_portfolio_id NUMBER DEFAULT NULL,
> p_tickers IN_STR_ARR) AS
>
> BEGIN
> FOR v_rec IN 1..p_tickers.count LOOP
> DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> END LOOP;
> END;
> /
>
>
> NEW_CUSTOMER SCHEMA
> --------------------------------------------
> CREATE OR REPLACE TYPE
> IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> /
>
> CREATE OR REPLACE PROCEDURE X AS
> arr IN_STR_ARR := IN_STR_ARR(0);
>
> BEGIN
> arr.EXTEND;
> arr(1) := ABC';
> arr.EXTEND;
> arr(2) := 'XYZ';
> test(12345,arr);
> END;
> /
>
> Warning: Procedure created with compilation errors.
>
> Elapsed: 00:00:00.08
> SQL>
> SQL>
> SQL> show errors
> Errors for PROCEDURE X:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 19/3 PL/SQL: Statement ignored
> 19/3 PLS-00306: wrong number or types of arguments in call to
> 'TEST'

Check the Oracle documentation. You will find there is a warning in the
pl/sql reference manual dealing with parameter passing and collection
types.

Essentially, from PL/SQLs view, your two varray types are _different_
types despite having the same name and being made up of the same data
types.

Tim


--
tcross (at) rapttech dot com dot au