1  CREATE OR REPLACE FUNCTION DDI.F_Metadata_Import_NEW1 (P_Type VARCHAR2,
 2                                              P_Name VARCHAR2,
 3                                              P_Owner VARCHAR2 DEFAULT 'SYS'
 4                                             , P_Target_Owner VARCHAR2 DEFAULT 'SYS')
 5     RETURN number
 6     AUTHID CURRENT_USER
 7  AS
 8     V_Type VARCHAR2(25) := UPPER(P_Type);
 9     V_Name VARCHAR2(25) := UPPER(P_Name);
10     V_Owner VARCHAR2(25) := UPPER(P_Owner);
11     V_Target_Owner VARCHAR2(25) := UPPER(P_Target_Owner);
12     V_Objid     NUMBER;
13     V_Scmid NUMBER;
14     V_Objtpid NUMBER;
15     v_number NUMBER := 0;
16     SQL_ERROR     NUMBER := SQLCODE;
17     SQL_ERRMESS   VARCHAR2 (250) := SUBSTR (SQLERRM, 1, 200);
18  BEGIN
19      DBMS_OUTPUT.PUT_LINE('Before execution ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
20     -- Get metadata from source database.
21     SELECT   Scm_Id INTO   V_Scmid FROM   DDI.Ddi_Scm_TRGT_T
22     WHERE   Scm = V_Owner AND SCM_IMP = 'Y';
23     DBMS_OUTPUT.PUT_LINE('Schema is importable');
24       BEGIN
25          DBMS_OUTPUT.PUT_LINE('check the obj type');
26          SELECT   Obj_Tp_Id INTO   V_Objtpid  FROM              DDI.Ddi_Obj_Tp_T
27          WHERE   Obj_Tp_Nm = V_Type AND SCM_IMP = 'Y';
28          DBMS_OUTPUT.PUT_LINE('Pointer in execution block');
29          return V_number;
30      EXCEPTION
31        WHEN NO_DATA_FOUND   THEN
32        DBMS_OUTPUT.PUT_LINE('Pointer comes into BEGINING OF no data found block');
33        RAISE_APPLICATION_ERROR (-20010,'Please check! ' || V_Type || ' is not an Importable Object t
34        DBMS_OUTPUT.PUT_LINE('Pointer comes to end of no data block');
35        WHEN OTHERS
36        THEN
37        DBMS_OUTPUT.PUT_LINE (SQL_ERROR || 'Message : ' || sql_errmess);
38      END;
39  EXCEPTION
40     WHEN NO_DATA_FOUND   THEN
41        RAISE_APPLICATION_ERROR (-20001,'Please check! ' || V_Owner || ' is not an Importable schema'
42     WHEN OTHERS
43     THEN
44        DBMS_OUTPUT.PUT_LINE (SQL_ERROR || 'Message : ' || sql_errmess);
45* END;