CODEWITHSUNDEEP
oracle-databaseplsql
Centurion
Centurion

Reputation: 14314

How to create a Oracle global type and use it in PL/SQL?

Oracle RECORD TYPE declared in procedure or function is local, therefore it might be used locally only. How to declare a RECORD TYPE that is global and might be used in all procedures and functions globally in DB?

Upvotes: 4

Views: 19171

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

Record types cannot be created as a separate schema object, so to make a Record type publicly available the type is usually declared in a package specification, or a package body to be available only in the scope of that package.

Upvotes: 5

Gaurav Soni
Gaurav Soni

Reputation: 6346

A basic example of using object type in your package .

CREATE OR REPLACE TYPE test_rec IS OBJECT
(
 ID             VARCHAR2(30)
 ,TYPE               VARCHAR2(30)
);
/

CREATE OR REPLACE TYPE test_NT AS TABLE OF test_rec;
/

declare
 v_test_NT   test_NT;

begin
select test_rec (id
                ,type
                 )
        BULK COLLECT INTO v_test_NT  
 FROM test ;

--use it as you want

end;

Upvotes: 3

Related Questions