How to Swap All Table & Index of Tablespace into different Tablespace

1. Find out distinct schema name of Tablespace using the following Query.

SELECT DISTINCT OWNER
  FROM ALL_TABLES
 WHERE TABLESPACE_NAME = 'TBS_01';

2. Now Create the following Procedure. [ if your table size is big you can add the parallel and nologging clause (||'  PARALLEL 8 NOLOGGING' ).

CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
   P_USER_NAME            IN VARCHAR2,
   P_CURRENT_TABLESPACE   IN VARCHAR2,
   P_NEW_TABLESPACE       IN VARCHAR2)
IS
   V_ALTER_SCRIPTS_TABLE   VARCHAR2 (3000);
   V_INDEX_NAME            VARCHAR2 (100);
   V_IDX_SCRIPTS           VARCHAR2 (3000);
BEGIN
   FOR IDX_TABLE
      IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
            FROM ALL_TABLES
           WHERE     OWNER = P_USER_NAME
                 AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
                 AND TABLE_NAME = P_TABLE_NAME)
   LOOP
      V_ALTER_SCRIPTS_TABLE :=
            'ALTER TABLE '
         || IDX_TABLE.OWNER
         || '.'
         || IDX_TABLE.TABLE_NAME
         || ' MOVE TABLESPACE '
         || P_NEW_TABLESPACE;

      BEGIN
         EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (
                  'EXCEPTION IN MOVING TABLESPACE'
               || IDX_TABLE.TABLE_NAME
               || ' : '
               || SQLERRM);
      END;

      BEGIN
         FOR IDX_INDEX
            IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
                  FROM ALL_INDEXES
                 WHERE     OWNER = P_USER_NAME
                       AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
         LOOP
            V_IDX_SCRIPTS :=
                  'ALTER INDEX '
               || IDX_INDEX.OWNER
               || '.'
               || IDX_INDEX.INDEX_NAME
               || ' REBUILD TABLESPACE '
               || P_NEW_TABLESPACE;

            BEGIN
               EXECUTE IMMEDIATE V_IDX_SCRIPTS;
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.PUT_LINE (
                        'EXCEPTION IN REBUILD INDEX'
                     || IDX_INDEX.INDEX_NAME
                     || ' : '
                     || SQLERRM);
            END;
         END LOOP;
      END;
   END LOOP;
END;
/

3. Now execute the procedure to move table into different Tablespace.

EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');

Sample Log:

SQL> conn /as sysdba
Connected.
SQL> CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
   P_USER_NAME            IN VARCHAR2,
  2    3     P_CURRENT_TABLESPACE   IN VARCHAR2,
  4     P_NEW_TABLESPACE       IN VARCHAR2,
  5     P_TABLE_NAME           IN VARCHAR2)
  6  IS
  7     V_ALTER_SCRIPTS_TABLE   VARCHAR2 (3000);
  8     V_INDEX_NAME            VARCHAR2 (100);
  9     V_IDX_SCRIPTS           VARCHAR2 (3000);
 10  BEGIN
 11     FOR IDX_TABLE
 12        IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
 13              FROM ALL_TABLES
 14             WHERE     OWNER = P_USER_NAME
 15                   AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
 16                   AND TABLE_NAME = P_TABLE_NAME)
 17     LOOP
 18        V_ALTER_SCRIPTS_TABLE :=
 19              'ALTER TABLE '
 20           || IDX_TABLE.OWNER
         || '.'
 21   22           || IDX_TABLE.TABLE_NAME
         || ' MOVE TABLESPACE '
 23   24           || P_NEW_TABLESPACE;
 25
 26        BEGIN
 27           EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
 28        EXCEPTION
 29           WHEN OTHERS
 30           THEN
 31              DBMS_OUTPUT.PUT_LINE (
 32                    'EXCEPTION IN MOVING TABLESPACE'
 33                 || IDX_TABLE.TABLE_NAME
 34                 || ' : '
 35                 || SQLERRM);
 36        END;
 37
 38        BEGIN
 39           FOR IDX_INDEX
 40              IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
 41                    FROM ALL_INDEXES
 42                   WHERE     OWNER = P_USER_NAME
 43                         AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
 44           LOOP
 45              V_IDX_SCRIPTS :=
 46                    'ALTER INDEX '
 47                 || IDX_INDEX.OWNER
 48                 || '.'
 49                 || IDX_INDEX.INDEX_NAME
 50                 || ' REBUILD TABLESPACE '
 51                 || P_NEW_TABLESPACE;
 52
 53              BEGIN
 54                 EXECUTE IMMEDIATE V_IDX_SCRIPTS;
 55              EXCEPTION
 56                 WHEN OTHERS
 57                 THEN
 58                    DBMS_OUTPUT.PUT_LINE (
 59                          'EXCEPTION IN REBUILD INDEX'
 60                       || IDX_INDEX.INDEX_NAME
 61                       || ' : '
 62                       || SQLERRM);
 63              END;
 64           END LOOP;
 65        END;
 66     END LOOP;
 67  END;
 68  /

Procedure created.

SQL>
SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS'  2    3  ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR                   ACCOUNTS                      TBS_01
SQL> EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');

PL/SQL procedure successfully completed.

SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS'  2    3  ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR                  ACCOUNTS                          TBS_02

SQL>

How to debug PL-SQL Program in RAC Database.

To debug PL-SQL code in RAC environment first create TNS in tnsnames.ora file using the only one instance. Connect into database using the new TNS name.


CBS_PROD_DB =
  (DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.201.200)(PORT = 1521))
      )
      (CONNECT_DATA =
          (SERVICE_NAME = CBSDB)    
          (INSTANCE_NAME = CBSDB1)
      )
  )

Move Whole Index From one Tablespace to Another Tablespace

CREATE OR REPLACE PROCEDURE SP_INDEX_MOVE

(P_USER_NAME VARCHAR2,

 P_NEW_TABLESPACE VARCHAR2

)

 IS

 V_INDEX_NAME VARCHAR2(100);

 V_IDX_SCRIPTS VARCHAR2(3000);

 BEGIN

    FOR IND IN (

        SELECT OWNER, INDEX_NAME, TABLESPACE_NAME

        FROM DBA_INDEXES

        WHERE OWNER=P_USER_NAME

        AND TABLESPACE_NAME<>P_NEW_TABLESPACE

        AND TABLE_NAME NOT LIKE 'SYS%'

       AND INDEX_TYPE<>'LOB')

    LOOP

       

    V_IDX_SCRIPTS:='ALTER INDEX '||IND.OWNER||'.'||IND.INDEX_NAME||' REBUILD TABLESPACE '||P_NEW_TABLESPACE;

    

        BEGIN

          EXECUTE IMMEDIATE V_IDX_SCRIPTS;

          

         EXCEPTION

                WHEN OTHERS THEN 

                 DBMS_OUTPUT.PUT_LINE('EXCEPTION IN REBUILD INDEX' || IND.INDEX_NAME ||' : '|| SQLERRM);

        END;

    

    END LOOP;    

 END;

============= Execution ==========

EXEC SP_INDEX_MOVE(USER,'TABLESPACE_NAME');

Faster Access Rows inside Table Using ROWID pseudocolumn.

ROWID : ROWID is pseudo column that returns the address of the row. It is faster to access data from table. This column are unique for an particular table. Rows in different tables that are stored together in the same cluster can have the same ROWID .

This is combination of :

The data object number of the object.
The data block in the datafile.
The position of the row in the data block (first row is 0).
The datafile number of tablespace.

SQL> SET TIMING ON;
SQL> CREATE TABLE BIG_TABLE
  2  (ID NUMBER(10),
  3  NAME VARCHAR2(200)
  4  );

Table created.

Elapsed: 00:00:00.05
SQL> INSERT INTO BIG_TABLE (ID , NAME)
  2  SELECT LEVEL, NULL FROM DUAL CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.11
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
  2  TYPE REC_DATA IS RECORD
  3  (
  4  ID BIG_TABLE.ID%TYPE
  5  );
  6  TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
  7  T_DATA TT_DATA;
  8  BEGIN
  9     SELECT ID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
 10
 11     FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
 12     UPDATE BIG_TABLE
 13     SET NAME ='NAME '||T_DATA(IND).ID
 14     WHERE ID=T_DATA(IND).ID;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:59.99
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> DECLARE
  2  TYPE REC_DATA IS RECORD
  3  (
  4  ID BIG_TABLE.ID%TYPE,
  5  ROW_ID VARCHAR2(1000)
  6  );
  7  TYPE TT_DATA IS TABLE OF REC_DATA INDEX BY PLS_INTEGER;
  8  T_DATA TT_DATA;
  9  BEGIN
 10     SELECT ID,ROWID BULK COLLECT INTO T_DATA FROM BIG_TABLE ;
 11
 12     FORALL IND IN T_DATA.FIRST .. T_DATA.LAST
 13     UPDATE BIG_TABLE
 14     SET NAME ='NAME '||T_DATA(IND).ID
 15     WHERE ROWID=T_DATA(IND).ROW_ID;
 16  END;
 17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.55
SQL>

ORA-01426: numeric overflow (Control numeric overflow error in PL/SQL)

SQL> set serveroutput on;
SQL> DECLARE
  2          V_INDEX_NUMBER NUMBER(14);
  3      TYPE REC_ACCOUNT_BAL IS RECORD
  4          (
  5          ACCOUNT_NUMBER NUMBER(14),
  6          ACCOUNT_BALANCE NUMBER(18,3)
  7          );
  8
  9      TYPE TT_ACCOUNT_BAL IS TABLE OF REC_ACCOUNT_BAL INDEX BY PLS_INTEGER;
 10          T_ACCOUNT_BAL TT_ACCOUNT_BAL;
 11  BEGIN
 12      V_INDEX_NUMBER:=1000100300012;
 13      T_ACCOUNT_BAL(V_INDEX_NUMBER).ACCOUNT_BALANCE:=5000.00;
 14      DBMS_OUTPUT.PUT_LINE(T_ACCOUNT_BAL(V_INDEX_NUMBER).ACCOUNT_BALANCE);
 15  END;
 16  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 13


SQL> DECLARE
  2          V_INDEX_NUMBER NUMBER(14);
  3      TYPE REC_ACCOUNT_BAL IS RECORD
  4          (
  5          ACCOUNT_NUMBER NUMBER(14),
  6          ACCOUNT_BALANCE NUMBER(18,3)
  7          );
  8
  9      TYPE TT_ACCOUNT_BAL IS TABLE OF REC_ACCOUNT_BAL INDEX BY VARCHAR2(14);
 10          T_ACCOUNT_BAL TT_ACCOUNT_BAL;
 11  BEGIN
 12      V_INDEX_NUMBER:=1000100300012;
 13      T_ACCOUNT_BAL(V_INDEX_NUMBER).ACCOUNT_BALANCE:=5000.00;
 14      DBMS_OUTPUT.PUT_LINE(T_ACCOUNT_BAL(V_INDEX_NUMBER).ACCOUNT_BALANCE);
 15  END;
 16  /
5000

PL/SQL procedure successfully completed.

SQL>

Oracle Servererror Login Trail.

SQL> CREATE TABLE LOG_FAILED_TRAIL
  2  (
  3    USER_NAME           VARCHAR2(200),
  4    ERROR_MESSAGE   VARCHAR2(300),
  5    LOGON_TIME          DATE                           DEFAULT SYSDATE,
  6    HOST_OR_IP          VARCHAR2(100 BYTE)             DEFAULT SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  7  );
Table created.
SQL> CREATE OR REPLACE TRIGGER LOGON_FAILURES_AUDIT
  2     AFTER SERVERERROR
  3     ON DATABASE
  4    DECLARE
  5    V_DB_USER VARCHAR2(300);
  6    V_ERROR_CODE VARCHAR2(100);
  7    V_ERROR_DESC VARCHAR2(200);
  8    V_HOST_OR_IP VARCHAR2(100);
  9    PROCEDURE SP_ERROR_TRACE IS
 10    BEGIN
 11      IF (IS_SERVERERROR(1017)) THEN
 12      V_ERROR_CODE:='1017';
 13      V_ERROR_DESC:='Invalid username/password.';
 14       ELSIF (IS_SERVERERROR(1005)) THEN
 15      V_ERROR_CODE:='1005';
 16      V_ERROR_DESC:='Null password given.';
 17           ELSIF (IS_SERVERERROR(1004)) THEN
 18      V_ERROR_CODE:='1004';
 19       V_ERROR_DESC:='Default username/password.';
 20           ELSIF (IS_SERVERERROR(1035)) THEN
 21      V_ERROR_CODE:='1035';
 22       V_ERROR_DESC:='ORACLE only available to users with RESTRICTED SESSION privilege.';
 23           ELSIF (IS_SERVERERROR(1045)) THEN
 24      V_ERROR_CODE:='1045';
 25       V_ERROR_DESC:='User lacks CREATE SESSION privilege.';
 26        ELSE
 27      V_ERROR_CODE:='0000';
 28      V_ERROR_DESC:='Invalid Error.';
 29      END IF;
 30    END;
 31
 32  BEGIN
 33      V_DB_USER:= SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY');
 34      V_HOST_OR_IP:=SYS_CONTEXT('USERENV', 'IP_ADDRESS');
 35      SP_ERROR_TRACE;
 36     IF V_ERROR_CODE IS NOT NULL
 37     THEN
 38       BEGIN
 39        INSERT INTO LOG_FAILED_TRAIL (USER_NAME, ERROR_MESSAGE,HOST_OR_IP)
 40        VALUES (V_DB_USER, V_ERROR_CODE||'-'||V_ERROR_DESC,V_HOST_OR_IP);
 41       EXCEPTION
 42              WHEN OTHERS THEN
 43              NULL;
 44       END;
 45     END IF;
 46  END;
 47  /
Trigger created.
SQL> CONN TEST/TEST;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> SELECT USER_NAME, ERROR_MESSAGE
  2  FROM LOG_FAILED_TRAIL;
USER_NAME  ERROR_MESSAGE
-------------- ----------------------------------------------------------------
TEST    1017-Invalid username/password.
SQL>

DROP and TRUNCATE Restriction in Oracle database.

SQL> CREATE TABLE OBJECT_EVENT_ALLOWED
  2  (OBJECT_NAME VARCHAR2(50),
  3   DROP_ALLOWED CHAR(1),
  4   TRUNCATE_ALLOWED CHAR(1),
  5   DELETE_ALLOWED CHAR(1)
  6   );
Table created.
SQL> CREATE TABLE ORA_EVENT_LOG
  2  (
  3    OBJECT_NAME     VARCHAR2(100 BYTE),
  4    EVENT      VARCHAR2(50 BYTE),
  5    OWNER      VARCHAR2(50 BYTE),
  6    MACHINE    VARCHAR2(100 BYTE) DEFAULT SYS_CONTEXT ('USERENV', 'HOST'),
  7    IPADRES    VARCHAR2(100 BYTE)  DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
  8    TIMESTAMP  DATE DEFAULT SYSDATE
  9  );
Table created.
SQL> CREATE TABLE TEST_TRUNCATE
  2  (
  3    DATA1  VARCHAR2(20 BYTE)
  4  );
Table created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.

SQL> CREATE OR REPLACE TRIGGER DTR_EVENT_CHECK_STORE
  2     BEFORE DROP OR TRUNCATE ON SCHEMA
  3  DECLARE
  4     V_OBJNAME   VARCHAR2 (50);
  5     V_OBJECT_TYPE VARCHAR2(30);
  6     V_EVENT     VARCHAR2 (30);
  7     V_OBJECT_COUNT NUMBER(5);
  8  BEGIN
  9     SELECT ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, ORA_SYSEVENT
 10       INTO V_OBJNAME, V_OBJECT_TYPE, V_EVENT
 11       FROM DUAL;
 12
 13       IF V_EVENT IN ('DROP','TRUNCATE') AND V_OBJECT_TYPE IN ('TABLE') THEN
 14          IF V_EVENT='DROP' THEN
 15              BEGIN
 16                  SELECT COUNT(OBJECT_NAME)
 17                  INTO V_OBJECT_COUNT
 18                  FROM OBJECT_EVENT_ALLOWED
 19                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 20                  AND DROP_ALLOWED='Y';
 21              END;
 22          END IF;
 23
 24          IF V_EVENT='TRUNCATE' THEN
 25              BEGIN
 26                  SELECT COUNT(OBJECT_NAME)
 27                  INTO V_OBJECT_COUNT
 28                  FROM OBJECT_EVENT_ALLOWED
 29                  WHERE UPPER(OBJECT_NAME)=UPPER(V_OBJNAME)
 30                  AND TRUNCATE_ALLOWED='Y';
 31              END;
 32          END IF;
 33
 34           IF V_OBJECT_COUNT=0 THEN
 35                  RAISE_APPLICATION_ERROR(-20099, V_EVENT||' Not allowed in '||V_OBJNAME|| ' '||V_OBJECT_TYPE);
 36             ELSE
 37                  BEGIN
 38                      INSERT INTO ORA_EVENT_LOG( OBJECT_NAME, EVENT, OWNER) VALUES (V_OBJNAME, ORA_SYSEVENT, USER);
 39                    EXCEPTION
 40                          WHEN OTHERS THEN
 41                          NULL;
 42                  END;
 43           END IF;
 44
 45       END IF;
 46
 47  END DTR_EVENT_CHECK_STORE;
 48  /
Trigger created.
SQL> INSERT INTO OBJECT_EVENT_ALLOWED
  2     (OBJECT_NAME, DROP_ALLOWED, TRUNCATE_ALLOWED, DELETE_ALLOWED)
  3   VALUES
  4     ('TEST_TRUNCATE', 'Y', 'Y', 'Y');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
Table truncated.
SQL> DELETE FROM OBJECT_EVENT_ALLOWED;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> TRUNCATE TABLE TEST_TRUNCATE;
TRUNCATE TABLE TEST_TRUNCATE
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TRUNCATE Not allowed in TEST_TRUNCATE TABLE
ORA-06512: at line 33

SQL>

Using Result Cache in Oracle 11g Database.

From Oracle 11g we can use result cache in function and query, these results is a faster response time. The cached results stored become invalid when data in the dependent database objects is changed. Result cache is instance specific. The Result Cache Memory pool consists of the SQL Query Result Cache and PL/SQL Function Result Cache, which stores the values returned by PL/SQL functions.

The RESULT_CACHE_MODE parameter determines the SQL query result cache behavior. This parameter contain MANUAL and FORCE. If you set manual you need to specify result_cache hint in your query. If set FORCE  all results use the cache, you can use no_result_cache hint to bypass the cache.

SQL> SET TIMING ON;
SQL> CREATE OR REPLACE FUNCTION RESULT_CASHE_TEST
  2  RETURN NUMBER
  3  RESULT_CACHE
  4  IS
  5  V_RETVALUE NUMBER:=0;
  6  BEGIN
  7
  8  FOR I IN 1 .. 5 LOOP
  9  DBMS_LOCK.sleep(1);
 10  V_RETVALUE:=V_RETVALUE*I;
 11  END LOOP;
 12
 13  RETURN V_RETVALUE;
 14  END ;
 15  /
Function created.
Elapsed: 00:00:00.01
SQL> SELECT RESULT_CASHE_TEST FROM DUAL;
RESULT_CASHE_TEST
-----------------
                0
Elapsed: 00:00:05.02
SQL> SELECT RESULT_CASHE_TEST FROM DUAL;
RESULT_CASHE_TEST
-----------------
                0
Elapsed: 00:00:00.00
SQL>
SQL> CREATE TABLE RESULT_CACHE( ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);
Table created.
Elapsed: 00:00:00.03
SQL> INSERT INTO RESULT_CACHE VALUES(10,'RAJIB.PRADHAN',5000);
1 row created.
Elapsed: 00:00:00.00
SQL> SELECT /*+ result_cache */  SALARY
  2  FROM RESULT_CACHE
  3  WHERE ID=10;
    SALARY
----------
      5000
Elapsed: 00:00:00.00
SQL>

Export BLOB from database to physical directory.

 I have seen many people are failed to extract image from database to physical directory (in my article comments ) for this reason today I have make this procedure for export blob file.

You can export BLOB file using the following instruction.

1. Create an directory .

CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

2. Create procedure.

CREATE OR REPLACE PROCEDURE SP_EXPORT_IMAGE (P_BLOB_DATA IN BLOB,P_FILE_NAME VARCHAR2,P_DIRECTORY VARCHAR2 )
AS
V_CLOB_DATA CLOB;
V_DATA VARCHAR2(32767);
V_START PLS_INTEGER := 1;
V_END PLS_INTEGER := 32767;
  V_OUTPUT UTL_FILE.FILE_TYPE;
  V_CHNK_SIZE PLS_INTEGER;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_CLOB_DATA, TRUE);

FOR I IN 1..CEIL(DBMS_LOB.GETLENGTH(P_BLOB_DATA) / V_END)
LOOP

   V_DATA := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(P_BLOB_DATA, V_END, V_START));

DBMS_LOB.WRITEAPPEND(V_CLOB_DATA, LENGTH(V_DATA), V_DATA);
V_START := V_START + V_END;
END LOOP;

V_CHNK_SIZE := 3000;

V_OUTPUT := UTL_FILE.FOPEN(P_DIRECTORY, P_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

  FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
  LOOP
    UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
    UTL_FILE.FFLUSH(V_OUTPUT);
  END LOOP;
       
  UTL_FILE.FCLOSE(V_OUTPUT);

END SP_EXPORT_IMAGE;


---------------- Export Blob File in physical directory. ------------------


DECLARE
V_BLOB BLOB;
 V_FILE_NAME VARCHAR2(300);
BEGIN
SELECT IMAGE_FRONT, DATA_NO INTO V_BLOB, V_FILE_NAME
FROM OUTWDCLR_REP
WHERE DATA_NO=77771613;

SP_EXPORT_IMAGE(V_BLOB,V_FILE_NAME||'.JPG','DATA_DIR');
END;

Replace character from Oracle Procedure, Function, Package and Trigger

You can replace character and compile Procedure, Function, Package and Trigger using this block.
----------------------------------------------------------------

DECLARE
  V_CLOB_USER CLOB;
  V_SEARCHING VARCHAR2(300):='FROM_USER';
  V_REPLACE_WITH VARCHAR2(300):='TO_USER';
 BEGIN
       
        FOR INDUSR IN (SELECT DISTINCT NAME, REPLACE (TYPE, 'PACKAGE BODY', 'PACKAGE') TYPE,U.OWNER OWNER_OBJECT
                          FROM DBA_SOURCE U, DBA_OBJECTS O
                         WHERE     O.OBJECT_NAME = U.NAME
                             AND U.OWNER = O.OWNER
                             AND U.OWNER = V_SEARCHING
                               AND TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGES', 'TRIGGER', 'PACKAGE BODY')) LOOP

         BEGIN

         SELECT REPLACE(DBMS_METADATA.GET_DDL(INDUSR.TYPE,INDUSR.NAME,INDUSR.OWNER_OBJECT),V_SEARCHING,V_REPLACE_WITH) INTO V_CLOB_USER FROM DUAL;

         EXECUTE IMMEDIATE V_CLOB_USER;
           EXCEPTION
                    WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(SQLERRM);
          END;

   END LOOP;
END;

Send SMS using GSM Modem from Oracle Database

Send SMS using GSM Modem from Oracle Database

 SMSLib:

SMSLib provides a universal texting API, which can be used for sending and receiving messages via GSM modems.

Pre-requisites:

JDK 1.6 or higher

 JAVA Compiler

Configure SMSLib: You need the following files to configure SMSLib. You can find all of this file in Configuration_Files folder.

Download Files

Click the download link

1.       javax.comm.properties

2.       comm.jar

3.       RXTXcomm.jar

4.       win32com.dll

JAVA_HOME is path where jdk is installed

Step 1:- Copy comm.jar to

·         %JAVA_HOME%/lib  

[ In my case C:\Program Files\ Java\jdk1.7.0_03\lib ]

·         %JAVA_HOME%/jre/lib/ext

[ In my case C:\Program Files\ Java\jdk1.7.0_03\jre\lib\ext ]

Step 2:- Copy win32com.dll to

·         %JAVA_HOME%/bin

[In my case C:\Program Files\ Java\jdk1.7.0_03\bin ]

·         %JAVA_HOME%/jre/bin

      [ In my case C:\Program Files\ Java\jdk1.7.0_03\jre\bin ]

·         %windir%System32

             [ In my case C:\Windows\System32 ]

Step 3 : Copy javax.comm.properties to

·         %JAVA_HOME%/lib

            [ In my case C:\Program Files\ Java\jdk1.7.0_03\lib ]

·         %JAVA_HOME%/jre/lib

            [ In my case C:\Program Files\Java\jdk1.7.0_03\jre\lib ]

Step 4 : Copy RXTXcomm.jar to

·         %JAVA_HOME%/ jre/lib/ext

            [ In my case C:\Program Files\ Java\jdk1.7.0_03\lib ]

1.     Now open your NetBeans IDE

2.     You can find your modem port using CommunicationPortTest class. After finding port and bauds enter your port and bauds in SendSMS class. Please see red color line.

3.     Add the following jar files in your libraries (log4j-1.2.16.jar, ojdbc6.jar, smslib-3.5.1.jar)

4.     Create the following two class (SendSMS, DBCP)

5.     Create one table (SMS_LIST)

6.     Now insert row in SMS_LIST table and run project you can get SMS.

SendSMS Class

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

package smsgetway;

import org.smslib.AGateway;

import org.smslib.AGateway.GatewayStatuses;

import org.smslib.IOutboundMessageNotification;

import org.smslib.OutboundMessage;

import org.smslib.Service;

import org.smslib.modem.SerialModemGateway;

import java.sql.*;

/**

 *

 * @author rajib.pradhan

 */

public class SendSMS extends Thread {

    OutboundNotification outboundNotification;

    StringBuffer sql1;  

    SerialModemGateway gateway;

    String smsGatewayStatus = "";

    GatewayStatuses status;

    int i=0;

    public SendSMS() {

        try {

           

            outboundNotification = new OutboundNotification();

            SerialModemGateway gateway = new SerialModemGateway("modem.COM27", "COM27", 9600, "", "");

            gateway.setInbound(true);

            gateway.setOutbound(true);

            Service.getInstance().setOutboundMessageNotification(outboundNotification);

            Service.getInstance().addGateway(gateway);

            Service.getInstance().startService();

            status = gateway.getStatus();

            smsGatewayStatus = status.toString();

        } catch (Exception e) {

            System.out.println("EXCEPTION gateway.getStatus : "+gateway.getStatus());

            status = gateway.getStatus();

            smsGatewayStatus = status.toString();

            e.printStackTrace();

            System.out.println("Exception e.getMessage: " + e.getMessage());

            System.out.println("Exception cause: " + e.getCause());

        }

    }

    public void sendSMStoMobile() throws Exception {

        if (smsGatewayStatus.equals("STOPPED")) {

            return;

        }

        DBCP dbcp = DBCP.getInstance();

        Connection connection = dbcp.getConnection();

        Statement statement = connection.createStatement();

        ResultSet resultSet = null;

       

        try {

            sql1 = new StringBuffer();

            sql1.append("SELECT MOBILE_NUMBER, MESSAGE, TRANUM ");

            sql1.append("FROM SMSGATEWAY.SMS_LIST ");

            sql1.append("WHERE SEND_STATUS = 'N' ");

            resultSet = statement.executeQuery(sql1.toString());

           

            while (resultSet.next()) {

              

                String destMobileNo = resultSet.getString(1);

                System.out.println("Phone Number "+resultSet.getString(1));

                String message = resultSet.getString(2);

                String tranNumber = resultSet.getString(3);

                OutboundMessage sms = new OutboundMessage(destMobileNo, message);

                Service.getInstance().sendMessage(sms);

                //Update Table After Send Message

                String updateQuery = "UPDATE SMSGATEWAY.SMS_LIST SET SEND_STATUS = 'Y' WHERE TRANUM = " + tranNumber ;

                if(i<20)

                {

               System.out.print(tranNumber+": Y, ");

               

                }

                if(i==20){

                System.out.print(tranNumber+": Y, "+"\n");

                i=0;

                }

                statement.executeUpdate(updateQuery);

                connection.commit();

                i=i+1;

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            if (resultSet != null) {

                resultSet.close();

            }

            if (statement != null) {

                statement.close();

            }

            dbcp.releaseConnection(connection);

        }

    }

    public class OutboundNotification implements IOutboundMessageNotification {

        public void process(AGateway gateway, OutboundMessage msg) {

            System.out.println(msg);

        }

    }

    public void run() {

        while (true) {

            try {

                sendSMStoMobile();

                this.sleep(1000);

            } catch (Exception e) {

            }

        }

    }

    public static void main(String[] args) throws Exception {

        SendSMS sendSMSFromDB = new SendSMS();

        sendSMSFromDB.start();

    }

    private void getModemInformation(SerialModemGateway gateway) throws Exception{

        System.out.println();

        System.out.println("Modem Information:");

       

        System.out.println("  Model: " + gateway.getModel());

        System.out.println("  Serial No: " + gateway.getSerialNo());

        System.out.println("  SIM IMSI: " + gateway.getImsi());

        System.out.println("  Signal Level: " + gateway.getSignalLevel() + " dBm");

        System.out.println("  Battery Level: " + gateway.getBatteryLevel() + "%");

        System.out.println("  Manufacturer: " + gateway.getManufacturer());

        System.out.println();

    }

}

Database Connection (DBCP) Class

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

package smsgetway;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Vector;

import java.util.Stack;

/**

 *

 * @author rajib.pradhan

 */

/*

 *

 * Database Connection Information

 *

 */

public class DBCP implements Runnable {

    private static DBCP connectionPool;

    private Stack pool;

    private Vector busyConnections;

    private int MAX_CONNECTIONS = 10000;

    private int MIN_CONNECTIONS = 1;

    private long timeout;

    private String sDriver;

    private String sDBUrl;

    private String sUsername;

    private String sPassword;

    private DBCP(String sDriver,

                 String sUrl,

                 String sUserName,

                 String sPassword,

                 int sMaxConnection,

                 int sMinConnection,

                 long sTimeOut) throws SQLException {

       

        this.timeout = sTimeOut;

        this.sDriver = sDriver;

        this.sDBUrl = sUrl;

        this.sUsername = sUserName;

        this.sPassword = sPassword;

        this.MAX_CONNECTIONS = sMaxConnection;

        this.MIN_CONNECTIONS = sMinConnection;

        busyConnections = new Vector();

        pool = new Stack();

        for (int i = 0; i < MIN_CONNECTIONS; i++) {

            pool.push(makeNewConnection());

        }

    }

    public static DBCP getInstance() {

        if (connectionPool == null) {

            try {

                connectionPool =

                        new DBCP(

                        "oracle.jdbc.OracleDriver",

                       "jdbc:oracle:thin:@localhost:1521:ORCL",

                        "SMSGATEWAY",

                        "SMSGATEWAY",

                        10,

                        5,

                        30000);

            } catch (SQLException _sqlex) {

                _sqlex.printStackTrace();

            }

        }

        return connectionPool;

    }

    public synchronized Connection getConnection() throws SQLException, InterruptedException {

        Connection connection = null;

        if (connectionPool != null) {

            if (pool.size() != 0) {

                connection = (Connection) pool.pop();

                busyConnections.add(connection);

            } else {

                if (getTotalConnections() >= MAX_CONNECTIONS) {

                    while (busyConnections.size() < MAX_CONNECTIONS) {

                        connection = (Connection) pool.pop();

                        if (connection != null) {

                            connection.close();

                        }

                    }

                } else {

                    makeBackgroundConnection();

                    wait();

                    connection = getConnection();

                }

            }

        }

        return connection;

    }

    protected int getTotalConnections() {

        return pool.size() + busyConnections.size();

    }

    private void makeBackgroundConnection() throws SQLException {

        try {

            Thread connectionThread = new Thread(this);

            connectionThread.start();

        } catch (Exception _ex) {

            throw new SQLException("Max Limit of connections exceeded");

        }

    }

    private synchronized Connection makeNewConnection() throws SQLException {

        try {

            Class.forName(sDriver);

            Connection connection =

                    DriverManager.getConnection(sDBUrl, sUsername, sPassword);

            connection.setAutoCommit(false);

            return connection;

        } catch (ClassNotFoundException cnfe) {

            throw new SQLException("Can't find class for driver: " + sDriver);

        }

    }

    /**

     * run() function for making a new connection from the

     * backgroud called by makeBackgroundConnection()

     */

    public void run() {

        synchronized (this) {

            try {

                Connection con = makeNewConnection();

                pool.push(con);

                notifyAll();

            } catch (Exception _ex) {

            }

        }

    }

    /**

     *

     * @return Information about this connection pool

     */

    public synchronized void releaseConnection(Connection con) {

        pool.push(con);

        busyConnections.remove(con);

        notifyAll();

    }

    public synchronized String toString() {

        String info =

                "ConnectionPool("

                + sDBUrl

                + ","

                + sUsername

                + ")"

                + ", available="

                + pool.size()

                + ", busy="

                + busyConnections.size()

                + ", max="

                + MAX_CONNECTIONS;

        return info;

    }

    /**

     *

     * @throws SQLException

     */

    private synchronized void closeAllConnections() throws SQLException {

        while (!pool.isEmpty()) {

            try {

                ((Connection) pool.pop()).close();

            } catch (Exception _ex) {

                throw new SQLException("Unable to close the Connection");

            }

        }

        pool = new Stack();

        for (int i = 0; i < busyConnections.size(); i++) {

            try {

                ((Connection) busyConnections.get(i)).close();

                busyConnections.remove(i);

            } catch (Exception _ex) {

                throw new SQLException("Unable to close the Connection");

            }

        }

        busyConnections = new Vector();

    }

    /**

     *

     * @throws java.lang.Throwable

     */

    protected void finalize() throws java.lang.Throwable {

        try {

            closeAllConnections();

        } catch (Exception _ex) {

        }

        super.finalize();

    }

}

Port finding class (CommunicationPortTest.class)

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

package smsgetway;

import java.io.InputStream;

import java.io.OutputStream;

import java.util.Enumeration;

import java.util.Formatter;

import javax.swing.JDialog;

import javax.swing.JOptionPane;

import org.smslib.helper.CommPortIdentifier;

import org.smslib.helper.SerialPort;

/**

 *

 * @author rajib.pradhan

 */

public class CommunicationPortTest {

    private static final String _NO_DEVICE_FOUND = "No Device Found.";

    private final static Formatter _formatter = new Formatter(System.out);

    static CommPortIdentifier portId;

    static Enumeration<CommPortIdentifier> portList;

    static int bauds[] = {9600, 14400, 19200, 28800, 33600, 38400, 56000, 57600, 115200};

    /**

     * Wrapper around {@link CommPortIdentifier#getPortIdentifiers()} to be

     * avoid unchecked warnings.

     */

    private static Enumeration<CommPortIdentifier> getCleanPortIdentifiers() {

        return CommPortIdentifier.getPortIdentifiers();

    }

    public static void main(String[] args) {

        System.out.println("\nSearching for devices...");

        portList = getCleanPortIdentifiers();

        while (portList.hasMoreElements()) {

            portId = portList.nextElement();

            if (portId.getPortType() == CommPortIdentifier.PORT_SERIAL) {

                _formatter.format("%nFound port: %-5s%n", portId.getName());

                for (int i = 0; i < bauds.length; i++) {

                    SerialPort serialPort = null;

                    _formatter.format("       Trying at %6d...", bauds[i]);

                    try {

                        InputStream inStream;

                        OutputStream outStream;

                        int c;

                        String response;

                        //serialPort = portId.open("SMSLibCommTester", 1971);

                        serialPort = portId.open("SMSAPP", 1971);

                        serialPort.setFlowControlMode(SerialPort.FLOWCONTROL_RTSCTS_IN);

                        serialPort.setSerialPortParams(bauds[i], SerialPort.DATABITS_8, SerialPort.STOPBITS_1, SerialPort.PARITY_NONE);

                        inStream = serialPort.getInputStream();

                        outStream = serialPort.getOutputStream();

                        serialPort.enableReceiveTimeout(1000);

                        c = inStream.read();

                        while (c != -1) {

                            c = inStream.read();

                        }

                        outStream.write('A');

                        outStream.write('T');

                        outStream.write('\r');

                        Thread.sleep(1000);

                        response = "";

                        StringBuilder sb = new StringBuilder();

                        c = inStream.read();

                        while (c != -1) {

                            sb.append((char) c);

                            c = inStream.read();

                        }

                        response = sb.toString();

                        if (response.indexOf("OK") >= 0) {

                            try {

                                System.out.print("  Getting Info...");

                                outStream.write('A');

                                outStream.write('T');

                                outStream.write('+');

                                outStream.write('C');

                                outStream.write('G');

                                outStream.write('M');

                                outStream.write('M');

                                outStream.write('\r');

                                response = "";

                                c = inStream.read();

                                while (c != -1) {

                                    response += (char) c;

                                    c = inStream.read();

                                }

                                System.out.println(" Found: " + response.replaceAll("\\s+OK\\s+", "").replaceAll("\n", "").replaceAll("\r", ""));

//                                JOptionPane.showMessageDialog(null," Found: " + response.replaceAll("\\s+OK\\s+", "").replaceAll("\n", "").replaceAll("\r", ""), "Warning",

//                                JOptionPane.WARNING_MESSAGE);

                            } catch (Exception e) {

                                System.out.println(_NO_DEVICE_FOUND);

//                                JOptionPane.showMessageDialog(null, _NO_DEVICE_FOUND, "Warning",

//                                JOptionPane.WARNING_MESSAGE);

                            }

                        } else {

                            System.out.println(_NO_DEVICE_FOUND);

//                            JOptionPane.showMessageDialog(null, _NO_DEVICE_FOUND, "Warning",

//                                JOptionPane.WARNING_MESSAGE);

                        }

                    } catch (Exception e) {

                        System.out.print(_NO_DEVICE_FOUND);

//                        JOptionPane.showMessageDialog(null, _NO_DEVICE_FOUND, "Warning",

//                                JOptionPane.WARNING_MESSAGE);

                        Throwable cause = e;

                        while (cause.getCause() != null) {

                            cause = cause.getCause();

                        }

                        System.out.println(" (" + cause.getMessage() + ")");

                    } finally {

                        if (serialPort != null) {

                            serialPort.close();

                        }

                    }

                }

            }

        }

        System.out.println("\nCommunication Test Completed.");

//        JOptionPane.showMessageDialog(null, "Communication Test Completed.", "Information",

//                                JOptionPane.INFORMATION_MESSAGE);

    }

}

Table Scripts

CREATE TABLE SMS_LIST

(

  MOBILE_NUMBER  VARCHAR2(20 BYTE),

  MESSAGE        VARCHAR2(300 BYTE),

  SEND_STATUS    CHAR(1 BYTE)                   DEFAULT 'N',

  TRANUM         VARCHAR2(30 BYTE)

)