• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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