Split String and Get as Table Type in Oracle

CREATE OR REPLACE FUNCTION SPLIT_STRING(P_LIST VARCHAR2,
                                        P_DEL  VARCHAR2 DEFAULT ',')
  RETURN SPLIT_TBL
  PIPELINED IS
  /*********************************************************
  This Function will split the string and return as table type.
  Created by :- Ajit Kumar Dubey
  Date :- 12-Sep-2014
  ----------------------------------------------------------
  Create a type as given below for this function.
 
  create or replace type split_tbl as table of varchar2(32767);
  *********************************************************/
  L_IDX   PLS_INTEGER;
  L_LIST  VARCHAR2(32767) := P_LIST;
  L_VALUE VARCHAR2(32767);
BEGIN
  LOOP
    L_IDX := INSTR(L_LIST, P_DEL);
    IF L_IDX > 0 THEN
      PIPE ROW(SUBSTR(L_LIST, 1, L_IDX - 1));
      L_LIST := SUBSTR(L_LIST, L_IDX + LENGTH(P_DEL));
   
    ELSE
      PIPE ROW(L_LIST);
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END SPLIT_STRING;

add to del.icio.us saved by 0 users

Extract Merge or Insert Script of a table

CREATE OR REPLACE PROCEDURE PROC_GEN_MERGE_SCRIPT(P_TABLENAME    VARCHAR2,
                                                  P_WHERE_CLAUSE VARCHAR2 DEFAULT '1=1') AS
  X_CNT_CLOB           NUMBER;
  X_INSERT_COLUMN_LIST VARCHAR2(10000);
  IS_PRIMARY_KEY_THERE PLS_INTEGER := 1; -- 1 Stand for Yes and 0 for No
  /*
  ----------------------------------------
  Create By : Ajit Kumar Dubey
  Version : ß (Beta)
  Creation Date : 18 Sep 2014
  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                     @@@@@@@@@@@@@@@@@
  There are two procedure in this Proc
  GEN_MERGE_WITH_COB_COL
  GEN_MERGE_WITHOUT_CLOB
 
  Table Needed for this Proc is
                                 create table SEEDED_MERGE_SCRIPT
                                 (
                                 TABLE_NAME VARCHAR2(30),
                                 SCRIPT     CLOB
                                 );
                       @@@@@@@@@@@@@@@@@
  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
  ----------------------------------------
  */

  /*
  ####################################################
                      *****************************
                            *************
  This Procedure will be used when there is/are clob column(s) in the table;
                            *************
                      *****************************
  #####################################################
  */

  PROCEDURE GEN_MERGE_WITH_COB_COL(P_TABLENAME    VARCHAR2,
                                   P_WHERE_CLAUSE VARCHAR2) AS
    L_TABLENAME    VARCHAR2(30) := NULL;
    L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
    L_TABLENAME1   CLOB;
 
    ERRPOS NUMBER(5);
 
    -- Cursors defined here -----
    CURSOR CUR_CONSTRAINT_COLS IS
      SELECT COLUMN_NAME
        FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
       WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
         AND UC.CONSTRAINT_TYPE = 'P'
         AND UCC.TABLE_NAME = L_TABLENAME
       ORDER BY POSITION;
 
    CURSOR CUR_COLUMNS IS
      SELECT UT.COLUMN_NAME,
             NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
             SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
        FROM USER_TAB_COLUMNS UT,
             (SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
                FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
               WHERE UCC.TABLE_NAME = L_TABLENAME
                 AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                 AND UC.CONSTRAINT_TYPE = 'P') UCCC
       WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
         AND UT.TABLE_NAME = L_TABLENAME
       ORDER BY COLUMN_ID;
 
    CUR_DATA_SET  SYS_REFCURSOR;
    CUR_DATA_SET1 SYS_REFCURSOR;
    --- Cursor Definition Ends Here -----
 
    RES_DATA_SET         CLOB;
    L_DATA_SET           CLOB := NULL;
    L_SQL_STMT           CLOB := NULL;
    L_SQL_STMT1          CLOB := NULL;
    L_DATA_PREP_SQL_STR  CLOB := NULL;
    L_DATA_PREP_SQL_STR1 CLOB := NULL;
    L_COL_LIST           CLOB := NULL;
    L_JOIN_COND          CLOB := NULL;
    L_UPD_STR            CLOB := NULL;
    L_INSRT_VALUES       CLOB := NULL;
    L_TABLE_EXIST_IND    NUMBER(1) := 0;
    L_COL_FORMAT         CLOB := NULL;
    NO_PRIMARY_KEY EXCEPTION;
    TABLE_DOES_NOT_EXIST EXCEPTION;
    ----------------------------------------
    X_CLOB_INDEX NUMBER := 0;
 
    TYPE STORE_RELATION_TAB IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
    X_STORE_RELATION_TAB STORE_RELATION_TAB;
    X_MODEL_CLOB_OUT     CLOB;
 
    X_CLOB_COLUMN_LIST    VARCHAR2(4000);
    X_PK_COLUMN_STRING    VARCHAR2(10000);
    X_PK_COLUMN_WITH_VALS VARCHAR2(10000);
    X_ASSIGN_NULL_TO_CLOB VARCHAR2(10000);
    X_ROWID               VARCHAR2(100);
 
  BEGIN
    L_TABLENAME := UPPER(P_TABLENAME);
 
    BEGIN
      SELECT COUNT(1)
        INTO L_TABLE_EXIST_IND
        FROM USER_TABLES
       WHERE TABLE_NAME = L_TABLENAME;
   
      IF L_TABLE_EXIST_IND = 0 THEN
        RAISE TABLE_DOES_NOT_EXIST;
      END IF;
   
    END;
 
    BEGIN
      FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
        L_JOIN_COND := L_JOIN_COND || 'T.' ||
                       RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
                       RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
      END LOOP;
      L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);
   
      IF L_JOIN_COND IS NULL THEN
        --RAISE NO_PRIMARY_KEY;
        IS_PRIMARY_KEY_THERE := 0;
      END IF;
    END;
    --$#$#$#-----------------------------------------------
    FOR RES_COLUMNS IN CUR_COLUMNS LOOP
   
      IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
        L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
                        'to_char(' || RES_COLUMNS.COLUMN_NAME ||
                        ',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
                        '''''''''' || '||' || ''',''' || '||' ||
                        '''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';
     
      ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN
     
        L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
                        'to_char(' || RES_COLUMNS.COLUMN_NAME ||
                        ',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
                        ''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
      ELSIF RES_COLUMNS.DATATYPE = 'CLOB' THEN
     
        X_CLOB_INDEX := X_CLOB_INDEX + 1;
        X_STORE_RELATION_TAB(X_CLOB_INDEX) := RES_COLUMNS.COLUMN_NAME;
        L_COL_FORMAT := q'~ q'#X_CLOB_VAR(~' || X_CLOB_INDEX || ')#'''; /*'*/
        X_CLOB_COLUMN_LIST := CASE WHEN X_CLOB_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_CLOB_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
      ELSE
        L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
                        RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
                        ',''''''''''''' || ')' || '||' || '''''''''';
      END IF;
      L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
                              ''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
                              ''',''' || '||';
      L_COL_LIST           := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
      IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
        L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
                     RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || '     ';
      END IF;
   
      ---PK Columns match and populate_its value
      IF INSTR(L_JOIN_COND, '.' || RES_COLUMNS.COLUMN_NAME) > 0 THEN
        X_PK_COLUMN_STRING := CASE WHEN X_PK_COLUMN_STRING IS NULL THEN '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT ELSE X_PK_COLUMN_STRING || '||'' AND ''||' || '''' || RES_COLUMNS.COLUMN_NAME || '''' || '||''=''||' || L_COL_FORMAT END;
      END IF;
   
      L_INSRT_VALUES       := L_INSRT_VALUES || 'S.' ||
                              RES_COLUMNS.COLUMN_NAME || ',';
      X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
    END LOOP;
    --$#$#$#-----------------------------------------------
    ERRPOS := 1;
    SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
      INTO L_UPD_STR
      FROM DUAL;
    L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
                                   1,
                                   LENGTH(L_DATA_PREP_SQL_STR1) - 7);
    L_COL_LIST           := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
    L_UPD_STR            := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
    L_INSRT_VALUES       := SUBSTR(L_INSRT_VALUES,
                                   1,
                                   LENGTH(L_INSRT_VALUES) - 1);
    --DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
    L_TABLENAME1 := L_TABLENAME;
    IF IS_PRIMARY_KEY_THERE = 0 THEN
      X_PK_COLUMN_STRING := '''1=1''';
    END IF;
    L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' ,' ||
                           X_PK_COLUMN_STRING || ', ROWID' || ' FROM ' ||
                           L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
    --INSERT INTO DROP_MSG_LOG VALUES (L_DATA_PREP_SQL_STR, SYSDATE);
    --COMMIT;
    ERRPOS := 2;
    --~$~#~$~#---------------------------------------
    OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
    LOOP
      FETCH CUR_DATA_SET
        INTO RES_DATA_SET, X_PK_COLUMN_WITH_VALS, X_ROWID;
      EXIT WHEN CUR_DATA_SET%NOTFOUND;
      X_MODEL_CLOB_OUT := NULL;
      --$$##--------------------------------------------
      DECLARE
        X_UNION_STMT VARCHAR2(20000);
        X_CLB_VALUE  CLOB;
        X_INDEX      NUMBER;
     
      BEGIN
        X_ASSIGN_NULL_TO_CLOB := NULL;
        FOR I_INDX IN 1 .. X_STORE_RELATION_TAB.COUNT LOOP
          -- X_UNION_STMT          := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ' || X_PK_COLUMN_WITH_VALS END;
          X_UNION_STMT          := CASE WHEN X_UNION_STMT IS NULL THEN 'SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ' COL1,' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' ELSE X_UNION_STMT || 'UNION ALL SELECT ' || X_STORE_RELATION_TAB(I_INDX) || ',' || I_INDX || ' as RNM FROM ' || L_TABLENAME1 || ' WHERE ROWID =''' || X_ROWID || '''' END;
          X_ASSIGN_NULL_TO_CLOB := CASE WHEN X_ASSIGN_NULL_TO_CLOB IS NULL THEN 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' ELSE X_ASSIGN_NULL_TO_CLOB || CHR(10) || 'X_CLOB_VAR(' || I_INDX || ') := NULL; ' END;
        END LOOP;
        X_UNION_STMT := 'Select * from (' || X_UNION_STMT ||
                        ') order by RNM ASC';
     
        OPEN CUR_DATA_SET1 FOR X_UNION_STMT;
        LOOP
          FETCH CUR_DATA_SET1
            INTO X_CLB_VALUE, X_INDEX;
          EXIT WHEN CUR_DATA_SET1%NOTFOUND;
          --##------------------------------------
          DECLARE
            X_STMT           CLOB;
            X_MODEL_QUERY    CLOB;
            X_LENGTH         NUMBER := 0;
            X_START_POSITION NUMBER := 1;
            X_END_POSITION   NUMBER;
            X_EXIT_LOOP      NUMBER := 0;
          BEGIN
         
            X_MODEL_QUERY := X_CLB_VALUE;
            X_LENGTH      := DBMS_LOB.GETLENGTH(X_MODEL_QUERY);
            WHILE X_START_POSITION < X_LENGTH LOOP
              X_END_POSITION := CASE WHEN X_START_POSITION + 1600 > X_LENGTH THEN X_LENGTH ELSE X_START_POSITION + 1600 END;
              -- DBMS_OUTPUT.PUT_LINE('End Postion :' || X_END_POSITION);
              X_STMT           := X_STMT || CHR(10) || 'X_CLOB_VAR(' ||
                                  X_INDEX || ') := X_CLOB_VAR(' || X_INDEX ||
                                  ')|| q''^' ||
                                  DBMS_LOB.SUBSTR(X_MODEL_QUERY,
                                                  1600,
                                                  X_START_POSITION) ||
                                  '^'';';
              X_START_POSITION := X_END_POSITION;
              --DBMS_OUTPUT.PUT_LINE('Start Position :' || X_START_POSITION);
              X_EXIT_LOOP := X_EXIT_LOOP + 1;
              --DBMS_OUTPUT.PUT_LINE('Loop :' || X_EXIT_LOOP);
              EXIT WHEN X_EXIT_LOOP > 5;
            END LOOP;
            --X_MODEL_CLOB_OUT := NULL;
            X_MODEL_CLOB_OUT := X_MODEL_CLOB_OUT || CHR(10) || X_STMT;
            --EXIT;
          END;
          --##------------------------------------
        END LOOP;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM ||
                               DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
          NULL;
      END;
      --$$##--------------------------------------------
      ---End fetch clob
      L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);
   
      SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
        INTO L_DATA_SET
        FROM DUAL;
      --L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
      ERRPOS := 3;
      IF IS_PRIMARY_KEY_THERE = 1 THEN
        X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
                            'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
                            CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
                            ' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
                            CHR(10) || X_MODEL_CLOB_OUT;
        L_SQL_STMT       := X_MODEL_CLOB_OUT || CHR(10) || 'MERGE INTO ' ||
                            L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
                            CHR(10) || L_DATA_SET || CHR(10) || ' ) S' ||
                            CHR(10) || 'ON (' || L_JOIN_COND || ')' ||
                            CHR(10) || 'WHEN MATCHED THEN ' || CHR(10) ||
                            ' UPDATE ' || CHR(10) || ' SET ' || L_UPD_STR ||
                            CHR(10) || 'WHEN NOT MATCHED THEN ' || CHR(10) ||
                            ' INSERT ' || CHR(10) || '  (' || L_COL_LIST || ')' ||
                            CHR(10) || ' VALUES' || CHR(10) || '  (' ||
                            L_INSRT_VALUES || ' );' || CHR(10) || 'END;' ||
                            CHR(10) || '/';
        L_SQL_STMT1      := L_SQL_STMT1 || L_SQL_STMT;
      ELSE
        X_MODEL_CLOB_OUT := CHR(10) || 'DECLARE' || CHR(10) ||
                            'TYPE CLOB_TAB is TABLE of CLOB INDEX by PLS_INTEGER;' ||
                            CHR(10) || 'X_CLOB_VAR CLOB_TAB;' || CHR(10) ||
                            ' BEGIN ' || CHR(10) || X_ASSIGN_NULL_TO_CLOB ||
                            CHR(10) || X_MODEL_CLOB_OUT;
        L_SQL_STMT       := X_MODEL_CLOB_OUT || CHR(10) || CHR(10) ||
                            'INSERT INTO ' || L_TABLENAME1 || ' (' ||
                            X_INSERT_COLUMN_LIST || ') ' || CHR(10) ||
                            L_DATA_SET || ' ;' || CHR(10) ||
                            'EXCEPTION WHEN OTHERS THEN NULL;' || CHR(10) ||
                            'END;' || CHR(10) || '/';
        L_SQL_STMT1      := L_SQL_STMT1 || L_SQL_STMT;
      END IF;
    END LOOP;
    L_SQL_STMT1 := L_SQL_STMT1 || CHR(10) || 'COMMIT' || CHR(10) || '/';
    --~$~#~$~#---------------------------------------
    --DBMS_OUTPUT.put_line(l_sql_stmt);
    DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
    INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT1);
 
    ERRPOS := 4;
  EXCEPTION
    WHEN NO_PRIMARY_KEY THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'No Primary Key found for the Table' ||
                              L_TABLENAME);
   
    WHEN TABLE_DOES_NOT_EXIST THEN
      RAISE_APPLICATION_ERROR(-20002,
                              'The table does not exist' || L_TABLENAME);
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Reached :' || ERRPOS || ' --- ' || SQLERRM ||
                           DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
      /*RAISE_APPLICATION_ERROR(-20003,
      'Some Error - ' || L_TABLENAME || ' - ' ||
      ERRPOS);*/
 
  END GEN_MERGE_WITH_COB_COL;
  /*
  ##########################################
                      *****************************
                            *************
  This Procedure will be used when there is no clob column in the table;
                            *************
                      *****************************
  #########################################
  */
  PROCEDURE GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME    VARCHAR2,
                                       P_WHERE_CLAUSE VARCHAR2) AS
 
    L_TABLENAME    VARCHAR2(30) := NULL;
    L_WHERE_CLAUSE CLOB := P_WHERE_CLAUSE;
    L_TABLENAME1   CLOB;
 
    ERRPOS NUMBER(5);
 
    -- Cursors defined here -----
    CURSOR CUR_CONSTRAINT_COLS IS
      SELECT COLUMN_NAME
        FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
       WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
         AND UC.CONSTRAINT_TYPE = 'P'
         AND UCC.TABLE_NAME = L_TABLENAME
       ORDER BY POSITION;
 
    CURSOR CUR_COLUMNS IS
      SELECT UT.COLUMN_NAME,
             NVL(UCCC.CONSTRAINT_TYPE, 'X') CONSTRAINT_TYPE,
             SUBSTR(UT.DATA_TYPE, 1, 9) DATATYPE
        FROM USER_TAB_COLUMNS UT,
             (SELECT UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
                FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC
               WHERE UCC.TABLE_NAME = L_TABLENAME
                 AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                 AND UC.CONSTRAINT_TYPE = 'P') UCCC
       WHERE UT.COLUMN_NAME = UCCC.COLUMN_NAME(+)
         AND UT.TABLE_NAME = L_TABLENAME
       ORDER BY COLUMN_ID;
 
    CUR_DATA_SET SYS_REFCURSOR;
    --- Cursor Definition Ends Here -----
 
    RES_DATA_SET         CLOB;
    L_DATA_SET           CLOB := NULL;
    L_SQL_STMT           CLOB := NULL;
    L_DATA_PREP_SQL_STR  CLOB := NULL;
    L_DATA_PREP_SQL_STR1 CLOB := NULL;
    L_COL_LIST           CLOB := NULL;
    L_JOIN_COND          CLOB := NULL;
    L_UPD_STR            CLOB := NULL;
    L_INSRT_VALUES       CLOB := NULL;
    L_TABLE_EXIST_IND    NUMBER(1) := 0;
    L_COL_FORMAT         CLOB := NULL;
    NO_PRIMARY_KEY EXCEPTION;
    TABLE_DOES_NOT_EXIST EXCEPTION;
 
  BEGIN
    L_TABLENAME := UPPER(P_TABLENAME);
 
    BEGIN
      SELECT COUNT(1)
        INTO L_TABLE_EXIST_IND
        FROM USER_TABLES
       WHERE TABLE_NAME = L_TABLENAME;
   
      IF L_TABLE_EXIST_IND = 0 THEN
        RAISE TABLE_DOES_NOT_EXIST;
      END IF;
   
    END;
 
    BEGIN
      FOR RES_CONSTRAINT_COLS IN CUR_CONSTRAINT_COLS LOOP
        L_JOIN_COND := L_JOIN_COND || 'T.' ||
                       RES_CONSTRAINT_COLS.COLUMN_NAME || ' = ' || 'S.' ||
                       RES_CONSTRAINT_COLS.COLUMN_NAME || ' AND ';
      END LOOP;
      L_JOIN_COND := SUBSTR(L_JOIN_COND, 1, LENGTH(L_JOIN_COND) - 5);
   
      IF L_JOIN_COND IS NULL THEN
        --RAISE NO_PRIMARY_KEY;
        IS_PRIMARY_KEY_THERE := 0;
      END IF;
    END;
 
    FOR RES_COLUMNS IN CUR_COLUMNS LOOP
   
      IF RES_COLUMNS.DATATYPE = 'TIMESTAMP' THEN
        L_COL_FORMAT := '''to_timestamp(''' || '||' || '''''''''' || '||' ||
                        'to_char(' || RES_COLUMNS.COLUMN_NAME ||
                        ',''dd-mm-yyyy hh24:mi:ss.ff'')' || '||' ||
                        '''''''''' || '||' || ''',''' || '||' ||
                        '''''''dd-mm-yyyy hh24:mi:ss.ff''' || ''')''';
     
      ELSIF RES_COLUMNS.DATATYPE = 'DATE' THEN
     
        L_COL_FORMAT := '''to_date(''' || '||' || '''''''''' || '||' ||
                        'to_char(' || RES_COLUMNS.COLUMN_NAME ||
                        ',''dd-mm-yyyy'')' || '||' || '''''''''' || '||' ||
                        ''',''' || '||' || '''''''dd-mm-yyyy''' || ''')''';
      ELSE
        L_COL_FORMAT := '''''''''' || '||' || 'Replace(' ||
                        RES_COLUMNS.COLUMN_NAME || ',''''''''' ||
                        ',''''''''''''' || ')' || '||' || '''''''''';
      END IF;
      L_DATA_PREP_SQL_STR1 := L_DATA_PREP_SQL_STR1 || L_COL_FORMAT || '||' ||
                              ''' ' || RES_COLUMNS.COLUMN_NAME || '''' || '||' ||
                              ''',''' || '||';
      L_COL_LIST           := L_COL_LIST || RES_COLUMNS.COLUMN_NAME || ',';
      IF RES_COLUMNS.CONSTRAINT_TYPE <> 'P' THEN
        L_UPD_STR := L_UPD_STR || 'T.' || RES_COLUMNS.COLUMN_NAME || ' = ' || 'S.' ||
                     RES_COLUMNS.COLUMN_NAME || ',' || CHR(10) || '     ';
      END IF;
   
      L_INSRT_VALUES       := L_INSRT_VALUES || 'S.' ||
                              RES_COLUMNS.COLUMN_NAME || ',';
      X_INSERT_COLUMN_LIST := CASE WHEN X_INSERT_COLUMN_LIST IS NULL THEN RES_COLUMNS.COLUMN_NAME ELSE X_INSERT_COLUMN_LIST || ',' || RES_COLUMNS.COLUMN_NAME END;
    END LOOP;
    ERRPOS := 1;
    SELECT REGEXP_REPLACE(L_UPD_STR, CHR(10), '', LENGTH(L_UPD_STR) - 7)
      INTO L_UPD_STR
      FROM DUAL;
    L_DATA_PREP_SQL_STR1 := SUBSTR(L_DATA_PREP_SQL_STR1,
                                   1,
                                   LENGTH(L_DATA_PREP_SQL_STR1) - 7);
    L_COL_LIST           := SUBSTR(L_COL_LIST, 1, LENGTH(L_COL_LIST) - 1);
    L_UPD_STR            := SUBSTR(L_UPD_STR, 1, LENGTH(L_UPD_STR) - 6);
    L_INSRT_VALUES       := SUBSTR(L_INSRT_VALUES,
                                   1,
                                   LENGTH(L_INSRT_VALUES) - 1);
    DBMS_OUTPUT.PUT_LINE(L_DATA_PREP_SQL_STR1);
    L_TABLENAME1        := L_TABLENAME;
    L_DATA_PREP_SQL_STR := 'SELECT ' || L_DATA_PREP_SQL_STR1 || ' FROM ' ||
                           L_TABLENAME1 || ' where ' || L_WHERE_CLAUSE;
    ERRPOS              := 2;
    OPEN CUR_DATA_SET FOR L_DATA_PREP_SQL_STR;
    LOOP
      FETCH CUR_DATA_SET
        INTO RES_DATA_SET;
      EXIT WHEN CUR_DATA_SET%NOTFOUND;
      L_DATA_SET := ' SELECT ' || RES_DATA_SET || ' FROM DUAL' || CHR(10);
   
      SELECT REGEXP_REPLACE(L_DATA_SET, CHR(10), '', LENGTH(L_DATA_SET) - 1)
        INTO L_DATA_SET
        FROM DUAL;
      --L_DATA_SET := SUBSTR(L_DATA_SET, 1, LENGTH(L_DATA_SET) - 8);
      ERRPOS := 3;
      IF IS_PRIMARY_KEY_THERE = 1 THEN
        L_SQL_STMT := L_SQL_STMT || CHR(10) || 'MERGE INTO ' ||
                      L_TABLENAME1 || ' T ' || CHR(10) || 'USING ( ' ||
                      CHR(10) || L_DATA_SET || CHR(10) || ' ) S' || CHR(10) ||
                      'ON (' || L_JOIN_COND || ')' || CHR(10) ||
                      'WHEN MATCHED THEN ' || CHR(10) || ' UPDATE ' ||
                      CHR(10) || ' SET ' || L_UPD_STR || CHR(10) ||
                      'WHEN NOT MATCHED THEN ' || CHR(10) || ' INSERT ' ||
                      CHR(10) || '  (' || L_COL_LIST || ')' || CHR(10) ||
                      ' VALUES' || CHR(10) || '  (' || L_INSRT_VALUES || ' )' ||
                      CHR(10) || '/';
      ELSE
        L_SQL_STMT := L_SQL_STMT || CHR(10) || 'BEGIN ' || CHR(10) ||
                      'INSERT INTO ' || L_TABLENAME1 || ' (' ||
                      X_INSERT_COLUMN_LIST || ') ' || CHR(10) || L_DATA_SET || ' ;' ||
                      CHR(10) || 'EXCEPTION WHEN OTHERS THEN NULL;' ||
                      CHR(10) || 'END;' || CHR(10) || '/';
      END IF;
    END LOOP;
    L_SQL_STMT := L_SQL_STMT || CHR(10) || 'COMMIT' || CHR(10) || '/';
    --DBMS_OUTPUT.put_line(l_sql_stmt);
    DELETE FROM SEEDED_MERGE_SCRIPT WHERE TABLE_NAME = L_TABLENAME;
    INSERT INTO SEEDED_MERGE_SCRIPT VALUES (L_TABLENAME, L_SQL_STMT);
 
    ERRPOS := 4;
  EXCEPTION
    WHEN NO_PRIMARY_KEY THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'No Primary Key found for the Table' ||
                              L_TABLENAME);
   
    WHEN TABLE_DOES_NOT_EXIST THEN
      RAISE_APPLICATION_ERROR(-20002,
                              'The table does not exist' || L_TABLENAME);
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20003,
                              'Some Error - ' || L_TABLENAME || ' - ' ||
                              ERRPOS);
   
  END GEN_MERGE_WITHOUT_CLOB_COL;
  /*
  ##########################################
                      *****************************
                            *************
  The below block is the wrapper of the procedures.
  This block will decide which Procedure to call.
                            *************
                      *****************************
  ############################################
  */
BEGIN
  EXECUTE IMMEDIATE 'Select Count(1) from USER_TAB_COLUMNS where Table_name = TRIM(Upper(''' ||
                    P_TABLENAME || ''')) and DATA_TYPE=''CLOB'''
    INTO X_CNT_CLOB;
  IF X_CNT_CLOB > 0 THEN
    GEN_MERGE_WITH_COB_COL(P_TABLENAME, P_WHERE_CLAUSE);
  ELSE
    GEN_MERGE_WITHOUT_CLOB_COL(P_TABLENAME, P_WHERE_CLAUSE);
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '--' ||
                         SQLERRM);
END PROC_GEN_MERGE_SCRIPT;

add to del.icio.us saved by 0 users

Export or Download Gmail mails and open it offline using Thunderbird

Well if you are looking for a way to download email from gmail, and then open it offline, here is the best way to do it.

1. Click https://www.google.com/settings/takeout
2. Click Create an archive.
3. Select the product, like gmail, calendar etc. You can specifically select a label of your Gmail too.
4. Create Archive.
5. You will get an email once archive is over, download it. That's all.

Opening the downloaded mails using Thunderbird.
1. Launch Thunderbird.
2. Left side you will find local folder in Thunderbird.
3. Click local folder and check properties to find the physical location of the folder.
4. Extract the archive file of your gmail, you will get .mbox for mails.
5. Copy that file to the local folder of Thunderbird.
6. Restart Thunderbird and you will find your mail under local folder.

add to del.icio.us saved by 0 users

Find three consecutive available seats

Table name is SEATS_TABLE and following are the rows of the table:

SEAT_NO	BOOKED
A1	N
A2	N
A3	Y
A4	N
A5	N
A6	Y
A7	N
A8	N
A9	N
A10	Y
A11	N
A12	N
A13	Y
A14	N
B1	N
B2	N
B3	N
B4	Y
B5	N
B6	Y
B7	N
B8	N
B9	N
B10	Y
B11	Y
B12	N
B13	N
B14	N

Solution 1 : 

with not_booked_seats as (select seat_no,

       lead(seat_no, 1, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_2,

       lead(seat_no, 2, 0) over(order by substr(seat_no, 1, 1), to_number(substr(seat_no, 2))) seat_no_3

  from Seats_table t1

  where t1.booked = 'N')

select * from   not_booked_seats

where to_number(substr(seat_no,2))+1= to_number(substr(seat_no_2,2))

and to_number(substr(seat_no,2))+2= to_number(substr(seat_no_3,2))

Solution 2: 

select *

  from (select t1.seat_no col1, t2.seat_no col2, t3.seat_no col3

          from Seats_table t1, Seats_table t2, Seats_table t3

         where t1.booked = 'N'

           and t2.booked = 'N'

           and t3.booked = 'N')

 where substr(col1, 2) + 1 = substr(col2, 2)

   and substr(col2, 2) + 1 = substr(col3, 2)

   and substr(col1,1,1) = substr(col2,1,1)

   and substr(col2,1,1) = substr(col3,1,1)

Related Question :

Find three consecutive available numbers from a table.

add to del.icio.us saved by 0 users

Oracle SQL Interview Questions

1. How to print Mathematical table using SQL in oracle.

Solution : SELECT &TABLE_OF * ROWNUM AS Print_Table FROM DUAL 
                 CONNECT BY LEVEL <= 10;

2. How to split and print all the characters of a word in rows using SQL in Oracle. Exp - Input : Hello, Output :

                         H
                         E
                         L
                         L
                         O

Solution : SELECT substr('&YourWord',ROWNUM,1) FROM DUAL 

                 CONNECT BY LEVEL <= length('&YourWord');

3. How to print all the dates between two given dates.

Solution : SELECT TO_DATE('01-Dec-2013', 'DD-Mon-YYYY') + ROWNUM - 1

                 FROM DUAL

                CONNECT BY LEVEL <= TO_DATE('10-Dec-2013', 'DD-Mon-YYYY') + 1 -

                TO_DATE('01-Dec-2013', 'DD-Mon-YYYY')

4.Split comma separated string into rows in Oracle.

Solution : SELECT REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL)

     FROM DUAL

   CONNECT BY REGEXP_SUBSTR('Jai,Shri,Ram,Hare,Ram', '[^,]+', 1, LEVEL) IS NOT NULL;

add to del.icio.us saved by 0 users