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

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

CHECK CONSTRAINTS ON SYSDATE IN ORACLE

RESTRICTIONS ON CHECK CONSTRAINTS

I.We cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK    OPTION clause, which is equivalent to specifying a check constraint for the view.
II.The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other        tables.
III.Conditions of check constraints cannot contain the following constructs:

1. Subqueries and scalar subquery expressions
2. Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP,  SESSIONTIMEZONE,   SYSDATE, SYSTIMESTAMP, UID,  USER, and USERENV)
3. Calls to user-defined functions
4. Dereferencing of REF columns (for example, using the DEREF function)
5. Nested table columns or attributes
6. The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
7. Date constants that are not fully specified

So from the above detail its quite clear that we can not write any comparison with SYSDATE in check constrains..

Now imagine a situation that we are having one table and in that table we want some validation. The requirement is that we are taking Date Of Birth from the user and we want that only that user's details can reside inside our table who is older then 18 years else his insert should not take place.

So for this validation we have to write one trigger to take care of this situation.

/*This trigger will fire on every insert on table name TABLE_NAME and check that the date inserted in DOB_COLUMN_NAME column is greater than the difference of SYSDATE and inserted value by 18 and accordingly either it will allow the data to go inside the table or abort the transaction. :NEW and :OLD are co-relation identifiers used to check for the value inserted, updated or deleted. It will work only in triggers*/

CREATE OR REPLACE TRIGGER PROJECT_NAME_AGE_CHECK_TRIGGER

 AFTER INSERT AFTER UPDATE ON TABLE_NAME

 FOR EACH ROW

 DECLARE

 V_AGECHECK EXCEPTION;

 BEGIN

 IF MONTHS_BETWEEN(SYSDATE,:NEW.DOB_COLUMN_NAME)/12 < 18 THEN

 RAISE V_AGECHECK;

 END IF;

 EXCEPTION

 WHEN V_AGECHECK THEN

 RAISE_APPLICATION_ERROR(-20001,'YOU ARE MINOR') ;

 END;

add to del.icio.us saved by 0 users

Armstrong number also called Narcissistic numbers.

Definition: 

 Armstrong numbers are the sum of their own digits to the power of the number of digits.

In other words

An Armstrong number is an n-digit base b number such that the sum of its (base b) digits raised to the power n is the number itself.

And according to wikipedia 

In recreational number theory, a narcissistic number (also known as a pluperfect digital invariant (PPDI), an Armstrong number or a plus perfect number) is a number that is the sum of its own digits each raised to the power of the number of digits. This definition depends on the base b of the number system used, e.g. b = 10 for the decimal system or b = 2 for the binary system or 

For Example:

There are just four numbers, after unity, which are the sums of the cubes of their digits:

153 = 1³ + 5³ + 3³

370 = 3³ + 7³ + 0³

371 = 3³ + 7³ + 1³

407 = 4³ + 0³ + 7³.

1.Program to check for Armstrong Number

#include

//Prototype of all the function being used in the program

int number_of_digits(int give_number);

int check_armstrong(int given_number,int number_of_digits);

int power(int temp_var,int number_of_digits);

//Main function starts

int main()

{

int give_number,no_of_digits,flag;

printf("\nEnter the number you want to check for Armstrong\n");

scanf("%d",&give_number);

no_of_digits=number_of_digits(give_number);

flag=check_armstrong(give_number,no_of_digits);

flag?printf("\n%d is Armstron\n",give_number):printf("\n%d is not Armstrong number\n",give_number);

return 0;

}

//Function to check how many degits are there in the given number

int number_of_digits(int given_number)

{

int count=0,temp;

while(given_number>0)

{

temp=given_number%10;

given_number=given_number/10;

count++;

}

return count;

}

//Function to check that the number is Armstrong or not

int check_armstrong(int given_number,int number_of_digits)

{

int temp_var,clone_number,regenerate_number=0;

clone_number = given_number;

while(clone_number>0)

{

temp_var=clone_number%10;

regenerate_number=regenerate_number + power(temp_var,number_of_digits);

clone_number=clone_number/10;

}

if(regenerate_number==given_number)

return 1;

else

return 0;

}

//Function the calculate the power same as pow() function

int power(int temp_var,int number_of_digits)

{

int cal_power=1,i;

for(i=0;i

cal_power=cal_power*temp_var;

return cal_power;

}

2.Program to find Armstrong Number in a given range 

#include

//Prototype of all the function being used in the program

int number_of_digits(long int give_number);

int check_armstrong(long int given_number,long int number_of_digits);

int power(long int temp_var,long int number_of_digits);

//Main function starts

int main()

{

long int give_number,no_of_digits,flag;

printf("\n Enter the number you want to check\n");

for(give_number=1;give_number<9999999;give_number++)

{

no_of_digits=number_of_digits(give_number);

flag=check_armstrong(give_number,no_of_digits);

flag?printf("\nNumber %ld is Armstron\n",give_number):printf("");

return 0;

}

}

//Function to check how many degits are there in the given number

int number_of_digits(long int given_number)

{

long int count=0,temp;

while(given_number>0)

{

temp=given_number%10;

given_number=given_number/10;

count++;

}

return count;

}

//Function to check that the number is Armstrong or not

int check_armstrong(long int given_number,long int number_of_digits)

{

long int temp_var,clone_number,regenerate_number=0;

clone_number = given_number;

while(clone_number>0)

{

temp_var=clone_number%10;

regenerate_number=regenerate_number + power(temp_var,number_of_digits);

clone_number=clone_number/10;

}

if(regenerate_number==given_number)

return 1;

else

return 0;

}

//Function the calculate the power same as pow() function

int power(long int temp_var,long int number_of_digits)

{

long int cal_power=1,i;

for(i=0;i

cal_power=cal_power*temp_var;

return cal_power;

}

Note: On compiling if you get any error than check it may that html has converted the lass than and greater than symbol as its own tags and also please do proper formatting.  This program  has been compiled and checked on GCC.

add to del.icio.us saved by 0 users