14 thoughts on “ApexLib Schema Export Tool”

1. Hi Peter,

   really good idea.

   Whenever I tried creating installable applications I always needed a couple of hours/days getting them running.

   Best regards,

   Tobias

2. Tried this on a hosted instance of APEX 4.x and it crashed looking for a table…. Any suggestions?

3. Well,
   which table is it looking for?
   What is the error message?
   Did you install the supported Objects?

4. ORA-06592: CASE not found while executing CASE statement
   Error installing plug-in.

   when i try to import this plugin i get the above error..

5. The ApexLib Schema Export Tool isn’t a Plugin.

   You’ll have to install it as Application.

6. Error is below.. Does NOT give table name…

   ORA-06550: line 4, column 994: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 52: PLS-00364: loop index variable ‘RDATA’ use is invalid ORA-06550: line 7, column 16: PL/SQL: Statement ignored

   Error Error exporting the Application: ORA-06550: line 4, column 994: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 52: PLS-00364: loop index variable ‘RDATA’ use is invalid ORA-06550: line 7, column 16: PL/SQL: Statement ignored
   OK
   ‘APEXLIB_SCHEMA_EXPORT’ ORDER BY TABLE_NAME ) LOOP — get INSERT statements for table vData := vData||CHR(10)||generateInsertStatements(pSchemaName,rTAB.TABLE_NAME); END LOOP; — RETURN vData; — END generateInsertStatements; — –============================================================================== — Sets the Parameters for DBMS_METADATA. This controls whether additional Info — like Tablespace, Storage, Segment Attributes and so on is generated –============================================================================== PROCEDURE setMetadataParameters ( pHandle IN NUMBER , pSchemaName IN VARCHAR2 , pObjectType IN VARCHAR2 , pShowStorage IN BOOLEAN := FALSE , pShowTablespace IN BOOLEAN := FALSE , pShowSegmentAttributes IN BOOLEAN := FALSE ) IS vModifyHandle NUMBER; vDdlHandle NUMBER; BEGIN — — get Handle to modify some stuff vModifyHandle := DBMS_METADATA.Add_Transform(pHandle, ‘MODIFY’); — — tell Output to remove Schema Name (set it to NULL) DBMS_METADATA.Set_Remap_Param(vModifyHandle, ‘REMAP_SCHEMA’, pSchemaName, NULL); — — Einstellungen für das DDL vDdlHandle := DBMS_METADATA.Add_Transform(pHandle, ‘DDL’); — IF pObjectType IN (‘TABLE’,’INDEX’,’CONSTRAINT’) THEN DBMS_METADATA.Set_Transform_Param(vDdlHandle, ‘TABLESPACE’, pShowTablespace); DBMS_METADATA.Set_Transform_Param(vDdlHandle, ‘STORAGE’, pShowStorage); DBMS_METADATA.Set_Transform_Param(vDdlHandle, ‘SEGMENT_ATTRIBUTES’, pShowSegmentAttributes); END IF; — IF pObjectType IN (‘TABLE’) THEN DBMS_METADATA.Set_Transform_Param(vDdlHandle, ‘REF_CONSTRAINTS’, FALSE); END IF; — DBMS_METADATA.Set_Transform_Param(vDdlHandle, ‘SQLTERMINATOR’, TRUE); — END setMetadataParameters; — –============================================================================== — Returns DDL Statements for all objects of given object type. — pExcludeObjectList should look like this: ‘EMP, DEPT, MYTABLE’ –============================================================================== FUNCTION generateDDLStatements ( pSchemaName IN VARCHAR2 , pObjectType IN VARCHAR2 , pExcludeObjectList IN VARCHAR2 := NULL ) RETURN CLOB IS vHandle NUMBER; vResultArray SYS.ku$_ddls; vResultClob CLOB; BEGIN vHandle := DBMS_METADATA.Open(pObjectType); — — set general parameters for Metadata API setMetadataParameters ( pHandle => vHandle , pSchemaName => pSchemaName , pObjectType => pObjectType , pShowStorage => FALSE , pShowTablespace => FALSE , pShowSegmentAttributes => FALSE ); — — which objects should not be exported? /* IF pExcludeObjectList IS NOT NULL THEN — assume we get a string having tablenames to be excluded like this: ‘EMP, DEPT’ — we need to get rid of the whitespaces and add single quotes around each table name DBMS_METADATA.Set_Filter(vHandle, ‘EXCLUDE_NAME_EXPR’, ‘IN (”’||REPLACE(REPLACE(pExcludeObjectList,’ ‘),’,’,”’,”’)||”’)’); END IF; */ — IF pObjectType = ‘TABLE’ THEN DBMS_METADATA.Set_Filter(vHandle, ‘EXCLUDE_NAME_EXPR’, ‘IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ”APEX$_%” UNION SELECT ”APEXLIB_SCHEMA_EXPORT” FROM DUAL)’); END IF; — IF pObjectType NOT IN (‘VIEW’,’INDEXTYPE’,’SEQUENCE’,’TYPE’,’PACKAGE’,’FUNCTION’,’PROCEDURE’) THEN DBMS_METADATA.Set_Filter(vHandle, ‘EXCLUDE_BASE_OBJECT_NAME_EXPR’, ‘LIKE ”APEX$_%”’); END IF; — vResultClob := RPAD(‘-‘,80,’-‘)||CHR(10)|| ‘– Object Type ‘||pObjectType||CHR(10)|| RPAD(‘-‘,80,’-‘); — — get created DDL Statements LOOP vResultArray := dbms_metadata.fetch_ddl(vHandle); EXIT WHEN vResultArray IS NULL; — vResultClob := vResultClob||CHR(10)||vResultArray(1).ddltext; — IF vResultArray.EXISTS(2) THEN vResultClob := vResultClob||CHR(10)||vResultArray(2).ddltext; END IF; END LOOP; — — thats it, close up and release handle dbms_metadata.close(vHandle); — — return DDL Statements as CLOB return vResultClob; — END generateDDLStatements; — –============================================================================== — exports all APEX Applications associated to the current schema –============================================================================== FUNCTION exportAllApplications RETURN CLOB IS vApexApp CLOB; vSecurityGroupId NUMBER; BEGIN — vApexApp := RPAD(‘-‘,80,’-‘)||CHR(10)|| ‘– APEX Exports ‘||CHR(10)|| RPAD(‘-‘,80,’-‘); — — get current workspace id SELECT MIN(WORKSPACE_ID) INTO vSecurityGroupId FROM APEX_WORKSPACES ; — — get all applications FOR rAPP IN ( SELECT APPLICATION_ID FROM APEX_APPLICATIONS WHERE APPLICATION_NAME ‘ApexLib Schema Export’ ORDER BY APPLICATION_ID ) LOOP — get Apex Application Export vApexApp := vApexApp||CHR(10)|| REPLACE ( WWV_FLOW_UTILITIES.export_application_to_clob(rApp.APPLICATION_ID,’Y’) , ‘wwv_flow_api.set_security_group_id(p_security_group_id=>’||LTRIM(vSecurityGroupId)||’);’ , ‘DECLARE’||CHR(10)|| ‘ vSecurityGroupId NUMBER;’||CHR(10)|| ‘BEGIN’||CHR(10)|| ‘ SELECT MIN(WORKSPACE_ID) INTO vSecurityGroupId FROM APEX_WORKSPACES;’||CHR(10)|| ‘ wwv_flow_api.set_security_group_id(p_security_group_id=>vSecurityGroupId);’||CHR(10)|| ‘END;’ ); END LOOP; — RETURN vApexApp; — END exportAllApplications; — –============================================================================== — Extracts all DDL Statements (Table, View, Trigger, Package, …) and all — DATA (in Form of INSERT Statements) as well as all APEX Applications — associated with the given (current) Schema. — All Statements are returned in a CLOB. –============================================================================== FUNCTION backupSchema ( pSchemaName IN VARCHAR2 , pExportMode IN VARCHAR2 ) RETURN CLOB IS vTableDDL CLOB; vData CLOB; vOtherDDL CLOB; vApexApp CLOB; BEGIN — get CREATE TABLE statements IF INSTR(pExportMode,’DDL’) > 0 THEN vTableDDL := generateDDLStatements(pSchemaName,’TABLE’); END IF; — — get INSERT INTO table statements IF INSTR(pExportMode,’DATA’) > 0 THEN vData := generateInsertStatements(pSchemaName); END IF; — — get DDL statements for all object types except tables IF INSTR(pExportMode,’DDL’) > 0 THEN vOtherDDL := generateDDLStatements(pSchemaName,’VIEW’) ||CHR(10)|| generateDDLStatements(pSchemaName,’INDEX’) ||CHR(10)|| generateDDLStatements(pSchemaName,’INDEXTYPE’) ||CHR(10)|| generateDDLStatements(pSchemaName,’CONSTRAINT’) ||CHR(10)|| generateDDLStatements(pSchemaName,’REF_CONSTRAINT’)||CHR(10)|| generateDDLStatements(pSchemaName,’TRIGGER’) ||CHR(10)|| generateDDLStatements(pSchemaName,’SEQUENCE’) ||CHR(10)|| generateDDLStatements(pSchemaName,’TYPE’) ||CHR(10)|| generateDDLStatements(pSchemaName,’FUNCTION’) ||CHR(10)|| generateDDLStatements(pSchemaName,’PROCEDURE’) ||CHR(10)|| generateDDLStatements(pSchemaName,’PACKAGE’) ; END IF; — — get all APEX Applications associated to the current schema IF INSTR(pExportMode,’APEX’) > 0 THEN vApexApp := exportAllApplications; END IF; — RETURN ‘SET DEFINE OFF’||CHR(10)||vTableDDL||CHR(10)||vData||CHR(10)||vOtherDDL||CHR(10)||vApexApp; — END backupSchema; — — –============================================================================== — calls backupSchema and converts result to a BLOB –============================================================================== FUNCTION exportToBlob ( pSchemaName IN VARCHAR2 , pExportMode IN VARCHAR2 ) RETURN BLOB IS vClob CLOB; vBlob BLOB; vSize NUMBER; vDestOffset NUMBER := 1; vSrcOffset NUMBER := 1; vAmount INTEGER := DBMS_LOB.LOBMAXSIZE; vBlobCsid NUMBER := DBMS_LOB.DEFAULT_CSID; vLangCtx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; vWarning INTEGER; BEGIN vClob := backupSchema(pSchemaName,pExportMode); — DBMS_LOB.CreateTemporary(vBlob,FALSE,2); — vAmount := DBMS_LOB.getLength(vClob); — DBMS_LOB.convertToBlob ( dest_lob => vBlob , src_clob => vClob , amount => vAmount , dest_offset => vDestOffset , src_offset => vSrcOffset , blob_csid => vBlobCsid , lang_context => vLangCtx , warning => vWarning ); — RETURN vBlob; — END exportToBlob; — BEGIN — get the Schema Export vBlob := exportToBlob(SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’),V(‘P1_EXPORT_MODE’)); — DELETE APEXLIB_SCHEMA_EXPORT; — INSERT INTO APEXLIB_SCHEMA_EXPORT ( CREATION_DATE , EXPORT_BLOB ) VALUES ( SYSDATE , vBlob ); END;–>

7. Peter,

   Sorry to bother you, I downloaded the file, and imported it into apex.oracle.com as an application but when I try to run it, I receive the following error:

   ORA-06550: line 4, column 35: PL/SQL: ORA-00936: missing expression ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored
   Error Error exporting the Application: ORA-06550: line 4, column 35: PL/SQL: ORA-00936: missing expression ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored

   Any suggestions?

   Thanks!

8. Hi Courtney,

   did you install the supporting objects when importing the schema-export application ?

   Peter

9. Hi,

   I’m also having trouble exporting using this application on apex.oracle.com. I did install the application with supported objects, which, as far as I can tell, is only a table named APEXLIB_SCHEMA_EXPORT. I have confirmed it is in place.

   This only occurs when selecting “Data” as an export option.

   Thanks for your assistance,

   -Luis

   ORA-06550: line 4, column 1379: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 52: PLS-00364: loop index variable ‘RDATA’ use is invalid ORA-06550: line 7, column 16: PL/SQL: Statement ignored

   Error exporting the Application: ORA-06550: line 4, column 1379: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 4, column 27: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 52: PLS-00364: loop index variable ‘RDATA’ use is invalid ORA-06550: line 7, column 16: PL/SQL: Statement ignored

10. Hi Peter,

    I´m getting this error trying to export only data:

    Error Error exporting the Application: ORA-01489: result of string concatenation is too long
    ORA-01489: result of string concatenation is too long

    OK

    Apex 4.1.1 – Oracle DB 11.2.0.3

11. Hi Jose,

    that can happen when you have many tables or tables with a lot of columns.

    Does that happen in your environment or on a hosted instance (e.g. apex.oracle.com) ?

12. Hello sir,

    I have created two tables Employee_mst
    Structure of table Enployee_mst
    Emp_id (primary key)
    Emp_name
    DOJ
    Phone_number
    DOB
    department (foreign key) references DEPTMST(dept_code)
    Designation

    The structure of table is DEPTMST
    DEPT_CODE(Primary key)
    DEPT_NAME
    DEPT_LOC

    On the basis of these two table i have created two forms
    Form1 is Employee registration from
    in which department column is a LOV which got updated whenever a new department get inserted in depertment_master form which is based on dept_mst table.

    Now I wnat to create a report (department wise employee)
    For which i followed following steps
    step 1: created a blank page named deparment wise employee report page no:2
    step 2: I created a region of HTML type whose title is search
    step3 : I created another region on same page of report type whose title id department wise employee &P2_TEXT
    query for report is:

    SELECT
    “EMPLOYEE_MST”.”EMP_ID” “EMPLOYEE_ID”,
    “EMPLOYEE_MST”.”EMP_NAME” “EMPLOYEE_NAME”,
    “EMPLOYEE_MST”.”DESIGNATION”,
    “EMPLOYEE_MST”.”DOJ” “HIRE_DATE”,
    “EMPLOYEE_MST”.”DEPARTMENT”
    FROM
    “EMPLOYEE_MST”
    WHERE DEPARTMENT=decode(:P2_DEPT,’%null%’,department,:P2_DEPT)
    step4: i created a item select list ,item name is P2_DEPT. this list is coming from dynamic LOV dept_search query for this is
    select dept_name d,dept_code r
    from deptmst
    order by 1

    now when i am running this page it is giving error
    ORA-06592 case not found while page rendering

    Please tell me where i am wrong

    Please

13. Harshita,

    please direct question regarding APEX to the official Forum on OTN: https://forums.oracle.com/forums/forum.jspa?forumID=137

14. This is only for local development right? It cant be used on web oracle apex?