Oracle 12cR2 DBMS_RECOVERABLE

Oracle DBMS_RECOVERABLE_SCRIPT Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.

Purpose Undocumented but related to Streams

AUTHID CURRENT_USER

Data Types TYPE connect_info IS RECORD ( username VARCHAR2(30), password VARCHAR2(30), connect_identifier VARCHAR2(30), connect_key VARCHAR2(??), i_customer_id BINARY_INTEGER); TYPE connect_info_set IS TABLE OF connect_info;

Dependencies

CDB_RECOVERABLE_SCRIPT	DBA_RECOVERABLE_SCRIPT_HIST	DBMS_STREAMS_AUTO_INT
CDB_RECOVERABLE_SCRIPT_BLOCKS	DBA_RECOVERABLE_SCRIPT_PARAMS	DBMS_STREAMS_MC
CDB_RECOVERABLE_SCRIPT_ERRORS	DBMS_ASSERT	DBMS_STREAMS_MT
CDB_RECOVERABLE_SCRIPT_HIST	DBMS_LOGREP_UTIL	DBMS_STREAMS_SM
CDB_RECOVERABLE_SCRIPT_PARAMS	DBMS_RECO_SCRIPT_INT	DBMS_SYS_ERROR
DBA_RECOVERABLE_SCRIPT	DBMS_RECO_SCRIPT_INVOK	DBMS_UTILITY
DBA_RECOVERABLE_SCRIPT_BLOCKS	DBMS_RECO_SCRIPT_LIB	PLITBLM
DBA_RECOVERABLE_SCRIPT_ERRORS	DBMS_STANDARD	RECO_SCRIPT$

Documented No

First Available 10gR1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvthrse.plb

Subprograms

ADD_FORWARD_BLOCK	INSERT_PARAM	RUN
CREATE_SCRIPT	MODIFY_FORWARD_BLOCK	UPDATE_COMMENT
DROP_SCRIPT	MODIFY_UNDO_BLOCK	UPDATE_STATUS

ADD_FORWARD_BLOCK

Undocumented dbms_recoverable_script.add_forward_block( script_id IN RAW, block IN CLOB, dblink_forward IN VARCHAR2, block_comment IN VARCHAR2, block_num IN NUMBER);

TBD

CREATE_SCRIPT

Creates a recoverable script dbms_recoverable_script.create_script( invoking_pkg_owner IN VARCHAR2, invoking_pkg IN VARCHAR2, invoking_proc IN VARCHAR2, script_comment IN VARCHAR2, script_id OUT RAW);

CREATE OR REPLACE PACKAGE testpkg AUTHID CURRENT_USER IS

        PROCEDURE display_string(teststr IN VARCHAR2);

      END testpkg;

      /

      

      CREATE OR REPLACE PACKAGE BODY testpkg IS

        PROCEDURE display_string(teststr IN VARCHAR2) IS

        BEGIN

          dbms_output.put_line(teststr);

        END;

      END testpkg;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

      

      set serveroutput on

      

      DECLARE

       r RAW(32);

      BEGIN

        dbms_recoverable_script.create_script('Test', 'A', 'B', 'C', r);

        dbms_output.put_line(r);

      END;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

      

      -- replace the raw value with the one you receive

      exec dbms_recoverable_script.drop_script(TO_RAW(TO_BLOB('CB68CBC3E4264A1680AE4620CFE6CD66')));

      

      

      DECLARE

       r RAW(32);

      BEGIN

        dbms_recoverable_script.create_script('Test1', 'DISPLAY_STRING', 'TESTPKG', 'SYS', r);

        dbms_output.put_line(r);

        dbms_recoverable_script.insert_param(r, 'TESTSTR', 'Morgan', 1);

        dbms_recoverable_script.update_comment(r, 'Test2');

        dbms_recoverable_script.run(r); 

        dbms_recoverable_script.drop_script(r);

      END;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

DROP_SCRIPT

Drops a recoverable script dbms_recoverable_script.drop_script( script_id IN RAW, flags IN BINARY_INTEGER, ignore_remote_error IN BOOLEAN, check_owner IN BOOLEAN, force IN BOOLEAN);

See CREATE_SCRIPT Demo Above

INSERT_PARAM

Undocumented dbms_recoverable_script.insert_param( script_id IN RAW, name IN VARCHAR2, value IN VARCHAR2, param_index IN NUMBER);

See CREATE_SCRIPT Demo Above

MODIFY_FORWARD_BLOCK

Undocumented dbms_recoverable_script.modify_forward_block( script_id IN RAW, block_num IN VARCHAR2, forward_block IN CLOB, dblink_forward IN VARCHAR2, action IN VARCHAR2);

TBD

MODIFY_UNDO_BLOCK

Undocumented dbms_recoverable_script.modify_undo_block( script_id IN RAW, block_num IN NUMBER, undo_block IN CLOB, dblink_undo IN VARCHAR2, action IN VARCHAR2);

TBD

RUN

Run a recovery script

Overload 1 dbms_recoverable_script.run( script_id IN RAW, forward IN BOOLEAN, remote_state IN CLOB);

See CREATE_SCRIPT Demo Above

Overload 2 dbms_recoverable_script.run( script_id IN RAW, forward IN BOOLEAN, remote_state IN CLOB, flags IN BINARY_INTEGER, conn_info IN dbms_recoverable_script.connect_info_set);

TBD

UPDATE_COMMENT

Modify a script comment dbms_recoverable_script.update_comment( script_id IN RAW, script_comment IN VARCHAR2);

See CREATE_SCRIPT Demo Above

UPDATE_STATUS

Undocumented dbms_recoverable_script.update_status( script_id IN RAW, script_status IN NUMBER);

TBD