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

Yamaha FZ - Lord of the Street

Yamaha FZ16 Midnight Limited Edition

It was year 2009, I was in Hyderabad for training, when I saw FZ for the first time. The time i saw it, I got curious to know about it. After some research, I gathered all the information about it. The brand name Yamaha, Its rear thick tyre and its Leopard look made me crazy for it. But that time I could gather information only :) . Days, months and year passed but my craze for this bike never ended. I got job. But I had some other priorities, so could not think of spending 85,000 rupees for bike. Few  months back one evening in office some brain attack :) came  and I thought I will buy a bike. I discussed my parents, gave all the logic to convince them for a bike. I spent almost half an hour on phone for it. Finally they were ready. But the Question was, Which bike?

I said, 'I have three options Pulsar, Apache or FZ'. But I like FZ.

Then my father told to gather all the information about that bike and advised me to purchase on Dhanteras ; a Holy Day which comes one day before Diwali. My father wanted me to buy Hero Honda Bike (Which is Hero now) or Discover.

I told every one is having Discover so i will not purchase it :). Finally my search started. I liked the FZ look, but i could not invest approx 85K just for looks.

Then I started enquiring with people having these bikes.
TVS Apache : I asked one of my colleague who is having Apache 160. He told me that its good but requires more maintenance. Its engine has some problem as it gets older. And personally i dont have much faith in TVS.

Next Bajaj Pulsar: Well first thing about pulsar is that every 3rd bike is Pulsar, So you will never feel that you have taken new bike. It is a good bike, its successful but it has gone old now.

And then Yamaha FZ: Well this is a new bike, with Yamaha power, having leopard looks. Its a clone of Yamaha FZ1. It's really Lord of Streets.
Good power, fast pick-up, awesome balance and fantastic control of FZ makes it better than any bike of its range. Its thick rear tyre adds more control and balance while riding. Its more comfortable then any other bike.

Well in FZ we have 2 models, one is FZ16 and other is FZS. Among these which one?
If we talk about old fz16, the only demerit was that it was not having kick start. But now in all models we have this. FZS come in dual color. Having some graphics. FZ16 comes in single color. For those graphics it costs around INR 2000 extra. Luckily for Dewali Yamaha lunched Midnight Limited Edition in Golden-Black Color in both the models. I liked the color. And for that edition in FZ and FZS there is no any difference other than the tank sticker which contains one extra S for FZS. So I finally decided to go with it. I paid 85,000 which included 3 years extended warranty, Buzzer, grip cover, Seat Cover, Leg Guard, engine guard, bike cover and Teflon Coating. And then I took my bike out of Showroom. Its start sound made me feel happy. Its smooth acceleration was awesome.

I read full user manual and following all the instruction to knock the engine.

Its really a good experience with Yamaha FZ. I will recommend to go for it if you really want somthing new and best. Its mileage is 35KM/L in city. I have gone to a max speed of 115KM/Hour.Being a small bike in length we can easily move in traffic. I bought my FZ in October 2011. Its 10 months now, I am really loving it.

add to del.icio.us saved by 0 users

Arranged Marriage :- A Crucial Decision "Whom to Marry?"

(Applicable to person belongs to Republic of India and Looking for web Based Marriage)

There are times in a person's life when he needs to take crucial decisions on his own. Marriage is one of them. Believe me, the decision on whom to marry is the most important decision a person will make in his life. After marriage, your wife is the most important person in your life. She can make or break your life. The mere thought of this is very frightening.


Some of the questions that crop up are - a.. What sort of a girl do I marry? b.. Will she adjust in my family? c.. How can I decide on a girl by just meeting her for a few times? d.. When should I get married? e.. This is my life. So, I should choose the girl I marry, but then what if I make a mistake? .. so on and so forth. I will try to address these & many more questions in the following sections. The Nine Rules of Arranged marriage.


Rule 1 - Magic no. 28

In an ideal scenario, a girl goes to college at the age of 18. By the time she graduates, goes for her post graduation and/ or works for 1-2 years, she will be about 23- 24. This means that she has spent about 5 years away from her home. In the 5 years period, she would meet many smart guys at college or during her first few years on job. So, in all probability it would be difficult to find a good girl older than 24 yrs. Secondly, in Indian families there is lot of pressure on the girl's to get married by the time they become 24-25.

Statistics says that there is a generation gap after every 5 years. So, in such scenario, one would prefer to marry a girl who is about 3-4 years younger to you. Thus, working backwards, an ideal age for a guy to get married is by 28. Earlier the marriage, the better it is.

Well, as we all know, in the current market scenario, there will never be stability in our career. So, I believe there is no such thing as, "I will marry when I settle down".

Rule 2 -- Subset of marriage-able girls

At times you hear statements like, "I am not getting the right match, I will look after 3 months, I will find a better match then". Well the truth is otherwise. The subset of unmarried girl looking for a match is fixed. From this subset, there would be girls who would get married & there would be new girls added who would be looking for a match. The net result is that at any given time, the variety & number of marriage-able girls are fixed.

Rule 3 - Competition for girls

Like all other facets of life, there is lot of competition for good girls. So, if you are looking for a girl who is post graduate, done her Engg, is working, very beautiful, smart, from a good family etc. etc, just think again. There are other guys who are also looking for similar girls & probably they are better off than you in terms of career, looks personality etc. Given a choice every guy would like to marry Aishwarya Rai. So, set your expectations accordingly.

Rule 4 -- Understanding girls

You would have met a lot of people during your life. As we all know, its difficult to judge a person based on a few meetings. I am sure you would agree with me that in case of girls it is even more difficult to understand them in a few meetings. I know people who are still trying to understand their wife. ;-).. Understanding your spouse is a life long assignment. So, then how do you select a girl based on a few meeting? This is where you need to take the help of your parents/ friends & latest technologies like email/chat to choose your girl.

Rule 5 - Society expectation

The selection process is tough on every one who is involved in the process. In arranged marriage, involvement of family & society is pretty high. You can't meet a girl 3-4 times & then say no to her. It is bad for her future. So, you should have a good short-listing criterion. Meet only a few girls & be sure what you are looking for. It is for the benefit of everyone involved.

Rule 6 -- Marriage between equals

Unlike love marriage, in arranged marriage you also marry into the girl's family. In arranged marriages, family support plays a major role in ensuring a successful marriage. This is where the compatibility of social status, family values & caste/religion plays a major role. Its important to note that in case there is a perfect match between the two families, the marriage is destined to succeed.

Rule 7 - Know yourself

Unlike love marriage, in arranged marriage you first marry a person & then fall in love. So, it's very important that you do a self-assessment on the kind of person you would love. They say, "Opposite attract", while they also say, "Bird of same feather flock together". So, you take a call on what sort of person you like. Take a pen & paper; write down the kind of attributes you are looking for in a girl. Say, she should ideally have the looks of Sonia, the style of Monica, the voice of Sheena, the patience of Rashmi. You will certainly not find the perfect girl, but then you would have a good idea of what you are looking for. The secret here is to set some minimum criteria for selection. Don't forget rule no.3 here.

Rule 8 -- Girl's Beauty

A girl's looks attract, but then no one wants to end up marrying a dumb It is like buying your bike. When you initially buy it, you are crazy about the looks, but later on you love it for its reliability, fuel economy & comfort level. Similarly, a girl's looks are important, but then it should not be the most important criteria. Later on in life, you will get bored of her looks. It is then that her personality & behavior will make all the difference to your marriage. I am sure your parents will be able to advice you a lot better on this topic.

Rule 9 -- Taking advice

As I have mentioned in the next rule, it's very important that the final decision on whom to marry must necessarily be yours. However, don't do the mistake of isolating yourself from the world while planning your marriage. Discuss with your parents & very close friends on this issue. They are your well wishers. Secondly, in such important matters its necessary that you analyze all possibilities. Remember, I am not suggesting that you follow others' advice, but don't forget to take their advice. 


Rule 10 -- Own decision

All said & done, it's your marriage & your life that is at stake. Once you are married, you & your wife are the only persons who will be facing the music. Don't marry a girl just because your parents or friends asked you to do so. After marriage, if things don't work out & you end up saying, "It's because of my friends or my parents that I married you", then your marriage is destined for disaster. If the girl is of your choice, it is you who will be responsible for whatever happens. That's when the marriage works out perfectly. So, ensure that you marry the girl of your choice. How to approach the selection process?

From the day, a person decides to get married; the selection process takes a minimum of 3 months. The whole process needs a lot of patience & commitment. The ideal steps to be followed are:

a.. Definition phase –

Define the minimum criteria for the kind of life partner you are looking for in terms of education, physical appearance, social status, family values, future career plans. Remember the Rule 3 here.

b.. Lead Generation phase –

Place ads in various newspapers, magazines, websites, through friends, family friends, family societies & association etc. You need to exhaust all possible means of getting bio-datas at one go. Remember the Rule 2 here.

c.. Short listing phase –

Based on your selection criteria, short-list the interesting bio-datas. The general process followed for correspondence is as follows:

1. The initiator sends a one page profile of himself/herself.
2. Based on the profile, the receiver sends his/her one page profile along with request for detailed profile, photo, horoscope.

3. The initiator then sends the requested information along with a request for similar information. 
4. The receiver send similar information.

5. If the bio-data is selected, it is passed over to the next phase. 


A.. Casual interaction phase –

Based on short listing, about 7 to 10 bio-datas are taken forwarded to this phase. The next step to follow here is to exchange email/ chat ids. The guy & the girl then interact for 10 - 15 days to try & judge mutual compatibility through email/chat.

B.. Family interaction phase –

Based on the earlier phase, about 5 leads are taken for consideration in this phase. During this phase, the parents get involved & check the background information about the families to find mutual compatibility.

C.. The dating phase –

Based on the earlier phase about 3 leads are taken forward to this phase. During this phase, the guy & the girl interact by going out alone for 2-3 times. The guy needs to prepare a set of simple questions like who is your favorite star, what are your hobbies? He needs to use his judgment to analyze the girl based on her responses.

D.. The D-day phase –

Finally, the D-day comes when the guy has to select the girl he wants to spend his life with. If the process if followed systematically, there will be no ambiguity in deciding who should be your life partner.

Finally, my dear friends, marriage is all about compromises. In spite of all the planning that you do, there are a lot of uncertainties in a marriage. In fact this is the best part about marriage. Just remember that the person you marry must be of your choice. In such case, there would be no going back for both of you.

A few words of advice: To make your marriage a success; just believe in the age-old virtue, "Never do anything to others that you don't like for yourself".

add to del.icio.us saved by 0 users