Oracle Beast

June 07, 2007

What are all those SYS_##### types in my schema?

If I create a package called MYSCHEMA.MYPACKAGE which has a type defined in the package spec...

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPACKAGE IS

  -- I define this type in the spec so that users of my package can create variables of this type
  -- and pass them in/out of my package.
  TYPE T_MYTYPE IS RECORD
  (
      field1 number,
      field2 varchar2(2000)
  );

  -- I need a table of my records if I want to return a whole big bunch of them out...
  TYPE T_MYTYPE_TAB IS TABLE OF T_MYTYPE;

  -- this procedure takes a T_MYTYPE record and stores the information in our super complex datamodel
  -- spread out over 100 databases across the globe
  PROCEDURE CREATE_RECORD(p_rec T_MYTYPE);

  -- this function gets a single one of my records after searching over a 100 databases across the globe to find it.
  FUNCTION GET_RECORD(p_record_id NUMBER) RETURN T_MYTYPE;

  -- this function queries the T_MYTYPE information from the super complex
  -- datamodel in each of the 100 databases across the globe and returns all of them in one nice result set.
  -- furthermore, due to pipelining and parallelism, this procedure fetches from
  -- all 100 databases at the same time so kills the network making the networks people's
  -- faces turn different shades of red and purple...
  -- to query from it "SELECT * FROM TABLE(MYSCHEMA.MYPACKAGE.GET_ALL_RECORDS);"
  FUNCTION GET_ALL_RECORDS RETURN T_MYTYPE_TAB PIPELINED;

END MYPACKAGE;

Then, Oracle automatically creates 2 types named MYSCHEMA.SYS_##### (where the ##### are seemingly to me random numbers) where one is akin to a synonym for MYSCHEMA.MYPACKAGE.T_MYTYPE and the other is the same for MYSCHEMA.MYPACKAGE.T_MYTYPE_TAB. This is so that the SQL engine "knows" about types defined in MYSCHEMA.MYPACKAGE; the package spec is known to the PL/SQL engine. Or at least that is my understanding of it….

Posted by rar675 at 12:09 PM | Comments (0)