Search code examples
oracle-databaseplsqlsubtype

Create or replace global subtype


I know that I can create a subtype inside a package specification like:

CREATE OR REPLACE PACKAGE XY
AS
  SUBTYPE type_sdebug IS VARCHAR (200);
  ...
END;
/

If I want to use the same subtype within another package then I need to redefine the same type again. Is there a way to create or replace a global subtype such as:

CREATE OR REPLACE TYPE STRING_ARRAY AS VARRAY(500) OF VARCHAR2(30);
/

Solution

  • As far as I know, SUBTYPEs are a PL/SQL feature, so you cannot create them globally. But nothing prevents you from using a type defined in your package XY in another package (e.g. AB):

    CREATE OR REPLACE PACKAGE XY
    AS
      SUBTYPE type_sdebug IS VARCHAR (200);
    END;
    
    CREATE OR REPLACE PACKAGE AB
    AS
      PROCEDURE print_it(p_Debug in XY.type_sdebug);
    END;
    
    CREATE OR REPLACE PACKAGE BODY AB
    AS
      PROCEDURE print_it(p_Debug in XY.type_sdebug) is
      begin
          dbms_output.put_line(p_Debug);
      end;
    END;
    
    
    declare
      v_Debug XY.type_sdebug default 'hello world';
    begin
      ab.print_it(v_Debug);
    end;