У меня есть приложения, созданные в апексе, в каждом приложении есть количество страниц.
Я хочу создать хранимую процедуру для экспорта каждого из этих приложений и страниц.
Я наткнулся на
SELECT * FROM APEX_APPLICATIONS;
select table_name from all_tables where owner='APEX_030200';
select username from all_users where username like 'APEX_0%' or username like 'FLOWS_0%';
Но это мне не помогает.
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ТЕЛО ПАКЕТА APEX_UTILITY КАК
PROCEDURE EXPORT_WORKSPACE( as_OracleDirectoryName IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
an_WorkspaceID IN APEX_040100.APEX_APPLICATIONS.WORKSPACE_ID%TYPE,
as_WorkspaceName IN APEX_040100.APEX_APPLICATIONS.WORKSPACE%TYPE )
IS
lclb_WorkspaceExport CLOB;
ls_ExportFileName VARCHAR2(128);
BEGIN
lclb_WorkspaceExport := WWV_FLOW_UTILITIES.EXPORT_WORKSPACE_TO_CLOB( p_workspace_id => an_WorkspaceID,
p_include_team_development => FALSE );
ls_ExportFileName := 'WS_' || as_WorkspaceName || '.sql';
DBMS_XSLPROCESSOR.CLOB2FILE( lclb_WorkspaceExport,
as_OracleDirectoryName,
ls_ExportFileName, 0 );
END;
PROCEDURE EXPORT_APPLICATION( as_OracleDirectoryName IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
an_ApexApplicationID IN APEX_040100.APEX_APPLICATIONS.APPLICATION_ID%TYPE )
IS
lclb_AppExport CLOB;
ls_ExportFileName VARCHAR2(128);
BEGIN
lclb_AppExport := WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB( p_application_id => an_ApexApplicationID,
p_export_ir_public_reports => 'N',
p_export_ir_private_reports => 'N',
p_export_ir_notifications => 'N');
ls_ExportFileName := 'APP_' || an_ApexApplicationID || '.sql';
DBMS_XSLPROCESSOR.CLOB2FILE( lclb_AppExport,
as_OracleDirectoryName,
ls_ExportFileName, 0 );
END;
PROCEDURE EXPORT_APPLICATION_PAGE( as_OracleDirectoryName IN ALL_DIRECTORIES.DIRECTORY_NAME%TYPE,
an_ApexApplicationID IN APEX_040100.APEX_APPLICATION_PAGES.APPLICATION_ID%TYPE,
an_ApexPageID IN APEX_040100.APEX_APPLICATION_PAGES.PAGE_ID%TYPE )
IS
lclb_PageExport CLOB;
ls_ExportFileName VARCHAR2(128);
BEGIN
lclb_PageExport := WWV_FLOW_UTILITIES.EXPORT_PAGE_TO_CLOB(
p_application_id => an_ApexApplicationID,
p_page_id => an_ApexPageID );
ls_ExportFileName := 'APP_' || an_ApexApplicationID || '_' ||
'PAGE_' || an_ApexPageID || '.sql';
DBMS_XSLPROCESSOR.CLOB2FILE( lclb_PageExport,
as_OracleDirectoryName,
ls_ExportFileName, 0 );
END;
FUNCTION EXPORT_APEX_OBJECTS
RETURN NUMBER
IS
ls_OracleDirectoryName ALL_DIRECTORIES.DIRECTORY_NAME%TYPE;
ls_WorkspaceName APEX_APPLICATIONS.WORKSPACE%TYPE;
ln_WorkspaceID APEX_APPLICATIONS.WORKSPACE_ID%TYPE;
lb_WorkspaceAlreadyAdded BOOLEAN := FALSE;
lclb_PageExport CLOB;
ln_ExportObjectCnt NUMBER := 0;
ls_ExportFileName VARCHAR2(128);
CURSOR lcsr_GetApexAppBackupTargets IS
SELECT APEX_APPLICATION_ID
FROM APEX_APP_BACKUP_TARGETS;
lrec_ApexAppBackupTarget lcsr_GetApexAppBackupTargets%ROWTYPE;
CURSOR lcsr_GetExportAppAndWorkspace IS
SELECT APPLICATION_ID, APPLICATION_NAME, WORKSPACE_ID, WORKSPACE
FROM APEX_APPLICATIONS
WHERE APPLICATION_ID = lrec_ApexAppBackupTarget.APEX_APPLICATION_ID;
lrec_ExportAppAndWorkspace lcsr_GetExportAppAndWorkspace%ROWTYPE;
CURSOR lcsr_GetExportPages IS
SELECT P.ID AS PAGE_ID
FROM APEX_040200.WWV_FLOW_STEPS P
WHERE FLOW_ID = lrec_ExportAppAndWorkspace.APPLICATION_ID;
lrec_ExportPage lcsr_GetExportPages%ROWTYPE;
TYPE TAB_WORKSPACE_LIST IS TABLE OF APEX_APPLICATIONS.WORKSPACE_ID%TYPE
INDEX BY APEX_APPLICATIONS.WORKSPACE%TYPE;
ltb_DistinctTablespaces TAB_WORKSPACE_LIST;
APEX_WORKSPACE_EXP_DIR CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_WS_EXPORT_DIR';
APEX_APPLICATION_EXP_DIR CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_APP_EXPORT_DIR';
APEX_PAGE_EXP_DIR CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'APEX_PAGE_EXPORT_DIR';
BEGIN
APEX_CUSTOM_AUTH.SET_USER('admin');
OPEN lcsr_GetApexAppBackupTargets;
LOOP
FETCH lcsr_GetApexAppBackupTargets INTO lrec_ApexAppBackupTarget;
EXIT WHEN lcsr_GetApexAppBackupTargets%NOTFOUND;
OPEN lcsr_GetExportAppAndWorkspace;
LOOP
FETCH lcsr_GetExportAppAndWorkspace INTO lrec_ExportAppAndWorkspace;
EXIT WHEN lcsr_GetExportAppAndWorkspace%NOTFOUND;
-- Compile a distinct list of container APEX Workspaces to be
-- exported later
lb_WorkspaceAlreadyAdded := ltb_DistinctTablespaces.EXISTS( lrec_ExportAppAndWorkspace.WORKSPACE );
If ( lb_WorkspaceAlreadyAdded = FALSE ) Then
ltb_DistinctTablespaces( lrec_ExportAppAndWorkspace.WORKSPACE ) := lrec_ExportAppAndWorkspace.WORKSPACE_ID;
End If;
WWV_FLOW_API.SET_SECURITY_GROUP_ID(ltb_DistinctTablespaces( lrec_ExportAppAndWorkspace.WORKSPACE ));
-- Export the application
ls_OracleDirectoryName := APEX_APPLICATION_EXP_DIR;
EXPORT_APPLICATION( ls_OracleDirectoryName, lrec_ExportAppAndWorkspace.APPLICATION_ID );
ln_ExportObjectCnt := ln_ExportObjectCnt + 1;
OPEN lcsr_GetExportPages;
LOOP
FETCH lcsr_GetExportPages INTO lrec_ExportPage;
EXIT WHEN lcsr_GetExportPages%NOTFOUND;
ls_OracleDirectoryName := APEX_PAGE_EXP_DIR;
EXPORT_APPLICATION_PAGE( ls_OracleDirectoryName,
lrec_ExportAppAndWorkspace.APPLICATION_ID,
lrec_ExportPage.PAGE_ID );
ln_ExportObjectCnt := ln_ExportObjectCnt + 1;
END LOOP;
CLOSE lcsr_GetExportPages;
END LOOP;
CLOSE lcsr_GetExportAppAndWorkspace;
END LOOP;
CLOSE lcsr_GetApexAppBackupTargets;
ls_OracleDirectoryName := APEX_WORKSPACE_EXP_DIR;
ls_WorkspaceName := ltb_DistinctTablespaces.FIRST;
WHILE ls_WorkspaceName IS NOT NULL
LOOP
ln_WorkspaceID := ltb_DistinctTablespaces(ls_WorkspaceName);
WWV_FLOW_API.SET_SECURITY_GROUP_ID(ln_WorkspaceID);
EXPORT_WORKSPACE( ls_OracleDirectoryName, ln_WorkspaceID, ls_WorkspaceName );
ln_ExportObjectCnt := ln_ExportObjectCnt + 1;
ls_WorkspaceName := ltb_DistinctTablespaces.NEXT(ls_WorkspaceName);
END LOOP;
RETURN ln_ExportObjectCnt;
END;
КОНЕЦ APEX_UTILITY; /
