This discussion is archived
3 Replies Latest reply: Sep 21, 2011 1:21 AM by 867407 RSS

database Migration issue

867407 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points