Code example: String replacement in PL/SQL

CREATE OR REPLACE PACKAGE QA_UTIL AS
  PROCEDURE imsi_update;
END QA_UTIL;

/

CREATE OR REPLACE PACKAGE BODY QA_UTIL AS
-------------------------------------------
  PROCEDURE imsi_update AS

    CURSOR imsi_rows_cur IS
    SELECT QAT2_TESTS_RAN.*, rowid FROM QAT2_TESTS_RAN
     WHERE generalrange1 LIKE '% IMSI = %'
       AND generalrange1 NOT LIKE '% IMSI = ''%'
       AND test_name LIKE 'VFES%';


    v_generalrange      QAT2_TESTS.generalrange1%TYPE;
    v_generalrange_out  QAT2_TESTS.generalrange1%TYPE;
    v_part_1_ind        NUMBER;
    v_part_1            QAT2_TESTS.generalrange1%TYPE;

    v_part_imsi_ind     NUMBER;
    v_part_imsi         QAT2_TESTS.generalrange1%TYPE;

    v_part_2_ind        NUMBER;
    v_part_2         QAT2_TESTS.generalrange1%TYPE;

  BEGIN
    FOR imsi_rows_rec IN imsi_rows_cur LOOP
      --select * from DEBUG_IPN_W_EDR where IMSI = 214010000000010 and DESCRIPTION='GPRS' and ACTION_TYPE_NAME= 'RELAY' and ACTION_REASON_NAME='Sub: SubInact'
      v_generalrange := imsi_rows_rec.generalrange1;

      v_part_1_ind := INSTR(v_generalrange,'IMSI = ') + LENGTH ('IMSI = ')-1;
      v_part_1 := SUBSTR(v_generalrange,0,v_part_1_ind);

      v_part_imsi_ind := v_part_1_ind+1;
      v_part_imsi  := SUBSTR(v_generalrange,v_part_imsi_ind,LENGTH('214010000000010'));

      v_part_2_ind := v_part_imsi_ind + LENGTH('214010000000010');
      v_part_2 := SUBSTR(v_generalrange,v_part_2_ind);

      v_generalrange_out := v_part_1||''''||v_part_imsi||''''||v_part_2;

      UPDATE QAT2_TESTS_RAN 
         SET generalrange1 = v_generalrange_out 
       WHERE QAT2_TESTS_RAN.rowid = imsi_rows_rec.rowid;
      commit;

    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END imsi_update;
-------------------------------------------
END QA_UTIL;

/

Code Example. Keep History of a Table Split into Several Tables

===============================
General
===============================
Purpose: Keep history of a fast growing table with performing DELETE.
This is a LOG table, so not important to keep all records, thus ROWNUM < 10001 limit was used per day.
Under normal execution - the number of entries in LOG tables is small.
It is when system encounter some unexpected behavior, is when the LOG table is overloaded with error messages having same text over and over again.


===============================
Code
===============================

PL/SQL Code

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

PROCEDURE move_tb_2_tb  (p_source_table_name IN VARCHAR2,
                         p_target_table_name IN VARCHAR2) IS

  v_module_name  VARCHAR2(100);
  v_sql_str      VARCHAR2(1000);
  v_msg_str      VARCHAR2(1000);
  
BEGIN

  v_module_name := 'ADMIN_UTIL.move_tb_2_tb';
  
  v_sql_str := 'TRUNCATE TABLE '||p_target_table_name;
  EXECUTE IMMEDIATE v_sql_str;

  v_sql_str :=   'INSERT /*+ APPEND */ INTO '||p_target_table_name||' SELECT * FROM '||p_source_table_name||' WHERE rownum < 10001';
  EXECUTE IMMEDIATE v_sql_str;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_msg_str := 'Unexpected Error: '||SQLERRM;
      SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
END move_tb_2_tb;

PROCEDURE purge_sga_w_log IS

  v_module_name  VARCHAR2(100);
  v_msg_str      VARCHAR2(1000);  
  v_origin_table VARCHAR2(30);
  v_source_table VARCHAR2(30);
  v_target_table VARCHAR2(30);  
  v_index        NUMBER;
  v_max_index    NUMBER;
BEGIN

  v_module_name := 'ADMIN_UTIL.purge_sga_w_log';
  v_origin_table := 'SGA_W_LOG';

  v_msg_str := 'Starting';
  SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
  v_max_index := 7;
  v_index := v_max_index;

  
  WHILE v_index > 0 LOOP
    IF v_index = 1 THEN
      v_source_table := v_origin_table;      
    ELSE            
      v_source_table := v_origin_table||'_'||TO_CHAR(v_index-1);
    END IF;  
    v_target_table := v_origin_table||'_'||TO_CHAR(v_index);  
    move_tb_2_tb(v_source_table, v_target_table);
    v_index := v_index -1;
  END LOOP;  
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_origin_table;

  v_msg_str := 'Finished';
  SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
  
  EXCEPTION
     WHEN OTHERS THEN
        v_msg_str := 'Unexpected error: '||SUBSTR(SQLERRM, 1, 900);
        BEGIN
          SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;      
  END purge_sga_w_log;        
END  ADMIN_UTIL;


View on top of the partial tables.
CREATE OR REPLACE VIEW SGA_W_LOG_VW
AS SELECT log_table, PROCEDURE_NAME, data, TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss') AS ts_last_modified FROM (
SELECT 'SGA_W_LOG' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG  
UNION ALL
SELECT 'SGA_W_LOG_1' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_1
UNION ALL
SELECT 'SGA_W_LOG_2' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_2
UNION ALL
SELECT 'SGA_W_LOG_3' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_3
UNION ALL
SELECT 'SGA_W_LOG_4' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_4
UNION ALL
SELECT 'SGA_W_LOG_5' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_5
UNION ALL
SELECT 'SGA_W_LOG_6' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_6
UNION ALL
SELECT 'SGA_W_LOG_7' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_7
)
ORDER BY ts_last_modified DESC

PL/SQ and sqlplus batch by example. Delete from Table in Three Steps

=======================
General
=======================
Generic delete from  a table using Business Logic.
Several million of records are being deleted.
The flow is a three steps process:

Step1.

pre_run_steps.sh

This create backup table for SOURCE_DATA_SUBSCRIBER (SOURCE_DATA_SUBSCRIBER_BAK) 

Creates additional temporary tables which are to be used during actual delete step.


Step2.

main_delete.sh 

The actual DELETE. 

Create the PL/SQL Package, which handles the business logic.

Call the PL/SQL package.

Progress is logged into SGA_W_LOG Table.

Step3.

post_run_steps.sh
This drop the SOURCE_DATA_SUBSCRIBER_BAK and drop additional temporary tables created in pre_run_steps.sh

=======================
Code
=======================

Step 0.
set_vipuser.sql

Step1.

pre_run_steps.sh

pre_run_steps.batpre_run_steps.sql


Step2.

main_delete.shmain_delete.bat

main_delete.sql

MULTI_DELETE.sql

Step3.

post_run_steps.shpost_run_steps.batpost_run_steps.sql

Step 0.
set_vipuser.sql
--Set the connection detailsdefine vipuser=my_userdefine vippass=my_passdefine connectStr=MY_INSTANCE

Step 1.

pre_run_steps.sh
sqlplus /nolog @pre_run_steps.sql

pre_run_steps.bat

sqlplus /nolog @pre_run_steps.sql

pre_run_steps.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2;

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SOURCE_DATA_SUBSCRIBER_BAK'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SOURCE_DATA_SUBSCRIBER_BAK';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;

PROMPT INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;

COMMIT;

PROMPT CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SGA_LOC_GRP_OUTBACK_NETWORKS'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   SGA_LOC_GRP_OUTBACK_NETWORKS (network_id        NUMBER(10) ) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE TABLE   MSISDN_PROFILE_NOT_PRE

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'MSISDN_PROFILE_NOT_PRE'; 

  IF v_counter = 0 THEN

    v_sql_str := 'CREATE TABLE   MSISDN_PROFILE_NOT_PRE (msisdn VARCHAR2(30) NOT NULL) TABLESPACE IGT_TEMP_TABLE;';

  ELSE

    v_sql_str := 'TRUNCATE TABLE MSISDN_PROFILE_NOT_PRE';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS

SELECT DISTINCT network_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUP_ENTRIES 

   WHERE location_group_id IN 

         (SELECT location_group_id 

    FROM SOURCE_USER.SGA_LOCATION_GROUPS 

WHERE LOCATION_GROUP_NAME='Outback');

commit;

PROMPT INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)

SELECT KEY1 FROM SOURCE_USER.SFI_CUSTOMER_PROFILE WHERE ATTR1 NOT IN ('RogEngPre','RogFrPre','FidoEngPre','FidoFrPre');

commit;

PROMPT CREATE INDEX SFI_CST_PROF_IX

DECLARE

  v_sql_str    VARCHAR2(2000);

  v_counter    NUMBER;

BEGIN

  SELECT COUNT(*) INTO v_counter FROM USER_INDEXES WHERE index_name = 'SFI_CST_PROF_IX'; 

  IF v_counter > 0 THEN

    v_sql_str := 'DROP INDEX SFI_CST_PROF_IX';

  END IF;

  EXECUTE IMMEDIATE v_sql_str;

END;

/

PROMPT CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;

BEGIN

  DBMS_STATS.gather_table_stats('&&vipuser','MSISDN_PROFILE_NOT_PRE');

END;

/

PROMPT Finished Pre Run step

EXIT;

Step 2.

main_delete.sh

sqlplus /nolog @main_delete.sql

main_delete.bat

sqlplus /nolog @main_delete.sql

main_delete.sql

@../set_vipuser.sql

SET SERVEROUT ON

SET HEADING OFF

SET LINESIZE 400

SET PAGESIZE 0

SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr

conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);

PROMPT CREATE PROCEDURE MULTI_DELETE

@MULTI_DELETE.sql

PROMPT EXECUTE PROCEDURE MULTI_DELETE

PROMPT .............................

PROMPT Running DELETE....

PROMPT Follow Up on process Progress by: SELECT * FROM SGA_W_LOG ORDER BY TS_LAST_MODIFIED;

PROMPT .............................

BEGIN

  MULTI_DELETE;

END;

/

PROMPT Finished Run step  

EXIT;

MULTI_DELETE.sql

CREATE OR REPLACE PROCEDURE MULTI_DELETE IS

    v_effected_rows NUMBER;

    v_delete_rows NUMBER;

    v_row_counter NUMBER;

    v_table_name VARCHAR2(30);

    v_sql_str VARCHAR2(1000);

    v_module_name VARCHAR2(30);

    v_msg_text VARCHAR2(1000);

  BEGIN

    v_table_name :='SOURCE_DATA_SUBSCRIBER';

    v_effected_rows := 1;

    v_row_counter := 0;

    v_module_name := 'MULTI DELETE';

    v_sql_str := 'DELETE FROM SOURCE_DATA_SUBSCRIBER WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS ) AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE) AND ROWNUM < 100001';

    v_msg_text  := 'Running SQL: '||v_sql_str;

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    v_msg_text  := 'Starting at: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss');

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

    WHILE v_effected_rows > 0 LOOP

      DELETE FROM SOURCE_DATA_SUBSCRIBER

       WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS )

         AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE)

         AND ROWNUM < 100001;

      v_effected_rows := SQL%ROWCOUNT;

      v_row_counter := v_row_counter + v_effected_rows;

      COMMIT;

      

      v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;      

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

      

    END LOOP;

    

    COMMIT;

    

    v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';

    INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

    VALUES(v_module_name,v_msg_text, SYSDATE);

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      INSERT INTO SGA_W_LOG (  procedure_name,  data,  ts_last_modified)

      VALUES(v_module_name,v_msg_text, SYSDATE);

      COMMIT;

  END MULTI_DELETE;

/

Step 3.

post_run_steps.shsqlplus /nolog @post_run_steps.sql

post_run_steps.bat
sqlplus /nolog @post_run_steps.sql

post_run_steps.sql

@../set_vipuser.sqlSET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF

PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr

EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK
DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK;

PROMPT DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS
DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS;

PROMPT DROP TABLE MSISDN_PROFILE_NOT_PRE
DROP TABLE MSISDN_PROFILE_NOT_PRE;

PROMPT Finished Post Run step
EXIT;

"ORA-00900: invalid SQL statement" and "ORA-02064 Distributed operation not supported" Errors When calling to Remote PL/SQL Procedure

================================
General
================================
A. When calling to a remote PL/SQL procedure from sqlplus - all works fine,
But when calling to the same remote PL/SQL procedure from PL/SQL code, an ORA-00900: invalid SQL statement error is thrown.

B. When calling a PL/SQL directly, all works fine, but when calling same procedure vi db link, it fails with ORA-02064 Distributed operation not supported

Why is that?

================================
ORA-00900 Example
================================

In this example, a DB_LINK named KUKU is created to the remote database.

CREATE DATABASE LINK KUKU CONNECT TO REMOTE_USER IDENTIFIED BY REMOTE_PASSWORD USING 'REMOTE_HOST_NAME';

In the remote database, a getDate Procedure in Package PKG_TEST should be activated.

In sqlplus
From sqlplus, any of these versions work:
EXEC PKG_TEST.getDate@KUKU;
EXEC REMOTE_USER.PKG_TEST.getDate@KUKU;

When creating a Synonym, it can be also used, to simplify code.

CREATE SYNONYM REMOTE_PKG FOR REMOTE_USER.PKG_TEST@KUKU

SELECT * FROM USER_SYNONYMS WHERE synonym_name = 'KUKU'

SYNONYM_NAME        TABLE_OWNER      TABLE_NAME      DB_LINK
------------------- ---------------- --------------- ---------------
REMOTE_PKG          REMOTE_USER      PKG_TEST        KUKU

So in sqlplus, it would be:
EXEC REMOTE_PKG.getDate;

In PL/SQL
When activating the same code in PL/SQL it MUST be inside a BEGIN END block.
Any other syntax, such as EXECUTE IMMEDIATE <sql_string> would fail with "ORA-00900: invalid SQL statement".

This is the correct code:

BEGIN
  PKG_TEST.getDate;
  EXCEPTION
    WHEN OTHERS THEN
      WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
      RAISE;
END;

================================
ORA-02064 Example
================================
In this example, same DB_LINK named KUKU is created to the remote database.
The procedure now is a DML Procedre, performing an update, and a commit.

The remote procedure has these API:

PKG_TEST.setDate(v_date    IN DATE,

                v_result  OUT NUMBER);

When calling the remote procedure in PL/SQL, it would be:


DECLARE
  v_date   DATE;
  v_result NUMBER;
BEGIN
  v_date := SYSDATE;
  PKG_TEST.setDate(v_date,v_result);
  EXCEPTION
    WHEN OTHERS THEN
      WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
      RAISE;

END;

The returned error is:

ORA-02064: distributed operation not supported
ORA-06512: at "REMOTE_USER.PKG_TEST", line 491

Per Oracle documentation, 

Error Cause:
One of the following unsupported operations was attempted:

1. array execute of a remote update with a subquery that references a dblink, or

2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or

3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.

Action:
Simplify remote update statement.

Per Oracle Metalink CALLING REMOTE PACKAGE RECEIVES ORA-2064 (Doc ID 1026597.6):

"This is Not A Bug. 

In the documentation for ORA-2064, it states that one of  the disallowed actions is "A commit is issued in a coordinated session from an RPC with OUT parameters." 

It happens that the return value of a function call counts as an OUT parameter for purposes of this rule. 

As a workaround for some cases Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package."

So, a work around this problem would be to change the remote Procedure to be Autonomous Transaction.

PKG_TEST.setDate(v_date    IN DATE,
                v_result  OUT NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;