Generic function and procedures

1. Purpose: This function will return the date from the input - no. of business days and from date

Modifications:  11/4/2003        xxxxxxxxxxxxxxxxxxxxxx
--------------------------------------------------------------------------------------------------------*/
FUNCTION    calc_date_add_business_days(from_date date, no_of_business_days number) RETURN DATE IS

out_date date;
weekend_day varchar2(20);
temp_date date;
i number:= 0;

BEGIN
  out_date := from_date;

  loop exit when (i = no_of_business_days);
  out_date := out_date +1;
    weekend_day := to_char(out_date, 'D');
    if (weekend_day not in (1,7)) then
        i := i+1;
    end if;

  end loop;

  return (out_date);
end;
 --first parameter is input and second parameter number of days after
 select calc_date_add_business_days(trunc(TO_DATE('27-JUN-2015')),1) from dual

2.) get age range

FUNCTION GET_AGE_RANGE(date_in IN date, /* e.g. birthday */
                        as_of_date_in IN date default sysdate, /* e.g. as of sysdate */
                        default_if_null IN varchar2 default null) RETURN VARCHAR2 IS
   temp number;
   ret_val varchar2(50) := null;
 BEGIN
   temp := floor((as_of_date_in - date_in)/365.25);
   if temp between 0 and 18 then
      ret_val := 'JRA';
   elsif temp between 18 and 29 then
      ret_val := '18-29';
   elsif temp between 30 and 49 then
      ret_val := '30-49';
   elsif temp between 50 and 64 then
      ret_val := '50-64';
   elsif temp > 64 then
      ret_val := '65+';
   else
      ret_val := default_if_null;
   end if;
   RETURN ret_val;
 END get_age_range;
---------------------
select GET_AGE_RANGE(to_date('20-JUN-2015')) from dual
3.)
/* this function is needed for delimited files*/
  function c_get(p_text varchar2,p_occur number, p_delimiter varchar2) return varchar2 is
    v_out varchar2(32000) :=null;
  begin
    v_out:=substr(p_text,instr(p_text,p_delimiter,1,p_occur)+1,instr(p_text,p_delimiter,1,p_occur+1)-(instr(p_text,p_delimiter,1,p_occur)+1));
    return (v_out);
  end;

select dtc_sql_util.c_get('|subbu|srikanth|viswa|',1,'|') from dual
4.)
/**Returns only numbers from the given string**/
function return_numbers(p_text varchar2) return varchar2 is
  v_length number:=length(p_text);
  v_text varchar2(30000):=null;
  v_char varchar2(10):=null;
begin
 if (p_text is not null) then
  for i in 1..v_length loop
    v_char:=substr(p_text,i,1);
    if (ascii(v_char) between 48 and 57) then
      v_text:=v_text||v_char;
    end if;
  end loop;
 end if;
    return trim(v_text);
end;
5.)
/**Returns only alpha bytes(abcds) from the given string**/
function return_abcds(p_text varchar2) return varchar2 is
  v_length number:=length(p_text);
  v_text varchar2(30000):=null;
  v_char varchar2(10):=null;
begin
 if (p_text is not null) then
  for i in 1..v_length loop
    v_char:=substr(p_text,i,1);
    if (ascii(v_char) between 65 and 90) or (ascii(v_char) between 97 and 122) then
      v_text:=v_text||v_char;
    end if;
  end loop;
 end if;
    return trim(v_text);
end;
6)Routine to get next sequence no from the sequence
/*************************************************************
*
*  Routine to return the next sequence value from any sequence
*  that is passed to the routine.
*
*************************************************************/
FUNCTION get_nextval (in_sequence IN VARCHAR2)
         RETURN NUMBER
IS
  str          VARCHAR2(100) := 'SELECT ' || in_sequence || '.' ||
                                'NEXTVAL next_seq FROM dual where rownum = 1' ;
  cur          INTEGER := DBMS_SQL.OPEN_CURSOR;
  upd_rows     INTEGER;
  return_value INTEGER;
BEGIN
  DBMS_SQL.PARSE(cur, str, DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN (cur, 1, return_value);
  upd_rows := DBMS_SQL.EXECUTE_AND_FETCH(cur);
  IF upd_rows = 1 THEN
     DBMS_SQL.COLUMN_VALUE (cur, 1, return_value);
  END IF;
  DBMS_SQL.CLOSE_CURSOR (cur);
  RETURN return_value;
EXCEPTION
  WHEN OTHERS THEN
       DBMS_SQL.CLOSE_CURSOR(cur);
       RAISE_APPLICATION_ERROR(-20100,'DTC_UTIL.GET_NEXTVAL: Unable to get nextval for ' || in_sequence);
END get_nextval;
PROCEDURE ddl (ddl_string IN VARCHAR2, out_err_msg OUT VARCHAR2)
IS
  cur INTEGER;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, ddl_string, DBMS_SQL.V7);
  DBMS_SQL.CLOSE_CURSOR (cur);
EXCEPTION
  WHEN OTHERS THEN
       out_err_msg := 'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm;
       --RAISE_APPLICATION_ERROR(-20101,'DTC_UTIL.DDL failed on: ' || ddl_string || ' SQL Error Message: ' ||sqlerrm);
END ddl;
/******************************************************************************
  Returns the value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_column_value (   column_name   IN  VARCHAR2
                , table_name    IN  VARCHAR2
                , pk_column IN  VARCHAR2
                , pk_value  IN  VARCHAR2
                , err_text  OUT VARCHAR2)
RETURN VARCHAR2 IS
  sql_stmt  VARCHAR2(2000);
  cur_id    INTEGER := DBMS_SQL.OPEN_CURSOR;
  exe_err   INTEGER;
  col_value VARCHAR2(2000);

BEGIN
  sql_stmt := 'SELECT '||column_name||' '||
          'FROM '||table_name||' '||
          'WHERE '||pk_column||' = '''||pk_value||'''';
  DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
  exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
  DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
  DBMS_SQL.CLOSE_CURSOR(cur_id);

  RETURN col_value;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    err_text := 'Too Many Rows Selected';
    RETURN 'TOO_MANY_ROWS';
  WHEN NO_DATA_FOUND THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    RETURN NULL;
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    err_text := SQLERRM;
    RETURN 'ERROR';
END get_column_value;

/******************************************************************************
  Returns the date value of the column passed (good for post-query stuff)
******************************************************************************/
FUNCTION get_date_column_value (   column_name   IN  VARCHAR2
                , table_name    IN  VARCHAR2
                , pk_column IN  VARCHAR2
                , pk_value  IN  VARCHAR2
                , err_text  OUT VARCHAR2)
RETURN VARCHAR2 IS
  sql_stmt  VARCHAR2(2000);
  cur_id    INTEGER := DBMS_SQL.OPEN_CURSOR;
  exe_err   INTEGER;
  col_value VARCHAR2(2000);

BEGIN
  sql_stmt := 'SELECT to_char('||column_name||',''MM/DD/YYYY'') '||
          'FROM '||table_name||' '||
          'WHERE '||pk_column||' = '''||pk_value||'''';
  DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);
  DBMS_SQL.DEFINE_COLUMN(cur_id,1,col_value,100);
  exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,TRUE);
  DBMS_SQL.COLUMN_VALUE(cur_id,1,col_value);
  DBMS_SQL.CLOSE_CURSOR(cur_id);

  RETURN col_value;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    err_text := 'Too Many Rows Selected';
    RETURN 'TOO_MANY_ROWS';
  WHEN NO_DATA_FOUND THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    RETURN NULL;
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cur_id);
    err_text := SQLERRM;
    RETURN 'ERROR';
END get_date_column_value;



/************************************************************************
*
* Return the roles for the user/program that are currently active.
*
************************************************************************/
/***** Commented out for DTC - Needs to be modified to support new user/role structure *****
FUNCTION get_user_roles(in_user_id    IN cmn_user.user_id%TYPE       DEFAULT 1,
                        in_program_id IN cmn_program.program_id%TYPE DEFAULT 1)
           RETURN VARCHAR2
 IS
     CURSOR get_program_role IS
       SELECT role
       FROM   cmn_user_prog_role
       WHERE
              program_id   = in_program_id
       AND    user_id      = in_user_id
       AND    sysdate      between start_date and nvl(end_date,sysdate);
     role_str VARCHAR2(1000);
     ret_val  VARCHAr2(1000);
 BEGIN
 FOR i IN get_program_role LOOP
     role_str := ',' || i.role || role_str;
 END LOOP;
 -- strip off the leading comma from the role streing, if it is NULL then
 -- return 'DTC_USER'.
 ret_val := nvl(substr(role_str,2,1000),'DTC_USER');
 RETURN ret_val;
END get_user_roles;
/******************************************************************************
  This procedure sets the seq value to the value passed
******************************************************************************/
PROCEDURE set_seq_value(  in_seq_name   IN  VARCHAR2
                        , in_value      IN  INTEGER) IS
  sql_drop  VARCHAR2(500);
  sql_create    VARCHAR2(500);
  sql_stmt  VARCHAR2(500) := 'SELECT '||in_seq_name||'.NEXTVAL FROM DUAL';
  cur_id    INTEGER;
  exe_err   INTEGER;
  curr_val  INTEGER;
BEGIN
  /* First Drop The Sequence */
  sql_drop := 'DROP SEQUENCE '||in_seq_name;
  cur_id := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(cur_id,sql_drop,DBMS_SQL.NATIVE);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20169,'Could Not Drop Sequence '||
      in_seq_name||', it may be an invalid sequence!');
  END;
  DBMS_SQL.CLOSE_CURSOR(cur_id);
  sql_create := 'CREATE SEQUENCE '||in_seq_name||CHR(10)||
        'INCREMENT BY 1'||CHR(10)||
        'START WITH '||in_value|| CHR(10)||
        'MINVALUE '||in_value ||CHR(10)||
        'MAXVALUE 9999999999'||CHR(10)||
        'NOCYCLE'||CHR(10)||
        'CACHE 20'||CHR(10)||
        'NOORDER';
  /* Then re-create the sequence */
  cur_id := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(cur_id,sql_create,DBMS_SQL.NATIVE);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20169,'Could Not Re-Create Sequence '||
      in_seq_name||', it has been dropped so you must recreate it so it will work, Sorry!'||
      '--'||SQLERRM);
  END;
  DBMS_SQL.CLOSE_CURSOR(cur_id);
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(cur_id) THEN
      DBMS_SQL.CLOSE_CURSOR(cur_id);
    END IF;
    RAISE_APPLICATION_ERROR(-20569,'DTC_UTIL.SET_SEQ_VALUE Error--'||SQLERRM);
END set_seq_value;
/******************************************************************************
  This function returns a format mask (99,999,999.99) based on the length and
  decimal places passed
******************************************************************************/
FUNCTION gimme_a_mask(   in_length  IN  INTEGER
                        , in_dec_places IN  INTEGER
            , do_commas IN  BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2 IS
  curr_length   INTEGER := in_length;
  curr_decimal  INTEGER := NVL(in_dec_places,0);
  mask      VARCHAR2(50);
  num_of_commas INTEGER;
BEGIN
  /* mask := RPAD('9',curr_length,'9'); */
  mask := RPAD('9',10,'9');
  IF do_commas THEN
    mask := REPLACE(TO_CHAR(TO_NUMBER(mask),'9,999,999,999'),' ',NULL);
  END IF;
  IF curr_decimal > 0 THEN
    mask := mask||'.'||RPAD('9',curr_decimal,'9');
  END IF;
  RETURN mask;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20187,'DTC_UTIL.GIMME_A_MASK Error--'||SQLERRM);
END gimme_a_mask;
/******************************************************************************
  This function returns 'Y' if the value passed is a number or 'N' if it is not
******************************************************************************/
FUNCTION is_this_a_number(    in_string     IN  VARCHAR2)
RETURN VARCHAR2 IS
  test_number   NUMBER;
BEGIN
    test_number := TO_NUMBER(in_string);
    RETURN 'Y';
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN 'N';
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20152,
    'DTC_UTIL.IS_THIS_A_NUMBER Error--Can Not Display SQLERRM, Sorry!');
END is_this_a_number;
/******************************************************************************
  This function returns the given string to the DD-MON-YY format
******************************************************************************/
FUNCTION valid_date (in_date      IN  VARCHAR2,
                     in_format    IN  VARCHAR2 DEFAULT 'DD-MON-YY'
                    )
RETURN VARCHAR2
IS
  ret_val VARCHAR2(50);
BEGIN
  ret_val := TO_CHAR(TO_DATE(in_date, in_format), in_format);
  RETURN ret_val;
EXCEPTION
  WHEN OTHERS THEN
       RETURN NULL;
END valid_date;
/******************************************************************************
  This function returns the datatype of the table.column
******************************************************************************/
FUNCTION get_datatype(in_table    IN  VARCHAR2,
                      in_column   IN  VARCHAR2,
                      in_global   IN  VARCHAR2)
RETURN VARCHAR2
IS
  CURSOR get_column_datatype IS
  SELECT data_type
  FROM user_tab_columns
  WHERE table_name      =  in_table
  AND   column_name     =  in_column;
  CURSOR get_global_datatype IS
   SELECT datatype
   FROM   dtc_global_variable
   WHERE  variable_name  = in_global;
  ret_val VARCHAR2(30);
BEGIN
IF in_column IS NOT NULL THEN
  OPEN get_column_datatype;
  FETCH get_column_datatype INTO ret_val;
  CLOSE get_column_datatype;
ELSE
  OPEN get_global_datatype;
  FETCH get_global_datatype INTO ret_val;
  CLOSE get_global_datatype;
END IF;
  RETURN ret_val;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RAISE_APPLICATION_ERROR(-20519,'dtc_util.get_datatype: Unable to find datatype.');
END get_datatype;
/******************************************************************************
  This function returns the position of the column in an index
******************************************************************************/
FUNCTION get_ind_col_position(in_owner    IN  VARCHAR2,
                              in_table    IN  VARCHAR2,
                              in_column   IN  VARCHAR2,
                              in_dblink  IN  VARCHAR2 default null
                             )
RETURN INTEGER
IS
  CURSOR get_ind_col IS
   SELECT
           column_position
    FROM
           sys.all_ind_columns
    WHERE
           TABLE_OWNER        =   in_owner
    AND    TABLE_NAME         =   in_table
    AND    COLUMN_NAME        =   in_column;

    v_select_statement        varchar2(1000);
    CURSOR_HANDLE             INTEGER;
    EXECUTE_RESULTS           INTEGER;

  ret_val   INTEGER;

BEGIN

  if in_dblink is null then
    OPEN get_ind_col;
    FETCH get_ind_col INTO ret_val;
    IF get_ind_col%NOTFOUND THEN
      ret_val := 0;
    END IF;
    CLOSE get_ind_col;

  else
      v_select_statement := 'select column_position from sys.all_ind_columns@'||in_dblink||
                            ' where table_owner = '''||in_owner||''''||
                            '   and table_name = '''||in_table||''''||
                            '   and column_name = '''||in_column||'''';

      CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(CURSOR_HANDLE,v_select_statement,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(CURSOR_HANDLE,1,ret_val);
      EXECUTE_RESULTS := DBMS_SQL.EXECUTE(CURSOR_HANDLE);
      IF DBMS_SQL.FETCH_ROWS(CURSOR_HANDLE) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(CURSOR_HANDLE,1,ret_val);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(CURSOR_HANDLE);

  end if;
  RETURN ret_val;

EXCEPTION

 WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20783,'dtc_util.get_ind_col_position ' || sqlerrm);

END get_ind_col_position;



/******************************************************************************
  This function returns the number of pipe sessions running
******************************************************************************/
--/************************* Commented out for DTC ******************************
FUNCTION get_pipe_count
RETURN INTEGER
IS
  CURSOR get_count IS
    SELECT count(*)
    FROM   sys.dba_jobs_running
    WHERE  job < 0;    -- all PAP j0obs run with job < 0
  ret_val  INTEGER;
BEGIN
  OPEN  get_count;
  FETCH get_count INTO ret_val;
  CLOSE get_count;
  RETURN ret_val;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END get_pipe_count;
--******************************************************************************/
PROCEDURE delimited_to_table ( in_string     IN  VARCHAR2
                  ,in_delimiter  IN  VARCHAR2 DEFAULT '|'
                  ,out_rec_count OUT  INTEGER
                  ,out_tab       OUT dtc_global.vc_2000
                             )
IS
  i    integer;
  p    integer;
  np   integer;
BEGIN
if in_string is null then out_rec_count := 0; return; end if;
  i  := 0;
  p  := 0;
  np := 0;
  LOOP
    np := INSTR(in_string,in_delimiter,p+1);
    IF np = 0 THEN
--       IF i <> 0 THEN
          i := i + 1;
          out_tab(i) := substr(in_string,p+1);
--       END IF;
       EXIT;
    ELSE
       i := i + 1;
       out_tab(i) := substr(in_string,p+1,np-(p+1));
       p := np;
    END IF;
  END LOOP;
  out_rec_count := i;
END;
PROCEDURE table_to_delimited(  in_tab         IN      dtc_global.vc_2000
                      , in_delimiter  IN      VARCHAR2 DEFAULT '|'
                      , out_rec_count OUT     INTEGER
                  , out_string    OUT     VARCHAR2
                 )
IS
  i   integer;
  j   integer;
  t_out_string varchar2(2000);
BEGIN
  t_out_string := '';
  i := in_tab.COUNT;
  FOR J in 1..i
  LOOP
     IF j <> i THEN
       t_out_string := t_out_string || in_tab(j) || in_delimiter;
     ELSE
       t_out_string := t_out_string || in_tab(j) ;
     END IF;
  END LOOP;
  out_rec_count := i;
  out_string := t_out_string;
END;


/******************************************************************************
  This procedure writes a message to a pipe named 'HOST_' || user
  The messages can be retrieved real-time by executing host_r on the host
  host_r is below in the comments
******************************************************************************/
    procedure         host( cmd in varchar2 ) is
        status number;
    begin
        dbms_pipe.pack_message( cmd );
        status := dbms_pipe.send_message( 'HOST_' || UPPER(user) );
        if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
        end if;
    end host;



/******************************************************************************
  This section uses set_c_string and set_c_delimiter to declare a string and its
  delimiter, then get_val (given an number)  will return that argument in the
  string
******************************************************************************/
    FUNCTION get_val (arg NUMBER,
 in_string VARCHAR2, --  DEFAULT c_string,
 in_delimiter VARCHAR2 -- DEFAULT c_delimiter
 ) RETURN VARCHAR2 IS
        val VARCHAR2(256) := SUBSTR(in_string,
                    ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1),
                    ifelse(INSTR(in_string,in_delimiter,1,arg) = 0,1000000,INSTR(in_string,in_delimiter,1,arg))-ifelse(arg = 1,1,INSTR(in_string,in_delimiter,1,arg-1)+1));
    BEGIN
        IF INSTR(in_string,in_delimiter,1,arg-1) = 0 THEN
            val := NULL;
        END IF;
        RETURN val;
    END get_val;
    PROCEDURE set_c_string (str VARCHAR2) IS
    BEGIN
        c_string := str;
    END set_c_string;
    PROCEDURE set_c_delimiter (del VARCHAR2) IS
    BEGIN
        c_delimiter := del;
    END set_c_delimiter;

/******************************************************************************
  This section creates a DECODE for pl/sql.  Overloaded, evaluates boolean expression
  and returns tval_in if true else fval_in
  Also includes ex_dyn, a generic execute for dbms_sql
******************************************************************************/
    FUNCTION ifelse (BOOL_IN IN BOOLEAN,
            TVAL_IN IN BOOLEAN,
            FVAL_IN IN BOOLEAN)
        RETURN BOOLEAN IS
    BEGIN
        IF BOOL_IN THEN
            RETURN TVAL_IN;
        ELSE
            RETURN FVAL_IN;
        END IF;
    END ifelse;
    FUNCTION ifelse (BOOL_IN IN BOOLEAN,
            TVAL_IN IN DATE,
            FVAL_IN IN DATE)
        RETURN DATE IS
    BEGIN
        IF BOOL_IN THEN
            RETURN TVAL_IN;
        ELSE
            RETURN FVAL_IN;
        END IF;
    END ifelse;
    FUNCTION ifelse (BOOL_IN IN BOOLEAN,
            TVAL_IN IN VARCHAR2,
            FVAL_IN IN VARCHAR2)
        RETURN VARCHAR2 IS
    BEGIN
        IF BOOL_IN THEN
            RETURN TVAL_IN;
        ELSE
            RETURN FVAL_IN;
        END IF;
    END ifelse;
    FUNCTION ifelse (BOOL_IN IN BOOLEAN,
            TVAL_IN IN NUMBER,
            FVAL_IN IN NUMBER)
        RETURN NUMBER IS
    BEGIN
        IF BOOL_IN THEN
            RETURN TVAL_IN;
        ELSE
            RETURN FVAL_IN;
        END IF;
    END ifelse;
    FUNCTION ex_dyn(sql_statement varchar2) RETURN VARCHAR2 IS
        cursor_handle     INTEGER;
        curs_result       INTEGER;
        ret_column        VARCHAR2(2000);
        rows_fetched      INTEGER;
    BEGIN
        cursor_handle := dbms_sql.open_cursor;
        dbms_sql.parse(cursor_handle, sql_statement, dbms_sql.native);
        dbms_sql.define_column(cursor_handle,1,ret_column,2000);
        curs_result := dbms_sql.execute(cursor_handle);
        rows_fetched := dbms_sql.fetch_rows(cursor_handle);
        dbms_sql.column_value(cursor_handle,1,ret_column);
        dbms_sql.close_cursor(cursor_handle);

        RETURN ret_column;
    EXCEPTION
        WHEN others THEN
            dbms_sql.close_cursor(cursor_handle);
            RETURN null;
    END ex_dyn;


/******************************************************************************
  This section evaluates any range.  yes_end_null and no_end_null determine
  if the second value of the range can be null or not.
******************************************************************************/
    PROCEDURE yes_end_null IS
    BEGIN
        end_null_ok := TRUE;
    END yes_end_null ;
    PROCEDURE no_end_null IS
    BEGIN
        end_null_ok := FALSE;
    END no_end_null ;
    FUNCTION C (begin_val DATE, end_val DATE) RETURN BOOLEAN IS
        begin_null BOOLEAN := begin_val IS NULL;
        end_null BOOLEAN := end_val IS NULL;
    BEGIN
        IF begin_null THEN
            IF end_null THEN
                RETURN TRUE;
            ELSE
                RETURN FALSE;
            END IF;
        ELSIF end_null_ok THEN
            IF end_null THEN
                RETURN TRUE;
            ELSIF TRUNC(begin_val) > TRUNC(end_val) THEN
                RETURN FALSE;
            ELSE
                RETURN TRUE ;
            END IF ;
        ELSE
            IF end_null OR TRUNC(begin_val) > TRUNC(end_val) THEN
                RETURN FALSE ;
            ELSE
                RETURN TRUE ;
            END IF ;
        END IF;
    END c;
    FUNCTION C (begin_val NUMBER, end_val NUMBER) RETURN BOOLEAN IS
        begin_null BOOLEAN := begin_val IS NULL;
        end_null BOOLEAN := end_val IS NULL;
    BEGIN
        IF begin_null THEN
            IF end_null THEN
                RETURN TRUE;
            ELSE
                RETURN FALSE;
            END IF;
        ELSIF end_null_ok THEN
            IF end_null THEN
                RETURN TRUE;
            ELSIF begin_val > end_val THEN
                RETURN FALSE;
            ELSE
                RETURN TRUE ;
            END IF ;
        ELSE
            IF end_null OR begin_val > end_val THEN
                RETURN FALSE ;
            ELSE
                RETURN TRUE ;
            END IF ;
        END IF;
    END  c;
    FUNCTION C (begin_val VARCHAR2, end_val VARCHAR2) RETURN BOOLEAN IS
        begin_null BOOLEAN := begin_val IS NULL;
        end_null BOOLEAN := end_val IS NULL;
    BEGIN
        IF begin_null THEN
            IF end_null THEN
                RETURN TRUE;
            ELSE
                RETURN FALSE;
            END IF;
        ELSIF end_null_ok THEN
            IF end_null THEN
                RETURN TRUE;
            ELSIF begin_val > end_val THEN
                RETURN FALSE;
            ELSE
                RETURN TRUE ;
            END IF ;
        ELSE
            IF end_null OR begin_val > end_val THEN
                RETURN FALSE ;
            ELSE
                RETURN TRUE ;
            END IF ;
        END IF;
    END c;


/******************************************************************************
  This function returns the number of delimiters passed until it finds the value
  passed in the string passed if the value.
******************************************************************************/
FUNCTION get_occurance (  in_string IN  VARCHAR2
, in_value IN  VARCHAR2
, in_delimiter IN  VARCHAR2)
RETURN NUMBER IS

  new_string VARCHAR2(2000);
  value_pos NUMBER;
  counter NUMBER;

BEGIN

  value_pos := INSTR(in_string,in_value);

  IF value_pos = 0 THEN
    RETURN NULL;
  END IF;

  new_string := SUBSTR(in_string,1,value_pos - 1);

  counter := 0;

  LOOP
    counter := counter + 1;

    EXIT WHEN new_string IS NULL;

    value_pos  := INSTR(new_string,in_delimiter);
    IF value_pos = 0 THEN
      new_string := NULL;
    ELSE
      new_string := SUBSTR(new_string,value_pos + 1);
    END IF;

  END LOOP;

  RETURN counter;

END get_occurance;

FUNCTION over_punch (opunch varchar2)
return varchar2
IS
/***************************************************************
*      HDS Information Systems
****************************************************************
*
* Name: OVER_PUNCH
*
* Author:       Dave Robinson
*
* Purpose:      Converts numbers that contain over punch
*               characters into real numbers.  Returns
*               either the converted number or null.
*
* Mod History:
*      Mapped from PAP into DTC by Wayne Xu on 2/10/1999
*
****************************************************************
***************************************************************/
        cnvt_result     varchar2(15);
begin
        select decode(substr(opunch,1,1),'{','+','}','-',
        decode(substr(opunch,-1,1),'{','+','}','-',
        decode(sign(trunc(ascii(substr(upper(opunch),1,1))/74)),1,'-',
        decode(sign(trunc(ascii(substr(upper(opunch),-1,1))/74)),1,'-','+'
        ))))||translate(upper(opunch),
        '{}ABCDEFGHIJKLMNOPQRSTUVWXYZ',
        '0012345678912345678900000000')
        into cnvt_result
        from dual;
        if to_number(cnvt_result)>=-10
        and to_number(cnvt_result) <=-1 then
                cnvt_result:=null;
        end if;
        return cnvt_result;
exception
        when others then
                cnvt_result:=null;
                return cnvt_result;
end over_punch;

/**************** Moved from dtc_global **************************/

/******************************************************************************
  This procedure assigns the value passed to the global package variable
  passed.
******************************************************************************/
PROCEDURE assign_global_value_char(      in_var_name   IN      VARCHAR2
                                , in_value      IN      VARCHAR2)
IS

  plsql_stmt VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
  exe_err     INTEGER;
  assign_cur_id INTEGER;

BEGIN

  IF in_value IS NULL THEN
    plsql_stmt := plsql_stmt||'NULL;';
  ELSE
    plsql_stmt := plsql_stmt||':char_value;';
  END IF;

  plsql_stmt := plsql_stmt||' END;';


  assign_cur_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.NATIVE);
  IF in_value IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(assign_cur_id,'char_value',in_value);
  END IF;

  exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
  DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_char;




PROCEDURE assign_global_value(    in_var_name   IN      VARCHAR2
                                , in_value      IN      NUMBER)
IS

  plsql_stmt    VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
  exe_err       INTEGER;
  assign_cur_id INTEGER;

BEGIN

  IF in_value IS NULL THEN
    plsql_stmt := plsql_stmt||'NULL;';
  ELSE
    plsql_stmt := plsql_stmt||TO_CHAR(in_value)||';';
  END IF;

  plsql_stmt := plsql_stmt||' END;';

  assign_cur_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

  exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
  DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value;




PROCEDURE assign_global_value_date(    in_var_name   IN      VARCHAR2
                                , in_value      IN      DATE)
IS

  plsql_stmt    VARCHAR2(1000) := 'BEGIN '||in_var_name||' := ';
  exe_err       INTEGER;
  assign_cur_id INTEGER;

BEGIN


  IF in_value IS NULL THEN
    plsql_stmt := plsql_stmt||'NULL;';
  ELSE
    plsql_stmt := plsql_stmt||'TO_DATE('''||TO_CHAR(in_value,'MM/DD/YYYY')||''',''MM/DD/YYYY'');';
  END IF;

  plsql_stmt := plsql_stmt||' END;';

  assign_cur_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(assign_cur_id,plsql_stmt,DBMS_SQL.V7);

  exe_err := DBMS_SQL.EXECUTE(assign_cur_id);
  DBMS_SQL.CLOSE_CURSOR(assign_cur_id);

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20100,'Assign Global Value--'||SQLERRM);

END assign_global_value_date;



/******************************************************************************
  This function returns the title of the current application title.
******************************************************************************/

/*********************** This is not used by DTC ******************************
FUNCTION get_application RETURN VARCHAR2 IS

  app_record dtc_cur.app_cur%ROWTYPE;

BEGIN

  OPEN dtc_cur.app_cur(dtc_global.application_id);
    FETCH dtc_cur.app_cur INTO app_record;
    IF dtc_cur.app_cur%NOTFOUND THEN
      CLOSE dtc_cur.app_cur;
      RETURN NULL;
    END IF;
  CLOSE dtc_cur.app_cur;

  RETURN app_record.application_code;

END get_application;
******************************************************************************/

/******************************************************************************
  This function returns the current organization name. (previously get_customer_name)
******************************************************************************/
FUNCTION get_organization_name RETURN VARCHAR2 IS

  org_record dtc_cur.org_cur%ROWTYPE;

BEGIN

  OPEN dtc_cur.org_cur(dtc_global.organization_id);
    FETCH dtc_cur.org_cur INTO org_record;
    IF dtc_cur.org_cur%NOTFOUND THEN
      CLOSE dtc_cur.org_cur;
      RETURN NULL;
    END IF;
  CLOSE dtc_cur.org_cur;

  RETURN org_record.org_name;

END get_organization_name;

/******************************************************************************
  This function returns the program name of the id passed
******************************************************************************/
FUNCTION get_program_name (in_org_prog_id IN dtc_org_prog.org_prog_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

  prog_record dtc_cur.prog_cur%ROWTYPE;

BEGIN

  OPEN dtc_cur.prog_cur(NVL(in_org_prog_id,dtc_global.org_prog_id));
    FETCH dtc_cur.prog_cur INTO prog_record;
    IF dtc_cur.prog_cur%NOTFOUND THEN
      CLOSE dtc_cur.prog_cur;
      RETURN NULL;
    END IF;
  CLOSE dtc_cur.prog_cur;

  RETURN prog_record.program_desc;

END get_program_name;


/******************************************************************************
  This function returns the campaign name of the id passed
******************************************************************************/
FUNCTION get_campaign_name (in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

  cam_record dtc_cur.cam_cur%ROWTYPE;

BEGIN

  OPEN dtc_cur.cam_cur(NVL(in_org_prog_cam_id,dtc_global.org_prog_cam_id));
    FETCH dtc_cur.cam_cur INTO cam_record;
    IF dtc_cur.cam_cur%NOTFOUND THEN
      CLOSE dtc_cur.cam_cur;
      RETURN NULL;
    END IF;
  CLOSE dtc_cur.cam_cur;

  RETURN cam_record.campaign_desc;

END get_campaign_name;


/******************************************************************************
  This function sets the DTC_GLOBAL.USER_ID and returns the username
******************************************************************************/

FUNCTION assign_user_id RETURN VARCHAR2 IS

  return_username VARCHAR2(30);

  CURSOR get_user IS
    SELECT
 APP_USER_ID
, USERNAME
    FROM
DTC_APP_USER
    WHERE
USERNAME = NVL(dtc_global.current_user,USER);

BEGIN
  OPEN get_user;
    FETCH get_user INTO
 dtc_global.user_id
, return_username;
    IF get_user%NOTFOUND THEN
      dtc_global.user_id := 0;
      return_username := 'UNKNOWN';
    END IF;
  CLOSE get_user;

  RETURN return_username;

END assign_user_id;


/******************************************************************************
  This function returns the form title of the module name passed.
******************************************************************************/

FUNCTION get_module_title(  in_mod_name VARCHAR2) RETURN VARCHAR2 IS

  mod_title VARCHAR2(50);

  CURSOR get_title (in_name IN VARCHAR2) IS
    SELECT MODULE_TITLE
    FROM   DTC_MODULE
    WHERE
   MODULE_NAME = in_name;

BEGIN

  OPEN get_title(in_mod_name);
    FETCH get_title INTO mod_title;
  CLOSE get_title;

  IF mod_title IS NULL THEN
    OPEN get_title('UNKNOWN');
      FETCH get_title INTO mod_title;
      IF get_title%NOTFOUND THEN
        mod_title := 'Module Title Unknown';
      END IF;
    CLOSE get_title;
  END IF;


  RETURN mod_title;

END get_module_title;

/******************************************************************************
  This function returns the current date in character format.
******************************************************************************/
FUNCTION get_char_date RETURN VARCHAR2 IS

BEGIN
  RETURN TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI');
END get_char_date;


/******************************************************************************
  This procedure gathers the entered/last updated infomation for the
  table and rowid passed.
******************************************************************************/
PROCEDURE get_entry_info(  in_table IN VARCHAR2
, in_rowid IN VARCHAR2
, out_created   OUT NUMBER
, out_create_dt OUT DATE
, out_lst_up_by OUT NUMBER
, out_lst_up_dt OUT DATE) IS

  cur_id   INTEGER := DBMS_SQL.OPEN_CURSOR;
  sql_stmt VARCHAR2(1000);
  exe_err   INTEGER;

  created_user NUMBER;
  create_dt DATE;
  last_upt_by NUMBER;
  last_upt_dt DATE;

BEGIN

  sql_stmt := 'SELECT '||
'  CREATED_BY'||
', CREATE_DATE'||
', LAST_UPDATED_BY'||
', LAST_UPDATE_DATE '||
'FROM '||in_table||' '||
'WHERE ROWID = '''||in_rowid||'''';

  DBMS_SQL.PARSE(cur_id,sql_stmt,DBMS_SQL.V7);

  DBMS_SQL.DEFINE_COLUMN(cur_id,1,created_user);
  DBMS_SQL.DEFINE_COLUMN(cur_id,2,create_dt);
  DBMS_SQL.DEFINE_COLUMN(cur_id,3,last_upt_by);
  DBMS_SQL.DEFINE_COLUMN(cur_id,4,last_upt_dt);

  exe_err := DBMS_SQL.EXECUTE_AND_FETCH(cur_id,FALSE);

  DBMS_SQL.COLUMN_VALUE(cur_id,1,created_user);
  DBMS_SQL.COLUMN_VALUE(cur_id,2,create_dt);
  DBMS_SQL.COLUMN_VALUE(cur_id,3,last_upt_by);
  DBMS_SQL.COLUMN_VALUE(cur_id,4,last_upt_dt);

  DBMS_SQL.CLOSE_CURSOR(cur_id);

  out_created   := created_user;
  out_create_dt := create_dt;
  out_lst_up_by := last_upt_by;
  out_lst_up_dt := last_upt_dt;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END get_entry_info;

/******************************************************************************
  This function returns the value of the Global package variable passed.
******************************************************************************/
FUNCTION get_global_value(in_variable   IN   VARCHAR2)
RETURN VARCHAR2 IS
  plsql_text    VARCHAR2(500);
  cur_id        INTEGER := DBMS_SQL.OPEN_CURSOR;
  exe_err       INTEGER;
BEGIN
  plsql_text := 'DECLARE m_char VARCHAR2(255); m_date DATE;' ||
                'BEGIN m_char := TO_CHAR('|| in_variable || '); ' ||
                      'BEGIN m_date := TO_DATE('||in_variable||');' ||
                      'DTC_GLOBAL.GLOBAL_VAR_VALUE := TO_CHAR('||in_variable||',''MM/DD/YYYY''); ' ||
                      'EXCEPTION WHEN OTHERS THEN DTC_GLOBAL.GLOBAL_VAR_VALUE := '||in_variable||'; END; ';

  plsql_text := plsql_text ||' END;';
  DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
  exe_err := DBMS_SQL.EXECUTE(cur_id);
  DBMS_SQL.CLOSE_CURSOR(cur_id);

  return DTC_GLOBAL.GLOBAL_VAR_VALUE;
EXCEPTION
  WHEN OTHERS THEN -- Was a Character Global
  BEGIN
    plsql_text := ' BEGIN DTC_GLOBAL.GLOBAL_VAR_VALUE := ' || in_variable || ';' ||
                  ' END;';
    DBMS_SQL.PARSE(cur_id,plsql_text,dbms_sql.v7);
    exe_err := DBMS_SQL.EXECUTE(cur_id);
    DBMS_SQL.CLOSE_CURSOR(cur_id);

    RETURN DTC_GLOBAL.GLOBAL_VAR_VALUE;

  EXCEPTION
    WHEN OTHERS THEN
      RETURN '**ERROR**' || SQLERRM;
  END;
END get_global_value;


/******************************************************************************
  This function returns the table and column name of the table_def_col_id passed
  (used in order by in forms)
******************************************************************************/
FUNCTION get_table_column_name
  (in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

  return_table_column_name VARCHAR2(61);

  CURSOR table_column_cur IS
    SELECT table_name||'.'||column_name
      FROM dtc_table_def,dtc_table_def_col
     WHERE dtc_table_def.table_def_id = dtc_table_def_col.table_def_id
       AND dtc_table_def_col.table_def_col_id = in_table_def_col_id;

BEGIN
  OPEN table_column_cur;
    FETCH table_column_cur INTO return_table_column_name;
    IF table_column_cur%NOTFOUND THEN
      return_table_column_name := NULL;
    END IF;
  CLOSE table_column_cur;

  RETURN return_table_column_name;

END get_table_column_name;


/******************************************************************************
  This function returns the organization*program*campaign name of the ids passed
   (Used in order_by in forms)
******************************************************************************/
FUNCTION get_org_prog_cam_name
  (in_organization_id IN dtc_organization.organization_id%TYPE DEFAULT NULL,
   in_org_prog_id     IN dtc_org_prog.org_prog_id%TYPE         DEFAULT NULL,
   in_org_prog_cam_id IN dtc_org_prog_cam.org_prog_cam_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

  return_org_prog_cam_name VARCHAR2(555);
  v_org_name               dtc_organization.org_name%type;
  v_program_desc           dtc_org_prog.program_desc%type;
  v_campaign_desc          dtc_org_prog_cam.campaign_desc%type;


  CURSOR organization_cur IS
    select org_name
      from dtc_organization
     where organization_id = in_organization_id;

  CURSOR org_prog_cur IS
    select program_desc
      from dtc_org_prog
     where org_prog_id = in_org_prog_id;

  CURSOR org_prog_cam_cur IS
    select campaign_desc
      from dtc_org_prog_cam
     where org_prog_cam_id = in_org_prog_cam_id;

BEGIN

  if in_organization_id is not null then
    open organization_cur;
    fetch organization_cur into v_org_name;
    if organization_cur%NOTFOUND then
      v_org_name := NULL;
    end if;
    return_org_prog_cam_name := v_org_name;
    close organization_cur;

    if in_org_prog_id is not null then
      open org_prog_cur;
      fetch org_prog_cur into v_program_desc;
      if org_prog_cur%NOTFOUND then
        v_program_desc := NULL;
      else
        return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_program_desc;
      end if;
      close org_prog_cur;

      if in_org_prog_cam_id is not null then
        open org_prog_cam_cur;
        fetch org_prog_cam_cur into v_campaign_desc;
        if org_prog_cam_cur%NOTFOUND then
          v_campaign_desc := NULL;
        else
          return_org_prog_cam_name := return_org_prog_cam_name||'*'||v_campaign_desc;
        end if;
        close org_prog_cam_cur;
      end if;
    end if;
  else
    return_org_prog_cam_name := NULL;
  end if;

  return return_org_prog_cam_name;

END get_org_prog_cam_name;


/******************************************************************************
  This function returns the table title of the table_def_id passed
  (used in order by in forms)
******************************************************************************/
FUNCTION get_table_title
  (in_table_def_col_id IN dtc_table_def_col.table_def_col_id%TYPE DEFAULT NULL)
RETURN VARCHAR2 IS

  return_table_title dtc_table_def.title%type;

  CURSOR table_cur IS
    SELECT t.title
      FROM dtc_table_def t, dtc_table_def_col c
     WHERE c.table_def_col_id = in_table_def_col_id
       and t.table_def_id = c.table_def_id;

BEGIN
  OPEN table_cur;
    FETCH table_cur INTO return_table_title;
    IF table_cur%NOTFOUND THEN
      return_table_title := NULL;
    END IF;
  CLOSE table_cur;

  RETURN return_table_title;

END get_table_title;


/******************************************************************************
  This function calculates the business (working) days between the two days
  It doesn't take into account the Holidays.
******************************************************************************/
FUNCTION calc_business_days(P_start_date date,
                            P_end_date   date)
RETURN NUMBER IS

  begin_date date;
  thru_date  date;
  weekend    number;
  days       number;
  maxweekend number;
  weekday    number;

BEGIN
  IF P_start_date is null or P_end_date is null THEN
     return(null);
  ELSIF TRUNC(P_start_date) > TRUNC(P_end_date) THEN
     return( -1 );
  ELSE
     begin_date := P_start_date;
     thru_date  := P_end_date;
  END IF;

  days := TRUNC(thru_date) - TRUNC(begin_date);
  IF days = 0 THEN
     return(0);
  ELSE
     weekday := to_char(begin_date,'D');
     days := days + 0;
     IF mod(days,7) = 0 THEN
        weekend := TRUNC(days / 7) * 2;
     ELSE
        MAXWEEKEND := (TRUNC(days / 7) + 1) * 2;
          IF weekday = 1 THEN
             weekend := maxweekend - 1;
          ELSE
             IF (weekday + days) = (TRUNC(days / 7) + 1) * 7 + 1 THEN
                 weekend := maxweekend - 1;
             ELSE
                IF (weekday + days) < (trunc(days / 7) + 1) * 7 + 1 THEN
                   weekend := maxweekend - 2;
                ELSE
                   weekend := maxweekend;
                END IF;
             END IF;
          END IF;
     END IF;
     RETURN ((days - weekend) * SIGN(TRUNC(P_end_date) - TRUNC(P_start_date)));
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN (0);
END; -- calc_business_days

/***************************************************************************
  This function is used by the Job Queue screen DTCFM560 for getting the description
  of the opc_item.
******************************************************************************/

FUNCTION dtc_opc_item_desc_sort(i_opc_item_id IN NUMBER) RETURN VARCHAR2
IS
opc_item_desc  VARCHAR2(255);
BEGIN
  DECLARE
   item_desc dtc_opc_item.opc_item_desc%TYPE;
  BEGIN
   SELECT opc_item_desc
    INTO item_desc
    FROM dtc_opc_item
     WHERE opc_item_id = i_opc_item_id
      AND sysdate between start_date and nvl(end_date,sysdate);

 RETURN item_desc;
  EXCEPTION
  WHEN OTHERS THEN NULL;
   RETURN ('OPC ITEM DESCRIPTION NOT FOUND');
  END;
END dtc_opc_item_desc_sort;
7)Conversion Cvs to xml code
SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
  v_rec_count   NUMBER := 0;

  --File Variables
  v_file_out       UTL_FILE.FILE_TYPE;
  v_file_in        UTL_FILE.FILE_TYPE;
  l_in_path        varchar2(100) := '/u02/apps/dtc/incoming_data/lc_5050/';
  l_out_path       varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';
  v_input_buffer   varchar2(4000);
  v_issuedate      varchar2(20);
  v_char           clob;
  v_firstag varchar2(30):='<Document_Revision_Request>';
  v_lasttag varchar2(30):='</Document_Revision_Request>';
         
BEGIN
  dbms_output.put_line('First Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
  dtc_global.org_prog_id := 5050;

  v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_PHS_20150917.csv','R');
  UTL_FILE.get_LINE(v_file_in,v_input_buffer);
  IF  utl_file.is_open(v_file_in) then
  LOOP
  begin
 
    UTL_FILE.get_LINE(v_file_in,v_input_buffer);
    v_rec_count := v_rec_count + 1;
 
    v_issuedate  :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);
 
    select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
                          XMLElement("Document_Revision_ID",v_rec_count),
                          XMLElement("Payment_Code",'CHK'),
                          XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
                          XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
                          XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
                          XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
                          XMLElement("Revised_Document_Status",'V'),
                          XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
                          XMLElement("Document_Status_Revision_Description")
                    ))) into v_char from dual;
   
        IF v_char is not null then
         IF v_rec_count=1 then
         v_char:=v_firstag||v_char;
         END IF;
        END IF;
                 
   IF NOT utl_file.is_open(v_file_out) then
   v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_PHS_20150917.xml', 'W');
   END IF;
     UTL_FILE.PUT_LINE(v_file_out,v_char);
  EXCEPTION
 
    WHEN NO_DATA_FOUND THEN
      IF ( UTL_FILE.is_open(v_file_in) ) THEN
         v_char:=null;
         v_char:=v_char||v_lasttag;
        UTL_FILE.PUT_LINE(v_file_out,v_char);
        UTL_FILE.fclose(v_file_in);
      END IF;
       IF ( UTL_FILE.is_open(v_file_out) ) THEN
        UTL_FILE.fclose(v_file_out);
      END IF;
     EXIT;

  end;
  END LOOP;
  END IF;
   dbms_output.put_line('No of records converted:' ||v_rec_count);

  UTL_FILE.FCLOSE(v_file_in);
  UTL_FILE.FCLOSE(v_file_out);
 
  dbms_output.put_line('First Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
  dbms_output.put_line('Second Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
  v_rec_count:=0;

  v_file_in := UTL_FILE.FOPEN(l_in_path,'VOID_MHS_FRS_20150917.csv','R');
  UTL_FILE.get_LINE(v_file_in,v_input_buffer);
  IF  utl_file.is_open(v_file_in) then
  LOOP
  begin
 
    UTL_FILE.get_LINE(v_file_in,v_input_buffer);
    v_rec_count := v_rec_count + 1;
 
    v_issuedate  :=REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 5);
 
    select to_clob(XMLAgg(XMLElement("Document_Revision_Request",
                          XMLElement("Document_Revision_ID",v_rec_count),
                          XMLElement("Payment_Code",'CHK'),
                          XMLElement("Original_Recipient_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 2)),
                          XMLElement("Original_Document_Date",to_char(to_date(v_issuedate,'YYYYMMDD'),'MM/DD/YYYY')),
                          XMLElement("Original_Document_Number",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 3)),
                          XMLElement("Original_Document_Amount",REGEXP_SUBSTR(v_input_buffer, '[^,]+', 1, 4)),
                          XMLElement("Revised_Document_Status",'V'),
                          XMLElement("Document_Status_Revision_Reason_Code",'VR9'),
                          XMLElement("Document_Status_Revision_Description")
                    ))) into v_char from dual;
   
        IF v_char is not null then
         IF v_rec_count=1 then
         v_char:=v_firstag||v_char;
         END IF;
        END IF;
                 
   IF NOT utl_file.is_open(v_file_out) then
   v_file_out := utl_file.fopen(l_out_path,'VOID_MHS_FRS_20150917.xml', 'W');
   END IF;
     UTL_FILE.PUT_LINE(v_file_out,v_char);
  EXCEPTION
 
    WHEN NO_DATA_FOUND THEN
      IF ( UTL_FILE.is_open(v_file_in) ) THEN
         v_char:=null;
         v_char:=v_char||v_lasttag;
        UTL_FILE.PUT_LINE(v_file_out,v_char);
        UTL_FILE.fclose(v_file_in);
      END IF;
       IF ( UTL_FILE.is_open(v_file_out) ) THEN
        UTL_FILE.fclose(v_file_out);
      END IF;
     EXIT;

  end;
  END LOOP;
  END IF;
   dbms_output.put_line('No of records converted:' ||v_rec_count);

  UTL_FILE.FCLOSE(v_file_in);
  UTL_FILE.FCLOSE(v_file_out);

    dbms_output.put_line('Second Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
    IF ( UTL_FILE.is_open(v_file_in) ) THEN
      UTL_FILE.fclose(v_file_in);
         dbms_output.put_line( 'file_in'||SQLERRM);
    END IF;
    RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
    dbms_output.put_line(SQLERRM);
END;

/

7)Write to txt file

SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;
DECLARE
  v_rec_count   NUMBER := 0;
  v_prog_recp_cnt NUMBER:=0;
  v_terminate_cnt NUMBER:=0;
  v_call_cnt NUMBER:=0;
  v_class_cnt NUMBER:=0;
  v_out_msg  varchar2(4000);
  v_term_evnt_exp  EXCEPTION;

  --File Variables
  v_file            UTL_FILE.FILE_TYPE;
  l_dir_path        varchar2(100) := '/u02/apps/dtc/outgoing_data/lc_5050/';

  cursor cur_pat is
    select opcr.org_prog_cam_recipient_id opcr_id,opcr.recipient_pk_id,
    dtc_report_util.get_attr_usage_val(8466,opcr.org_prog_cam_recipient_id)coachid ,
    email.comm_value email,
    opcr.create_date create_date
    from dtc_org_prog_cam_recipient opcr, dtc_recipient_comm_value email
    where opcr.recipient_pk_id=email.recipient_pk_id
    and opcr.org_prog_cam_id=7103
    and opcr.end_date is null
    and email.comm_type_id=76
    and (email.comm_value like '%VOXIVA%' or email.comm_value like '%TEST%')
    and email.end_date is null
    ;
  CURSOR get_event_chk(in_opcr_id number)
  IS
  SELECT opcr_event_id
  FROM dtc_opcr_event
  WHERE org_prog_cam_recipient_id = in_opcr_id
  AND  end_date IS NULL
  AND  current_status <> 'CLOSED TERMINATED';
  v_opcr_event NUMBER(10);
BEGIN
   dbms_output.put_line('DML Script Start Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
   dtc_global.org_prog_id := 5050;
    FOR rec in cur_pat
    LOOP
     IF NOT utl_file.is_open(v_file) then
     v_file := utl_file.fopen(l_dir_path,'dtc_pmo_5400_out_file.csv', 'W');
     END IF;
     v_rec_count := v_rec_count + 1;
    --(1) PART Please create an output file with the following columns so we can send to the
      IF v_rec_count=1 then
      utl_file.put_line(v_file,'COACH ID'||','||'EMAIL'||','||'MEMBER CREATE DATE');
      END IF;
      utl_file.put_line(v_file,rec.coachid||','||rec.email||','||TO_CHAR(rec.create_date,'MM/DD/YYYY'));
 --(2)PART For these recipients we need do the following:
--dbms_output.put_line(rec.opcr_id);
      Update dtc_org_prog_cam_recipient
      set   end_date=trunc(sysdate)
      where org_prog_cam_recipient_id=rec.opcr_id
      and org_prog_cam_id=7103;
      v_prog_recp_cnt:=v_prog_recp_cnt+sql%rowcount;
      v_opcr_event:=null;
     OPEN get_event_chk(rec.opcr_id);
     LOOP
      FETCH get_event_chk into v_opcr_event;
      exit when  get_event_chk%notfound;
      dtc_dupe_process_pkg.terminate_event(v_opcr_event, v_out_msg, 'TERMINATED AS PER PMO-5400');
      v_terminate_cnt:=v_terminate_cnt+1;
     END LOOP;
     CLOSE get_event_chk;
      IF v_out_msg<>'0' THEN
       RAISE v_term_evnt_exp;
      END IF;
      UPDATE dtc_opcret_call_attempt
      SET    end_date=sysdate
      WHERE org_prog_cam_recipient_id=rec.opcr_id;
   
      v_call_cnt:=v_call_cnt+sql%rowcount;

      --Class Term and new class creation:
      dtc_lc_5077_util_pkg.end_dt_cur_cls_and_ins_class(8283,rec.opcr_id);

      v_class_cnt:=v_class_cnt+sql%rowcount;
     
    END LOOP;
    UTL_FILE.FCLOSE(v_file);
    DBMS_OUTPUT.PUT_LINE('No of records Updated Prog_cam_recipient:'||v_prog_recp_cnt );
    DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_event:'||v_terminate_cnt );
    DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcret_call_attempt:'||v_call_cnt );
    DBMS_OUTPUT.PUT_LINE('No of records Updated dtc_opcr_class:'||v_class_cnt );
    dbms_output.put_line('DML Script End Time ' ||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
    commit;
 EXCEPTION
 WHEN v_term_evnt_exp THEN
   RAISE_APPLICATION_ERROR(-20012,SQLERRM);
 WHEN OTHERS THEN
    IF ( UTL_FILE.is_open(v_file) ) THEN
      UTL_FILE.fclose(v_file);
    END IF;
    RAISE_APPLICATION_ERROR(-20202, 'Error: ' || SQLERRM);
END;  
/  

Generating txt for to .csv files

declare
Procedure upd_card_type ( p_file_name IN VARCHAR2, p_card_type IN varchar2 ) IS
f1          UTL_FILE.FILE_TYPE;
f2          UTL_FILE.FILE_TYPE;
f3          UTL_FILE.FILE_TYPE;
v_file_name1 varchar2(100) ;
v_file_name2 varchar2(100) ;
v_file_name3 varchar2(100) ;
v_file_dir   varchar2(300) := '/u02/apps/dtc/incoming_data/lc_5050/';
v_record     varchar2(500);
v_rownum     number := 0 ;
v_cnt        number;
v_random_no  dtc_opc_random_no.RANDOM_NO%type ;
begin
v_cnt:=0;
DBMS_OUTPUT.put_line ('DML Script Start Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
v_file_name1 := p_file_name ;
v_file_name2 := replace(v_file_name1, '.txt', '_reject.csv');
v_file_name3 := replace(v_file_name1, '.txt', '_log.csv');
f1 := UTL_FILE.FOPEN(v_file_dir, v_file_name1 , 'r', 4000);
f2 := UTL_FILE.FOPEN(v_file_dir, v_file_name2 , 'w', 4000);
f3 := UTL_FILE.FOPEN(v_file_dir, v_file_name3 , 'w', 4000);
dtc_global.org_prog_id := 5050 ;
  LOOP
     BEGIN
       v_record := null ;
       utl_file.get_line(f1,v_record);
     
       v_random_no := null ;
       if substr(v_record, 1, 4) = 'TRLR' then
          EXIT ;
       END IF;
       v_random_no := substr(v_record, 1, 9);
     
      for  rec IN(select count(1) cnt from dtc_opc_random_no
                    WHERE  random_no =v_random_no
                    AND    attribute01='IN OFFICE'
                    AND    org_prog_cam_id=6967)
        LOOP
              v_cnt:=v_cnt+rec.cnt;
       END LOOP;
       
       if SQL%ROWCOUNT = 1 then
        v_rownum := v_rownum + 1 ;
       else
         utl_file.put_line(f2, v_random_no || ','||p_card_type ||', Rows updated = '||SQL%ROWCOUNT);
       end if;
   
       if mod(v_rownum, 5000) = 0 then
          commit;
          utl_file.put_line(f3, 'Record processed = '||v_rownum );
       end if;
     EXCEPTION
       WHEN  no_data_found THEN
            EXIT ;
     END ;
  END LOOP;
    DBMS_OUTPUT.put_line('No Of records CNT:'|| v_cnt);
    DBMS_OUTPUT.put_line('No Of Update records:'||v_rownum);
    DBMS_OUTPUT.put_line('DML Script End Time ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
  utl_file.fclose(f1);
  utl_file.fclose(f2);
  utl_file.fclose(f3);
  commit;
end;
begin
   upd_card_type('IDs_sq9416_RESPIMAT6967LS_grp6967_cnt117335_typePATIENTSTARTERKIT_10152014.txt', 'PATIENT STARTER KIT' );
end ;
/

Select operators or set operators

Also called a set operators

Union:
This is combines the results of two queries and returns the set of distinct rows returned by either query.
Union All:
This is combines the results of two queries and returns all rows returned by either query,including  duplicates.
Intersect:

This combines the results of two queries and returns the set of distinct rows returned by both queries

Minus:

This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second

 
Other select operators

(+)Denotes that the preceding column is an outer join

* wildcard operator.Equals all columns in a select statement 

Prior Denotes a parent-child relation ship operator in prior

Sample Emp Tables Backup

CREATE TABLE DEPT ( DEPTNO  NUMBER(2) NOT NULL primary key,
                    DNAME   CHAR(14),
                    LOC     CHAR(13));

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               CHAR(10),
 JOB                 CHAR(9),
 MGR                 NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) NOT NULL,
 CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
 CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE BONUS (
ENAME               CHAR(10),
JOB                 CHAR(9),
SAL                 NUMBER,
COMM                NUMBER);

CREATE TABLE SALGRADE
 ( GRADE               NUMBER,
   LOSAL               NUMBER,
   HISAL               NUMBER);

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

Q: How to enter a single quotation mark in Oracle?

Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.

SELECT 'test single quote''' from dual;

The output of the above statement would be:
test single quote'

Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.

This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.

Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.

The same example inside PL/SQL I will use like following:

DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;

The output above is same as the Method 1.

Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:

SELECT 'test single quote'||CHR(39) FROM dual;

The output is same in all the cases.

Dynamic Sql

DECLARE      
      vl_str_sql VARCHAR2(255);
       vl_cnt   VARCHAR2(20);
BEGIN
     vl_str_sql  := 'SELECT count(*) FROM emp';
     EXECUTE IMMEDIATE vl_str_sql  INTO vl_cnt;
     dbms_output.put_line(vl_cnt);
END;


DECLARE
  TYPE CUR_TYP IS REF CURSOR;
  c_cursor   CUR_TYPE;
  emp_rec   emp%ROWTYPE;
  v_query   VARCHAR2(255);
BEGIN
  v_query := 'SELECT * FROM emp';

  OPEN c_cursor FOR v_query;
  LOOP
    FETCH c_cursor INTO emp_rec;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(emp_rec.empno);
  END LOOP;
  CLOSE c_cursor;
END;

DECLARE 
TYPE cur_typ IS REF CURSOR;
  c_cursor     CUR_TYPE;
  emp_rec     emp%ROWTYPE;
  v_query      VARCHAR2(255);
  vl_manager VARCHAR2(3) := 'MANAGER';
BEGIN
  v_query := 'SELECT * FROM emp WHERE job = :job';
  OPEN c_cursor FOR v_query USING vl_manager;
  LOOP
    FETCH c_cursor INTO emp_rec;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(emp_rec.job);
  END LOOP;
  CLOSE c_cursor;
END;

INDEX

What is index?
An index  is a pointer for to a location of data.The purpose of index is to make sql queries to run faster
 syntax:-

CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[, column_name...])
TABLESPACE table_space;

 

Normal Index?