0

I have created a package in oracle database with type and subtype. When I am trying to see the types and subtype in "ALL_TYPES" or "ALL_PLSQL_TYPES" i am able to see only type details but not subtype details. Where can i find subtype details in oracle system tables?

 Create or replace package TEST_PKG_NEW is
       subtype V_TYP is TEST01.st_spatial_references.sr_name%Type;
       type R_TYP is record(c1 pls_integer, c2 varchar2(100));
       procedure VARR_PROC(p1 in V_TYP, p2 OUT V_TYP);
       procedure REC_PROC(p1 in R_TYP, p2 OUT R_TYP);
    end TEST_PKG_NEW;

I have already created the "st_spatial_references" table with "sr_name" as column under "TEST01" user.

udaysrinivas praneeth
  • 1
  • Which "details" are you looking for? You can see its basics in DBA_IDENTIFIERS. `select * from DBA_IDENTIFIERS where object_name = 'TEST_PKG_NEW' and name = 'V_TYP';` – Matthew McPeak Sep 13 '18 at 15:13
  • I See this table holding subtypes but my package details are not available. The above query returning empty result set. All the subtypes in that table are system defined. I am not sure if its holding user defined subtypes. – udaysrinivas praneeth Sep 13 '18 at 15:40
  • You need to compile the package after enabling PLSQL identifiers. I.e., `ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL';`, then compile your package, then check `DBA_IDENTIFIERS`. You still won't see the _structure_ of your subtype the way you can for the primary types. – Matthew McPeak Sep 13 '18 at 16:01
  • Subtypes exist only within PL/SQL code, similar to variables. They are not exposed via the data dictionary like types. – William Robertson Sep 15 '18 at 17:19
  • Is there any other table or view which is equivalent to "SYS.ALL_TYPE_ATTRS" to get metadata of subtypes created specific to package ? – udaysrinivas praneeth Sep 16 '18 at 09:05
  • Hi @MatthewMcPeak I want to get subtype metadata like if a package is created with subtypes,which also contains a subtype "rowtype" then i need to get all list of subtypes declared. If its a type i am able get them from ALL_TYPE_ATTRS but unable to do it for subtypes. – udaysrinivas praneeth Sep 17 '18 at 15:38
  • You can get a list of declared subtypes in a package using the method I gave you. I.e., `alter session set plscope_settings='IDENTIFIERS:ALL';` then select from `DBA_IDENTIFIERS`. What you will not be able to see is the structure of the subtype. – Matthew McPeak Sep 17 '18 at 16:18

0 Answers0