CODEWITHSUNDEEP
oracle-databasefunctionstored-proceduresplsqloracle11g
Prashant Bhanarkar
Prashant Bhanarkar

Reputation: 960

oracle declare a user defined type that can be used across multiple functions and procedure

I have an SQL file that creates other stored procedures/functions. I wanted to make use of a user defined type that can be accessed among the created stored procedures/functions. What is the best way to achieve this? The simplest way that I thought of is to just create that user defined type in every procedure/function.

My user defined type looks something like this:

create  type array_t is varray(2) of number;

Upvotes: 0

Views: 214

Answers (2)

William Robertson
William Robertson

Reputation: 15991

You can either create the type as a stand-alone item, with a create [or replace] type statement, or else include it in a package header. The advantage of a standalone type is that you can use it in SQL queries and as the type for table and view columns. The advantage of a PL/SQL-only declaration is that it simplifies your deployment, and nobody in their right mind would want a varray column anyway.

If your deployment process is giving 'name is already used by an existing object' errors then yes it already exists (perhaps from a previous run). create or replace is standard practice and a good idea, although your scripts should define each item in only one place.

Upvotes: 1

Kacper
Kacper

Reputation: 4818

Just execute

create  type array_t is varray(2) of number;

globally.

Upvotes: 2

Related Questions