3 Replies Latest reply: Sep 21, 2011 3:21 AM by 867407 RSS

    database Migration issue

    867407
      hi all
      i need your help

      i have completed my Database Migration from sql server 2008 to Oracle 11g

      i got a very diffrent kind of issue:
      Mgrated database
      SQL SERVER 2008 to ORACLE 11g
      using SQL DEVELOPER 3

      and when completed with all compilation and cross check

      i generated DATABASE Script

      by

      Database export Wizard
      Format: insert
      Line Terminator: enviroment default
      Save :as single file
      Encoding: Cp1252
      <<<<what is the meaning of these attributes like format,encoding.....etc....>>>>>>>
      and recreate in Oracle 11g,10g (on another machine)

      where i got so many errors

      most of time :

      Error(48,13): PLS-00905: object DBO_PMS_DB.SQLSERVER_UTILITIES is invalid

      SQLSERVER_UTILITIES is invalid

      how to resolve this issue?

      please help me out

      thanks
      Rajneesh
        • 1. Re: database Migration issue
          867407
          i got My solution

          h3. create a package from this code :

          h3. and compile this :

          AND all done

          CODE:::::::::::::::::::::

          i am putting the Code in parts because this page can support
          MAXIMUM 30000 caharacter

          h1. part 1
          ---------
          CREATE PACKAGE sqlserver_utilities
          AS
          identity NUMBER(10);
          trancount NUMBER(10):=0;
          var_number NUMBER(10):=0;

          FUNCTION convert_(p_dataType IN VARCHAR2, p_expr IN VARCHAR2, p_style IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
          FUNCTION year_(p_date_str IN VARCHAR2) RETURN NUMBER;
          FUNCTION stuff(p_expr VARCHAR2, p_startIdx NUMBER, p_len NUMBER, p_replace_expr VARCHAR2) RETURN VARCHAR2;
          PROCEDURE incrementTrancount;
          FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN VARCHAR2) RETURN DATE;
          FUNCTION isdate(p_expr IN VARCHAR2) RETURN NUMBER;
          FUNCTION stats_date(p_table IN VARCHAR2, p_index IN VARCHAR2) RETURN DATE;
          FUNCTION rand(p_seed NUMBER DEFAULT NULL) RETURN NUMBER;
          FUNCTION to_base(p_dec NUMBER, p_base NUMBER) RETURN VARCHAR2;
          FUNCTION patindex(p_pattern IN VARCHAR2, p_expr IN VARCHAR2) RETURN NUMBER;
          FUNCTION datediff(p_datepart VARCHAR2, p_start_date_str VARCHAR2, p_end_date_str VARCHAR2) RETURN NUMBER;
          FUNCTION day_(p_date_str IN VARCHAR2) RETURN NUMBER;
          FUNCTION ident_incr(p_sequence IN VARCHAR2) RETURN NUMBER;
          FUNCTION isnumeric(p_expr IN VARCHAR2) RETURN NUMBER;
          FUNCTION hex(p_num VARCHAR2) RETURN VARCHAR2;
          PROCEDURE decrementTrancount;
          FUNCTION difference(p_expr1 IN VARCHAR2, p_expr2 IN VARCHAR2) RETURN NUMBER;
          FUNCTION datepart(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2) RETURN NUMBER;
          FUNCTION radians(p_degree IN NUMBER) RETURN NUMBER;
          FUNCTION reverse_(p_expr IN VARCHAR2) RETURN VARCHAR2;
          FUNCTION parsename(p_object_name IN VARCHAR2, p_object_piece IN NUMBER) RETURN VARCHAR2;
          FUNCTION round_(p_expr NUMBER, p_len NUMBER, p_function NUMBER DEFAULT 0) RETURN NUMBER;
          FUNCTION month_(p_date_str IN VARCHAR2) RETURN NUMBER;
          PROCEDURE commit_transaction;
          FUNCTION pi RETURN NUMBER;
          PROCEDURE resetTrancount;
          FUNCTION oct(p_num VARCHAR2) RETURN VARCHAR2;
          FUNCTION str(p_expr IN NUMBER, p_len IN NUMBER DEFAULT 10, p_scale IN NUMBER DEFAULT 0) RETURN VARCHAR2;
          FUNCTION degrees(p_angle_radians IN NUMBER) RETURN NUMBER;
          FUNCTION datename(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2) RETURN VARCHAR2;
          FUNCTION ident_seed(p_sequence IN VARCHAR2) RETURN NUMBER;
          FUNCTION quotename(p_str IN VARCHAR2, p_delimiters IN VARCHAR2 DEFAULT '[]') RETURN VARCHAR2;
          FUNCTION str_to_date(p_date_expr IN VARCHAR2) RETURN DATE;
          FUNCTION fetch_status(p_cursorfound IN BOOLEAN) RETURN NUMBER;
          END sqlserver_utilities;CREATE PACKAGE BODY sqlserver_utilities AS
          FUNCTION str_to_date(p_date_expr IN VARCHAR2)
          RETURN DATE
          IS
          temp_val NUMBER;
          temp_exp VARCHAR2(50);
          format_str VARCHAR2(50) := NULL;
          BEGIN
          IF p_date_expr IS NULL THEN
          RETURN NULL;
          END IF;

          temp_exp := TRIM(p_date_expr);

          -- only for 10g
          IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
          IF REGEXP_INSTR(temp_exp,
          '^\{d[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})''\}$') = 1 THEN -- ISO861 format
          -- e.g. {d '2004-05-23' }
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^\{d[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})''\}$',
          '\1-\2-\3');
          format_str := 'YYYY-MM-DD';
          ELSIF REGEXP_INSTR(temp_exp,
          '^\{t[[:space:]]*''([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
          1, 1, 0, 'i') = 1 THEN -- ISO861 format
          -- e.g. { t '14:25:10.487' }
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^\{t[[:space:]]*''([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
          TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' \1:\2:\3');
          format_str := 'YYYY-MM-DD HH24:MI:SS';
          ELSIF REGEXP_INSTR(temp_exp,
          '^\{ts[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})[[:space:]]*' ||
          '([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
          1, 1, 0, 'i') = 1 THEN -- ISO861 format
          -- e.g. { ts '2005-05-23 14:25:10'}
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^\{ts[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})[[:space:]]*' ||
          '([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
          '\1-\2-\3 \4:\5:\6');
          format_str := 'YYYY-MM-DD HH24:MI:SS';
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})T([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?$') = 1 THEN -- ISO861 format
          -- e.g. 2004-05-23T14:25:10.487
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})T([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?$',
          '\1-\2-\3 \4:\5:\6');
          format_str := 'YYYY-MM-DD HH24:MI:SS';
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{1,2})[[:space:]]*(am|pm)$',
          1, 1, 0, 'i') = 1 THEN -- time format
          -- e.g. 4PM or 4 pm
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{1,2})[[:space:]]*(am|pm)$',
          TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' \1\2', 1, 1, 'i');
          format_str := 'YYYY-MM-DD HH12' || UPPER(REGEXP_SUBSTR(temp_exp, '(am|pm)$', 1, 1, 'i'));
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{1,2}):([[:digit:]]{2})(:[[:digit:]]{2})?([\.:][[:digit:]]{1,3})?[[:space:]]*(am|pm)?$',
          1, 1, 0, 'i') = 1 THEN -- time format
          -- e.g. 14:30 or 14:30:20.9 or 4:30:10 PM
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{1,2})(:[[:digit:]]{2})(:[[:digit:]]{2})?([\.:][[:digit:]]{1,3})?[[:space:]]*(am|pm)?$',
          TO_CHAR(SYSDATE, 'YYYY-MM-DD') || '\1\2\3\5', 1, 1, 'i');
          format_str := 'YYYY-MM-DD HH24:MI:SS';
          IF REGEXP_INSTR(temp_exp, '(am|pm)$', 1, 1, 0, 'i') <> 0 THEN
          format_str := REPLACE(format_str, 'HH24', 'HH12') || UPPER(REGEXP_SUBSTR(temp_exp, '(am|pm)$', 1, 1, 'i'));
          END IF;
          ELSIF REGEXP_INSTR(temp_exp, '^([[:digit:]]{4})$') = 1 THEN -- unseparated string format
          -- 4 digits is interpreted as year, century must be specified
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{4})$',
          '(01) 01 \1');
          format_str := '(DD) MM YYYY';
          ELSIF REGEXP_INSTR(temp_exp, '^[[:digit:]]{6,8}$') = 1 THEN -- unseparated string format
          IF LENGTH(temp_exp) = 6 THEN
          format_str := 'YYYYMMDD';
          -- default two-digit year cutoff is 2050 i.e.
          -- if the two digit year is greater than 50 the century prefix is interpreted as 19 otherwise it is 20.
          IF TO_NUMBER(SUBSTR(temp_exp, 1, 2)) > 50 THEN
          temp_exp := '19' || temp_exp;
          ELSE
          temp_exp := '20' || temp_exp;
          END IF;
          ELSE
          format_str := 'YYYYMMDD';
          END IF;
          ELSIF REGEXP_INSTR(temp_exp, '[-/\\.]') = 0 THEN -- alphanumeric date format
          IF REGEXP_INSTR(temp_exp,
          '^([[:alpha:]]+)[[:space:]]*,?[[:space:]]*([[:digit:]]{4})$') = 1 THEN
          -- e.g. APRIL, 1996 or APR 1996
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:alpha:]]+)[[:space:]]*,?[[:space:]]*([[:digit:]]{4})$',
          '(01) \1 \2');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:alpha:]]+)[[:space:]]+([[:digit:]]{1,2})?,?[[:space:]]+([[:digit:]]{2,4})$') = 1 THEN
          -- e.g. APRIL 15, 1996 or APR 15 96
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:alpha:]]+)[[:space:]]+([[:digit:]]{1,2})?,?[[:space:]]+([[:digit:]]{2,4})$',
          '(\2) \1 \3');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:alpha:]]+)[[:space:]]+([[:digit:]]{2,4})([[:space:]]+)?([[:digit:]]{1,2})?$') = 1 THEN
          -- e.g. APRIL 1996 or APRIL 1996 15
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:alpha:]]+)[[:space:]]+([[:digit:]]{2,4})([[:space:]]+)?([[:digit:]]{1,2})?$',
          '(\4) \1 \2');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+),?[[:space:]]+([[:digit:]]{2,4})$') = 1 THEN
          -- e.g. 15 APR, 1996 or 15 APR 96 or APRIL 1996
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+),?[[:space:]]+([[:digit:]]{2,4})$',
          '(\1) \2 \3');
          temp_exp := REPLACE(temp_exp, ',', '');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{1,2})?[[:space:]]+([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)$') = 1 THEN
          -- e.g. 15 1996 APRIL or 1996 APR
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{1,2})?[[:space:]]+([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)$',
          '(\1) \3 \2');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)([[:space:]]+)?([[:digit:]]{1,2})?$') = 1 THEN
          -- e.g. 1996 APRIL 15 or 1996 APR
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)([[:space:]]+)?([[:digit:]]{1,2})?$',
          '(\4) \2 \1');
          ELSIF REGEXP_INSTR(temp_exp,
          '^([[:digit:]]{2,4})[[:space:]]+([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+)$') = 1 THEN
          -- e.g. 1996 15 APRIL or 1996 APR
          temp_exp := REGEXP_REPLACE(temp_exp,
          '^([[:digit:]]{2,4})[[:space:]]+([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+)$',
          '(\2) \3 \1');
          END IF;
          • 2. Re: database Migration issue
            867407
            h2. part 2
            -------

            temp_exp := REPLACE(temp_exp, '()', '(1)');
            IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) < 100 THEN
            IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) > 50 THEN
            temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '19' || '\1');
            ELSE
            temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '20' || '\1');
            END IF;
            END IF;
            format_str := '(DD) MON YYYY';
            ELSIF REGEXP_INSTR(temp_exp, '[-/\\.]') <> 0 THEN -- numeric date format
            -- require the setting for SET FORMAT to determine the interpretation of the numeric date format,
            -- default is mdy
            IF REGEXP_INSTR(temp_exp,
            -- e.g. 4/15/1996 or 15/4/1996 or 4/96/15
            '^([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})$') = 1 THEN
            temp_exp := REGEXP_REPLACE(temp_exp,
            '^([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})$',
            '\1/\2/\3');
            ELSIF REGEXP_INSTR(temp_exp,
            '^([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})$') = 1 THEN
            -- e.g. 15/96/4
            temp_exp := REGEXP_REPLACE(temp_exp,
            '^([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})$',
            '\1/\3/\2');
            ELSIF REGEXP_INSTR(temp_exp,
            '^([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})$') = 1 THEN
            -- e.g. 1996/4/15 or 1996/15/4
            temp_exp := REGEXP_REPLACE(temp_exp,
            '^([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})$',
            '\2/\3/\1');
            END IF;

            -- first component
            temp_val := TO_NUMBER(SUBSTR(temp_exp, 1, INSTR(temp_exp, '/') - 1));
            IF temp_val > 31 AND temp_val < 100 THEN
            format_str := 'YYYY/';
            IF temp_val > 50 THEN
            temp_exp := '19' || temp_exp;
            ELSE
            temp_exp := '20' || temp_exp;
            END IF;
            ELSIF temp_val > 12 THEN
            format_str := 'DD/';
            ELSE
            format_str := 'MM/';
            END IF;

            -- second component
            temp_val := TO_NUMBER(SUBSTR(temp_exp, INSTR(temp_exp, '/') + 1, INSTR(temp_exp, '/', 1, 2) - INSTR(temp_exp, '/') - 1));
            IF temp_val > 31 AND temp_val < 100 THEN
            format_str := format_str || 'YYYY/';
            IF temp_val > 50 THEN
            temp_exp := REGEXP_REPLACE(temp_exp, '/([[:digit:]]{2,4})/', '/19' || '\1/');
            ELSE
            temp_exp := REGEXP_REPLACE(temp_exp, '/([[:digit:]]{2,4})/', '/20' || '\1/');
            END IF;
            ELSIF temp_val > 12 THEN
            format_str := format_str || 'DD/';
            ELSE
            IF INSTR(format_str, 'MM') > 0 THEN
            format_str := format_str || 'DD';
            ELSE
            format_str := format_str || 'MM/';
            END IF;
            END IF;

            IF INSTR(format_str, 'MM') = 0 THEN
            format_str := format_str || 'MM';
            ELSIF INSTR(format_str, 'DD') = 0 THEN
            format_str := format_str || 'DD';
            ELSE
            IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) < 100 THEN
            IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) > 50 THEN
            temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '19' || '\1');
            ELSE
            temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '20' || '\1');
            END IF;
            END IF;
            format_str := format_str || '/YYYY';
            END IF;
            END IF;
            END IF;

            IF format_str IS NOT NULL THEN
            RETURN TO_DATE(temp_exp, format_str);
            ELSE
            RETURN TO_DATE(temp_exp, 'DD-MON-YYYY HH24:MI:SS');
            END IF;
            EXCEPTION
            WHEN OTHERS THEN
            RETURN NULL;
            END str_to_date;
            FUNCTION convert_(p_dataType IN VARCHAR2, p_expr IN VARCHAR2, p_style IN VARCHAR2 DEFAULT NULL)
            RETURN VARCHAR2
            IS
            v_ret_value VARCHAR2(50);
            v_format VARCHAR2(30);
            v_year_format VARCHAR2(5) := 'YY';
            v_format_type NUMBER;
            v_numeric_dataType BOOLEAN := TRUE;
            v_is_valid_date BINARY_INTEGER := 0;
            BEGIN
            IF INSTR(UPPER(p_dataType), 'DATE') <> 0 OR INSTR(UPPER(p_dataType), 'CHAR') <> 0 OR
                 INSTR(UPPER(p_dataType), 'CLOB') <> 0 THEN
                 v_numeric_dataType := FALSE;
            END IF;

            IF NOT v_numeric_dataType THEN
                 SELECT NVL2(TO_DATE(p_expr), 1, 0) INTO v_is_valid_date FROM DUAL;
                 END IF;
                 
                 IF (str_to_date(p_expr) IS NOT NULL OR v_is_valid_date != 0 ) THEN
                 IF p_style IS NULL THEN
            v_ret_value := TO_NCHAR(p_expr);
            ELSE -- convert date to character data
            v_format_type := TO_NUMBER(p_style);
            IF v_format_type > 100 THEN
            v_year_format := 'YYYY';     
            END IF;

            v_format := CASE
            WHEN v_format_type = 1 OR v_format_type = 101 THEN 'MM/DD/' || v_year_format
            WHEN v_format_type = 2 OR v_format_type = 102 THEN v_year_format || '.MM.DD'
            WHEN v_format_type = 3 OR v_format_type = 103 THEN 'DD/MM/' || v_year_format
            WHEN v_format_type = 4 OR v_format_type = 104 THEN 'DD.MM.' || v_year_format
            WHEN v_format_type = 5 OR v_format_type = 105 THEN 'DD-MM-' || v_year_format
            WHEN v_format_type = 6 OR v_format_type = 106 THEN 'DD MM ' || v_year_format
            WHEN v_format_type = 7 OR v_format_type = 107 THEN 'MON DD, ' || v_year_format
            WHEN v_format_type = 8 OR v_format_type = 108 THEN 'HH12:MI:SS'
            WHEN v_format_type = 9 OR v_format_type = 109 THEN 'MON DD YYYY HH12:MI:SS.FF3AM'
            WHEN v_format_type = 10 OR v_format_type = 110 THEN 'MM-DD-' || v_year_format
            WHEN v_format_type = 11 OR v_format_type = 111 THEN v_year_format || '/MM/DD'
            WHEN v_format_type = 12 OR v_format_type = 112 THEN v_year_format || 'MMDD'
            WHEN v_format_type = 13 OR v_format_type = 113 THEN 'DD MON YYYY HH12:MI:SS.FF3'
            WHEN v_format_type = 14 OR v_format_type = 114 THEN 'HH24:MI:SS.FF3'
            WHEN v_format_type = 20 OR v_format_type = 120 THEN 'YYYY-MM-DD HH24:MI:SS'
            WHEN v_format_type = 21 OR v_format_type = 121 THEN 'YYYY-MM-DD HH24:MI:SS.FF3'
            WHEN v_format_type = 126 THEN 'YYYY-MM-DD HH12:MI:SS.FF3'
                      WHEN v_format_type = 127 THEN 'YYYY-MM-DD HH12:MI:SS.FF3'
            WHEN v_format_type = 130 THEN 'DD MON YYYY HH12:MI:SS:FF3AM'
            WHEN v_format_type = 131 THEN 'DD/MM/YY HH12:MI:SS:FF3AM'
            END;

                      v_ret_value := CASE
                           WHEN v_format_type = 9 OR v_format_type = 109 OR
                                v_format_type = 13 OR v_format_type = 113 OR
                                v_format_type = 14 OR v_format_type = 114 OR
                                v_format_type = 20 OR v_format_type = 120 OR
                                v_format_type = 21 OR v_format_type = 121 OR
                                v_format_type = 126 OR v_format_type = 127 OR
                                v_format_type = 130 OR v_format_type = 131 THEN
                                
                                CASE UPPER(p_dataType)
                                     WHEN 'DATE' THEN TO_CHAR(TO_TIMESTAMP(p_expr, v_format))
                 ELSE TO_CHAR(TO_TIMESTAMP(p_expr), v_format)
                                END
                           ELSE
                                CASE UPPER(p_dataType)
                                     WHEN 'DATE' THEN TO_CHAR(TO_DATE(p_expr, v_format))
                 ELSE TO_CHAR(TO_DATE(p_expr), v_format)
                                END
                           END;
            END IF;
            ELSE
            -- convert money or smallmoney to character data
            IF SUBSTR(p_expr, 1, 1) = '$' THEN
            v_ret_value := CASE TO_NUMBER(NVL(p_style, 1))
            WHEN 1 THEN TO_CHAR(SUBSTR(p_expr, 2), '999999999999999990.00')
            WHEN 2 THEN TO_CHAR(SUBSTR(p_expr, 2), '999,999,999,999,999,990.00')
            WHEN 3 THEN TO_CHAR(SUBSTR(p_expr, 2), '999999999999999990.0000')
            END;
            ELSE -- convert numeric data to character data
            v_ret_value := TO_CHAR(p_expr);
            END IF;
            END IF;

            RETURN v_ret_value;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END convert_;
            FUNCTION year_(p_date_str IN VARCHAR2)
            RETURN NUMBER
            IS
            v_date DATE;
            BEGIN
            v_date := str_to_date(p_date_str);
            IF v_date IS NULL THEN
            RETURN NULL;
            END IF;

            RETURN TO_NUMBER(TO_CHAR(v_date, 'YY'));
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END year_;
            FUNCTION stuff(p_expr VARCHAR2, p_startIdx NUMBER, p_len NUMBER, p_replace_expr VARCHAR2)
            RETURN VARCHAR2
            IS
            BEGIN
            RETURN REPLACE(p_expr, SUBSTR(p_expr, p_startIdx, p_len), p_replace_expr);
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END stuff;
            PROCEDURE incrementTrancount
            IS
            BEGIN     
            trancount := trancount + 1;
            END incrementTrancount;
            FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN VARCHAR2)
            RETURN DATE
            IS
            v_ucase_interval VARCHAR2(10);
            v_date DATE;
            BEGIN
            v_date := str_to_date(p_date_str);
            v_ucase_interval := UPPER(p_interval);

            IF v_ucase_interval IN ('YEAR', 'YY', 'YYYY')
            THEN
            RETURN ADD_MONTHS(v_date, p_interval_val * 12);
            ELSIF v_ucase_interval IN ('QUARTER', 'QQ', 'Q')
            THEN
            RETURN ADD_MONTHS(v_date, p_interval_val * 3);
            ELSIF v_ucase_interval IN ('MONTH', 'MM', 'M')
            THEN
            RETURN ADD_MONTHS(v_date, p_interval_val);
            ElSIF v_ucase_interval IN ('DAYOFYEAR', 'DY', 'Y', 'DAY', 'DD', 'D', 'WEEKDAY', 'DW', 'W')
            THEN
            RETURN v_date + p_interval_val;
            ElSIF v_ucase_interval IN ('WEEK', 'WK', 'WW')
            THEN
            RETURN v_date + (p_interval_val * 7);
            ElSIF v_ucase_interval IN ('HOUR', 'HH')
            THEN
            RETURN v_date + (p_interval_val / 24);
            ElSIF v_ucase_interval IN ('MINUTE', 'MI', 'N')
            THEN
            RETURN v_date + (p_interval_val / 24 / 60);
            ElSIF v_ucase_interval IN ('SECOND', 'SS', 'S')
            THEN
            RETURN v_date + (p_interval_val / 24 / 60 / 60);
            ElSIF v_ucase_interval IN ('MILLISECOND', 'MS')
            THEN
            RETURN v_date + (p_interval_val / 24 / 60 / 60 / 1000);
            ELSE
            RETURN NULL;
            END IF;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END dateadd;
            FUNCTION isdate(p_expr IN VARCHAR2)
            RETURN NUMBER
            IS
            v_is_valid_date BINARY_INTEGER := 0;
            BEGIN
            IF str_to_date(p_expr) IS NOT NULL THEN
            RETURN 1;
            ELSE
            SELECT NVL2(TO_DATE(p_expr), 1, 0) INTO v_is_valid_date FROM DUAL;
            RETURN v_is_valid_date;
            END IF;
            EXCEPTION
            WHEN OTHERS THEN
            RETURN 0;
            END isdate;
            FUNCTION stats_date(p_table IN VARCHAR2, p_index IN VARCHAR2)
            RETURN DATE
            IS
            v_last_analyzed DATE;
            BEGIN
            SELECT last_analyzed INTO v_last_analyzed
            FROM USER_IND_STATISTICS
            WHERE table_name LIKE UPPER(p_table)
            AND index_name LIKE UPPER(p_index);

            RETURN v_last_analyzed;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END stats_date;
            FUNCTION rand(p_seed NUMBER DEFAULT NULL)
            RETURN NUMBER
            IS
            v_rand_num NUMBER;
            BEGIN
            IF p_seed IS NOT NULL THEN
            DBMS_RANDOM.SEED(p_seed);
            END IF;

            v_rand_num := DBMS_RANDOM.VALUE();

            RETURN v_rand_num;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END rand;
            FUNCTION to_base(p_dec NUMBER, p_base NUMBER)
            RETURN VARCHAR2
            IS
            v_str VARCHAR2(255);
            v_num NUMBER;
            v_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';
            BEGIN
            v_num := p_dec;

            IF p_dec IS NULL OR p_base IS NULL THEN
            RETURN NULL;
            END IF;

            IF TRUNC(p_dec) <> p_dec OR p_dec < 0 THEN
            RAISE PROGRAM_ERROR;
            END IF;

            LOOP
            v_str := SUBSTR(v_hex, MOD(v_num, p_base) + 1, 1) || v_str;
            v_num := TRUNC(v_num / p_base);

            EXIT WHEN v_num = 0;
            END LOOP;

            RETURN v_str;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END to_base;
            FUNCTION patindex(p_pattern IN VARCHAR2, p_expr IN VARCHAR2)
            RETURN NUMBER
            IS
            v_search_pattern VARCHAR2(100);
            v_pos NUMBER := 0;
            BEGIN
            IF p_pattern IS NULL OR p_expr IS NULL THEN
            RETURN NULL;
            END IF;

            IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
            v_search_pattern := p_pattern;
            v_search_pattern := REPLACE(v_search_pattern, '\', '\\');
            v_search_pattern := REPLACE(v_search_pattern, '*', '\*');
            v_search_pattern := REPLACE(v_search_pattern, '+', '\+');
            v_search_pattern := REPLACE(v_search_pattern, '?', '\?');
            v_search_pattern := REPLACE(v_search_pattern, '|', '\|');
            v_search_pattern := REPLACE(v_search_pattern, '^', '\^');
            v_search_pattern := REPLACE(v_search_pattern, '$', '\$');
            v_search_pattern := REPLACE(v_search_pattern, '.', '\.');
            v_search_pattern := REPLACE(v_search_pattern, '{', '\{');
            v_search_pattern := REPLACE(v_search_pattern, '_', '.');

            IF SUBSTR(v_search_pattern, 1, 1) != '%' AND
            SUBSTR(v_search_pattern, -1, 1) != '%' THEN
            v_search_pattern := '^' || v_search_pattern || '$';
            ELSIF SUBSTR(v_search_pattern, 1, 1) != '%' THEN
            v_search_pattern := '^' || SUBSTR(v_search_pattern, 1, LENGTH(v_search_pattern) - 1);
            ELSIF SUBSTR(v_search_pattern, -1, 1) != '%' THEN
            v_search_pattern := SUBSTR(v_search_pattern, 2) || '$';
            ELSE
            v_search_pattern := SUBSTR(v_search_pattern, 2, LENGTH(v_search_pattern) - 2);
            END IF;

            v_pos := REGEXP_INSTR(p_expr, v_search_pattern);
            ELSE
            v_pos := 0;
            END IF;

            RETURN v_pos;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END patindex;
            FUNCTION datediff(p_datepart VARCHAR2, p_start_date_str VARCHAR2, p_end_date_str VARCHAR2)
            RETURN NUMBER
            IS
            v_ret_value NUMBER := NULL;
            v_part VARCHAR2(15);
            v_start_date DATE;
            v_end_date DATE;
            BEGIN
            v_start_date := str_to_date(p_start_date_str);
            v_end_date := str_to_date(p_end_date_str);
            v_part := UPPER(p_datepart);

            IF v_part IN ('YEAR', 'YY', 'YYYY') THEN
            IF EXTRACT(YEAR FROM v_end_date) - EXTRACT(YEAR FROM v_start_date) = 1 AND
            EXTRACT(MONTH FROM v_start_date) = 12 AND EXTRACT(MONTH FROM v_end_date) = 1 AND
            EXTRACT(DAY FROM v_start_date) = 31 AND EXTRACT(DAY FROM v_end_date) = 1 THEN
            -- When comparing December 31 to January 1 of the immediately succeeding year,
            -- DateDiff for Year ("yyyy") returns 1, even though only a day has elapsed.
            v_ret_value := 1;
            ELSE
            v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 12);
            END IF;
            ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN
            v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 3);
            ELSIF v_part IN ('MONTH', 'MM', 'M') THEN
            v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date));
            ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN
            v_ret_value := ROUND(v_end_date - v_start_date);
            ElSIF v_part IN ('DAY', 'DD', 'D') THEN
            v_ret_value := ROUND(v_end_date - v_start_date);
            ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN
            v_ret_value := ROUND((v_end_date - v_start_date) / 7);
            ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN
            v_ret_value := TO_CHAR(v_end_date, 'D') - TO_CHAR(v_start_date, 'D');
            ElSIF v_part IN ('HOUR', 'HH') THEN
            v_ret_value := ROUND((v_end_date - v_start_date) * 24);
            ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN
            v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60);
            ElSIF v_part IN ('SECOND', 'SS', 'S') THEN
            v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60);
            ElSIF v_part IN ('MILLISECOND', 'MS') THEN
            v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60 * 1000);
            END IF;

            RETURN v_ret_value;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END datediff;
            FUNCTION day_(p_date_str IN VARCHAR2)
            RETURN NUMBER
            IS
            v_date DATE;
            BEGIN
            v_date := str_to_date(p_date_str);
            IF v_date IS NULL THEN
            RETURN NULL;
            END IF;

            RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END day_;
            FUNCTION ident_incr(p_sequence IN VARCHAR2)
            RETURN NUMBER
            IS
            v_incr_by NUMBER;
            BEGIN
            SELECT increment_by INTO v_incr_by
            FROM USER_SEQUENCES
            WHERE sequence_name LIKE UPPER(p_sequence);

            RETURN v_incr_by;
            EXCEPTION
            WHEN OTHERS THEN
            raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
            END ident_incr;
            FUNCTION isnumeric(p_expr IN VARCHAR2)
            RETURN NUMBER
            IS
            numeric_val NUMBER;
            temp_str VARCHAR2(50);
            BEGIN
            temp_str := p_expr;
            IF SUBSTR(temp_str, 1, 1) = '$' THEN
            temp_str := SUBSTR(temp_str, 2);
            END IF;
            • 3. Re: database Migration issue
              867407
              h3. part 3
              ---------
              numeric_val := TO_NUMBER(temp_str);
              RETURN 1;
              EXCEPTION
              WHEN OTHERS THEN
              RETURN 0;
              END isnumeric;
              FUNCTION hex(p_num VARCHAR2)
              RETURN VARCHAR2
              IS
              BEGIN
              RETURN to_base(p_num, 16);
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END hex;
              PROCEDURE decrementTrancount
              IS
              BEGIN     
                   IF trancount > 0 THEN
                        trancount := trancount - 1;
                   END IF;
              END decrementTrancount;
              FUNCTION difference(p_expr1 IN VARCHAR2, p_expr2 IN VARCHAR2)
              RETURN NUMBER
              IS
              sound_ex_val_1 CHAR(4);
              sound_ex_val_2 CHAR(4);
              similarity NUMBER := 0;
              idx NUMBER := 1;
              BEGIN
              IF p_expr1 IS NULL OR p_expr2 IS NULL THEN
              RETURN NULL;
              END IF;

              sound_ex_val_1 := SOUNDEX(p_expr1);
              sound_ex_val_2 := SOUNDEX(p_expr2);

              LOOP
              IF SUBSTR(sound_ex_val_1, idx, 1) = SUBSTR(sound_ex_val_2, idx, 1) THEN
              similarity := similarity + 1;
              END IF;

              idx := idx + 1;
              EXIT WHEN idx > 4;
              END LOOP;

              RETURN similarity;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END difference;
              FUNCTION datepart(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2)
              RETURN NUMBER
              IS
              v_part VARCHAR2(15);
              v_date DATE;
              BEGIN
              v_date := str_to_date(p_date_str);
              v_part := UPPER(p_part_expr);

              IF v_part IN ('YEAR', 'YY', 'YYYY') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'YYYY'));
              ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'Q'));
              ELSIF v_part IN ('MONTH', 'MM', 'M') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
              ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DDD'));
              ELSIF v_part IN ('DAY', 'DD', 'D') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
              ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'D'));
              ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'IW'));
              ElSIF v_part IN ('HOUR', 'HH') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'HH24'));
              ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MI'));
              ElSIF v_part IN ('SECOND', 'SS', 'S') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'SS'));
              ElSIF v_part IN ('MILLISECOND', 'MS') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'FF3'));
              ELSE
              RETURN NULL;
              END IF;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END datepart;
              FUNCTION radians(p_degree IN NUMBER)
              RETURN NUMBER
              IS
              v_rad NUMBER;
              BEGIN
              v_rad := p_degree / 180 * pi();
              RETURN v_rad;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END radians;
              FUNCTION reverse_(p_expr IN VARCHAR2)
              RETURN VARCHAR2
              IS
              v_result VARCHAR2(2000) := NULL;
              BEGIN
              FOR i IN 1..LENGTH(p_expr) LOOP
              v_result := v_result || SUBSTR(p_expr, -i, 1);
              END LOOP;

              RETURN v_result;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END reverse_;
              FUNCTION parsename(p_object_name IN VARCHAR2, p_object_piece IN NUMBER)
              RETURN VARCHAR2
              IS
              ret_val VARCHAR2(150) := NULL;
              pos NUMBER;
              v_next_pos NUMBER;
              BEGIN
              IF p_object_name IS NULL THEN
              RETURN NULL;
              END IF;

              -- for 10g
              IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
              IF p_object_piece = 1 THEN -- object name
              ret_val := REGEXP_SUBSTR(p_object_name, '(^[^\.]+$)|(\.[^\.]+$)');
              ret_val := REPLACE(ret_val, '.', '');
              ELSIF p_object_piece = 2 THEN -- schema name
              ret_val := REGEXP_SUBSTR(p_object_name, '([^\.]+)\.([^\.]+$)');
              ret_val := REGEXP_REPLACE(ret_val, '\.([^\.]+$)', '');
              ELSIF p_object_piece = 3 THEN -- database name
              ret_val := REGEXP_SUBSTR(p_object_name, '([^\.]+)\.([^\.]*)\.([^\.]+$)');
              ret_val := REGEXP_REPLACE(ret_val, '\.([^\.]*)\.([^\.]+$)', '');
              ELSIF p_object_piece = 4 THEN -- server name
              ret_val := REGEXP_SUBSTR(p_object_name, '^([^\.]+)\.([^\.]*)\.([^\.]*)\.([^\.]+$)');
              IF ret_val IS NOT NULL THEN
              ret_val := REGEXP_REPLACE(p_object_name, '^([^\.]+)\.([^\.]*)\.([^\.]*)\.([^\.]+$)', '\1');
              END IF;
              END IF;
              ELSE
              ret_val := p_object_name;
              v_next_pos := LENGTH(p_object_name);
              FOR i IN 1 .. p_object_piece LOOP
              pos := INSTR(p_object_name, '.', -1, i);
              IF pos > 0 THEN
              ret_val := SUBSTR(p_object_name, pos + 1, v_next_pos - pos);
              END IF;
              v_next_pos := pos;
              END LOOP;

              IF LENGTH(ret_val) = 0 THEN
              RETURN NULL;
              END IF;
              END IF;

              RETURN ret_val;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END parsename;
              FUNCTION round_(p_expr NUMBER, p_len NUMBER, p_function NUMBER DEFAULT 0)
              RETURN NUMBER
              IS
              v_ret_value NUMBER;
              BEGIN
              IF p_function = 0 THEN
              v_ret_value := ROUND(p_expr, p_len);
              ELSE
              v_ret_value := TRUNC(p_expr, p_len);
              END IF;

              RETURN v_ret_value;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END round_;
              FUNCTION month_(p_date_str IN VARCHAR2)
              RETURN NUMBER
              IS
              v_date DATE;
              BEGIN
              v_date := str_to_date(p_date_str);
              IF v_date IS NULL THEN
              RETURN NULL;
              END IF;

              RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END month_;
              PROCEDURE commit_transaction
              IS
              BEGIN     
              IF trancount <= 1 THEN
              COMMIT;
              END IF;
              resetTrancount;
              END commit_transaction;
              FUNCTION pi
              RETURN NUMBER
              IS
              pi NUMBER := 3.141592653589793116;
              BEGIN
              RETURN pi;
              END pi;
              PROCEDURE resetTrancount
              IS
              BEGIN     
              trancount := 0;
              END resetTrancount;
              FUNCTION oct(p_num VARCHAR2)
              RETURN VARCHAR2
              IS
              BEGIN
              RETURN to_base(p_num, 8);
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END oct;
              FUNCTION str(p_expr IN NUMBER, p_len IN NUMBER DEFAULT 10, p_scale IN NUMBER DEFAULT 0)
              RETURN VARCHAR2
              IS
              v_ret_val VARCHAR2(50);
              v_temp_val NUMBER;
              v_format_str VARCHAR2(50);
              BEGIN
              IF p_len < LENGTH(TO_CHAR(p_expr)) THEN
              RETURN '**';
              END IF;

              v_temp_val := p_expr;
              v_temp_val := ROUND(v_temp_val, p_scale);

              IF p_scale > 0 THEN
              v_format_str := LPAD(' ', p_len - p_scale, '9');
              v_format_str := TRIM(v_format_str) || '.';
              v_format_str := RPAD(v_format_str, p_len, '0');
              ELSE
              v_format_str := LPAD('', p_len, '9');
              END IF;

              v_ret_val := TO_CHAR(v_temp_val, v_format_str);
              RETURN v_ret_val;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END str;
              FUNCTION degrees(p_angle_radians IN NUMBER)
              RETURN NUMBER
              IS
              BEGIN
              IF p_angle_radians IS NULL THEN
              RETURN NULL;
              END IF;

              RETURN p_angle_radians / pi() * 180;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END degrees;
              FUNCTION datename(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2)
              RETURN VARCHAR2
              IS
              v_part VARCHAR2(15);
              v_date DATE;
              BEGIN
              v_date := str_to_date(p_date_str);
              v_part := UPPER(p_part_expr);

              IF v_part IN ('YEAR', 'YY', 'YYYY') THEN RETURN TO_CHAR(v_date, 'YYYY');
              ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN RETURN TO_CHAR(v_date, 'Q');
              ELSIF v_part IN ('MONTH', 'MM', 'M') THEN RETURN TO_CHAR(v_date, 'MONTH');
              ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN RETURN TO_CHAR(v_date, 'DDD');
              ELSIF v_part IN ('DAY', 'DD', 'D') THEN RETURN TO_CHAR(v_date, 'DD');
              ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN RETURN TO_CHAR(v_date, 'DAY');
              ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN RETURN TO_CHAR(v_date, 'IW');
              ElSIF v_part IN ('HOUR', 'HH') THEN RETURN TO_CHAR(v_date, 'HH24');
              ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN RETURN TO_CHAR(v_date, 'MI');
              ElSIF v_part IN ('SECOND', 'SS', 'S') THEN RETURN TO_CHAR(v_date, 'SS');
              ElSIF v_part IN ('MILLISECOND', 'MS') THEN RETURN TO_CHAR(v_date, 'FF3');
              ELSE
              RETURN NULL;
              END IF;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END datename;
              FUNCTION ident_seed(p_sequence IN VARCHAR2)
              RETURN NUMBER
              IS
              v_seed NUMBER;
              BEGIN
              SELECT min_value INTO v_seed
              FROM USER_SEQUENCES
              WHERE sequence_name LIKE UPPER(p_sequence);

              RETURN v_seed;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END ident_seed;
              FUNCTION quotename(p_str IN VARCHAR2, p_delimiters IN VARCHAR2 DEFAULT '[]')
              RETURN VARCHAR2
              IS
              v_ret_val VARCHAR2(150) := NULL;
              BEGIN
              IF p_delimiters = '[]' THEN
              v_ret_val := '[' || REPLACE(p_str, ']', ']]') || ']';
              ELSIF p_delimiters = '"' THEN
              v_ret_val := '"' || p_str || '"';
              ELSIF p_delimiters = '''' THEN
              v_ret_val := '''' || p_str || '''';
              END IF;

              RETURN v_ret_val;
              EXCEPTION
              WHEN OTHERS THEN
              raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
              END quotename;

              FUNCTION fetch_status(p_cursorfound IN BOOLEAN)
              RETURN NUMBER
              IS
              v_fetch_status NUMBER := 0;
              BEGIN
              CASE
              WHEN p_cursorfound THEN
              v_fetch_status := 0;
              ELSE
              v_fetch_status := -1;
              END CASE;
              return v_fetch_status;
              END fetch_status;
              END sqlserver_utilities;

              ------------------------------------------
              *i hope its working with you too, let me know .*

              h5. thanks