• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

code for collection in package

what is the code for finding all collections in a package
0
thota198
Asked:
thota198
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
What are you asking?  Are you wanting to loop through an entire package and look for all Pl/SQL tables and/or VArrays declared in it?
0
 
ajexpertCommented:
Your question is ambiguous.  Please let us know if you are finding trouble in existing code or you want to develop NEW code in PL/SQL
0
 
thota198Author Commented:
i was trying to finding out how many collections are there in the package so wht is the code for it
0
 
ajexpertCommented:
You have to manually count the collections in a package specs or body

Collections can be of:
VARRAYS
NESTED TABLES
PL/SQL TABLES or ASSOCIATIVE ARRAYS
0
 
slightwv (䄆 Netminder) Commented:
Assuming the package isn't wrapped, you might be able to write some code that goes through dba_source looking for terms such as 'TABLE OF' and 'VARRAY'.  I'm not sure I would trust it to be 100% accurate but it might be close enough for what you are wanting.

This can get easier in 11g with REGEXP_COUNT.

Something like:

      select 'TABLE OF', count(*) from dba_source where name='MY_PKG' and type = 'PACKAGE BODY' and owner='SCOTT' and upper(text) like '%TABLE OF%';

quick regex:
      select 'TABLE OF', count(*) from dba_source where name='MY_PKG' and type = 'PACKAGE BODY' and owner='SCOTT' and regexp_like(text,'TABLE OF','i');
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now