Passing Parameters
Mtek
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'
ora...@msn.com
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
Mtek
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,
Shakespeare
"Mtek" <mt...@mtekusa.com> schreef in bericht
news:4d6e8daf-ac70-46c8...@i20g2000prf.googlegroups.com...
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.<typename>
Maybe referring to schema.type will work as well, no time to test this.
Shakespeare
Mtek
> "Mtek" <m...@mtekusa.com> schreef in berichtnews:4d6e8daf-ac70-46c8...@i20g2000prf.googlegroups.com...
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????
Shakespeare
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?)
Mtek
> "Mtek" <m...@mtekusa.com> schreef in berichtnews:e9785572-b39c-4873...@z6g2000pre.googlegroups.com...
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.......
gym dot scuba dot kennedy at gmail
"Mtek" <mt...@mtekusa.com> wrote in message
news:13cfb17c-fc05-49e3...@m73g2000hsh.googlegroups.com...
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
sybr...@hccnet.nl
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
Tim X
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
Tim X
Even worse, consider the maintenance nightmare you would be creating!
Doing what the OP suggests now means that every developer has to know
about *every* definition in every schema and when a change is made,
ensure all of them are updated. If any are missed, what sort of error
message would be given (assuming it can detect such errors).
Whats the bet that he isn't using %TYPE, %ROWTYPE either?
Mtek
> sybra...@hccnet.nl writes:
> > On Tue, 26 Aug 2008 13:14:29 -0700 (PDT), Mtek <m...@mtekusa.com>
Well Tim, I did not write the application. But, going on what
everyone has said here, I've just created the type on one of the
schemas and then created a synonym and granted access to that
type......all seems to be well now.
I can understand what Oracle is saying and all, but the concept seems
simple. Which goes back to the saying, you can't have your cake and
eat it too.....
Thanks all for your input..
ora...@msn.com
>
> - Show quoted text -
You most certainly can have your cake and eat it, too. I do, all of
the time. What you cannot do is eat your cake and have it, too.
David Fitzjarrell
William Robertson
Aren't those the same thing?
joel garry
wrote:
Only if, like my wife's cats, you eat barf.
The fundamental problem as referenced is time ordering, though the
actual intent of the saying is that you cannot have something that
you've already used. Which is obviously false given the existence of
enzymes. Which are in barf, anyways. Not to mention computer
programs can be copied without being used. So if you name a program
cake.sql:
select "eat it!" from dual;
You can indeed, have your cake and eat it too.
jg
--
@home.com is bogus.
http://icanhascheezburger.com/tag/lasers/