Re: Problem with Stored Proc and VARRAY

From: Chris Leonard <s_p_a_m_chris_at_hotmail.com>
Date: Mon, 7 Oct 2002 11:49:25 -0500
Message-ID: <qWio9.89$5g7.67766@news.uswest.net>

Marek - Das hat mir kein Sinn!

OK, I don't really know much German, but let me see if I can help you out. I don't know why you are having problems, but I have attached the following session output showing me doing what I think you are trying to do. Your "user1" is caleonar in my script, and your "user2" is caleonar2. Also, caleonar is a DBA account, but caleonar2 just has connect and resource granted to it. I hope that this output might help you see if you've missed anything. I have also included v$version so you can see that we are running 8.1.3. Of course, I have changed the password and instance name in the output. I hope this helps you out.

Good luck!
Chris

  SQL> connect caleonar/password_at_instance.world;   Connected.
  SQL> select * from v$version;

  BANNER

---

  Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production   PL/SQL Release 8.1.7.3.0 - Production
  CORE 8.1.7.0.0 Production
  TNS for IBM/AIX RISC System/6000: Version 8.1.7.3.0 - Production   NLSRTL Version 3.4.1.0.0 - Production

  SQL> CREATE or replace TYPE number_varray AS VARRAY(10) OF NUMBER(15);     2 /

  Type created.

  SQL> grant execute on number_varray to caleonar2;

  Grant succeeded.

  SQL> create or replace procedure p1
    2 ( v1 caleonar.number_varray )
    3 as
    4 begin
    5 dbms_output.put_line (v1.count);     6 end;
    7 /

  Procedure created.

  SQL> grant execute on p1 to caleonar2;

  Grant succeeded.

  SQL> connect caleonar2/password_at_instance.world;   Connected.
  SQL> create or replace procedure p1
    2 as
    3 v1 caleonar.number_varray := caleonar.number_varray(NULL);     4 begin
    5 v1(1) := 5;
    6 v1.extend;
    7 v1(2) := 7;
    8 caleonar.p1(v1);
    9 end;
   10 /

  Procedure created.

  SQL> set serveroutput on
  SQL> exec p1;
  2

  PL/SQL procedure successfully completed.

  SQL>

-- 
Cheers,
Chris

___________________________________

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, MCT, OCP, CIW
___________________________________

"Marek Lange" <marek.lange_at_web.de> wrote in message news:3DA1469A.4090301_at_web.de...


> Hi,

> 

> we have a problem mit Oracle 8i. We want to call a stored procedure 

> (that uses VARRAYs as data types) with a user called "USER2". The stored 

> procedure belongs to the "USER1".

> 

> We have created the type under "USER1" (CREATE TYPE number_varray AS 

> VARRAY(10) OF NUMBER(15)) and the type has the grant "execute" for the 

> "USER2" (who uses the procedure). We always get the error "invalid name 

> pattern: <USER1>.number_varray".

> 

> What went wrong here?

> 

> Thanks,

> 

> Marek

> 

Received on Mon Oct 07 2002 - 11:49:25 CDT