#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    4
    Rep Power
    0

    Converting Oracle Package to Postgres


    Hello,

    Wondering if someone can point me to right direction. Recently I am migrating an oracle database 11g to postgres(9.2). Using enterpriseDB migration tool kit is fairly straight forward and was able migrate most of them except for one package body. package worked but I am stuck with package body. Below is the the package which was created successfully.

    CREATE OR REPLACE PACKAGE PLOG
    authid current_user
    as
    procedure disable_logging (p_val in varchar2, p_cat in varchar2);
    procedure disable_sql (p_val in varchar2);
    procedure disable_app (p_val in varchar2);
    procedure disable_node (p_val in varchar2);
    procedure disable_uri (p_val in varchar2);

    function code_id (p_val in varchar2, p_cat in varchar2) return number;
    function code_val (p_id in number) return varchar2;

    function node_id (p_val in varchar2) return number;
    function app_id (p_val in varchar2) return number;
    function thread_id (p_val in varchar2) return number;
    function sql_id (p_val in varchar2) return number;
    function uri_id (p_val in varchar2) return number;
    function logger_id (p_val in varchar2) return number;
    function source_id (p_val in varchar2) return number;

    function parm_id (p_parm in varchar2, p_cat in varchar2) return varchar2;
    function parm_val (p_parm_id in varchar2) return varchar2;
    function dbparm_id (p_parm in varchar2) return varchar2;
    function webparm_id (p_parm in varchar2) return varchar2;

    end plog;

    /


    Below is the function in the package body which I have highlighted in bold is the error.

    function parm_name (p_parm in varchar2) return varchar2
    as -- returns "parm1= parm2= ... parmN="
    begin
    if p_parm is null then return null; end if;
    return
    regexp_replace(p_parm, '=[^=|]*(\||$)','=')
    ;
    end parm_name;

    error is
    if p_parm is null then return null; end if;

    ERROR: syntax error at or near "end"
    SQL state: 42601
    Character: 3063

    Below is the complete package body script.

    CREATE OR REPLACE PACKAGE BODY PLOG
    as
    procedure disable_logging (p_val in varchar2, p_cat in varchar2)
    as
    begin
    update codes
    set disabled = 'Y'
    where cat = p_cat
    and val = p_val
    ;
    end disable_logging;

    procedure disable_sql (p_val in varchar2)
    as
    begin
    disable_logging(p_val, 'SQL');
    end disable_sql;

    procedure disable_app (p_val in varchar2)
    as
    begin
    disable_logging(p_val, 'APP');
    end disable_app;

    procedure disable_node (p_val in varchar2)
    as
    begin
    disable_logging(p_val, 'NODE');
    end disable_node;

    procedure disable_uri (p_val in varchar2)
    as
    begin
    disable_logging(p_val, 'URI');
    end disable_uri;

    -------------------------

    function code_id (p_val in varchar2, p_cat in varchar2) return number
    as
    l_id number;
    l_val varchar2(4000);
    begin -- code_id
    begin
    l_val := nvl(p_val,'NULL');
    select decode(disabled,'Y',null,id)
    into l_id
    from codes
    where val = l_val
    and cat = p_cat
    ;
    exception
    when no_data_found then
    --dbms_output.put_line('no_data_found');
    insert into codes (val, cat)
    values (l_val, p_cat)
    returning id into l_id
    ;
    end;
    return l_id;
    exception
    when dup_val_on_index then
    --dbms_output.put_line('dup_val_on_index');
    select decode(disabled,'Y',null,id)
    into l_id
    from codes
    where val = l_val
    and cat = p_cat
    ;
    return l_id;
    when others then
    --dbms_output.put_line('others '|| to_char(sqlcode) ||' '||sqlerrm );
    return -1;
    end code_id;

    function code_val (p_id in number) return varchar2
    as
    cursor l_cur is
    select decode(val,'NULL','',val) val from codes where id = p_id;
    l_row l_cur%rowtype;
    begin -- code_val
    open l_cur;
    fetch l_cur into l_row;
    close l_cur;
    return l_row.val;
    end code_val;

    function node_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'NODE');
    end node_id;

    function app_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'APP');
    end app_id;

    function thread_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'THREAD');
    end thread_id;

    function sql_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'SQL');
    end sql_id;

    function uri_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'URI');
    end uri_id;

    function logger_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'LOGGER');
    end logger_id;

    function source_id (p_val in varchar2) return number
    as
    begin
    return code_id(p_val, 'SOURCE');
    end source_id;
    --

    function parm_id (p_parm in varchar2, p_cat in varchar2) return varchar2
    as
    /*
    Given p_parm of the form "parm1=value1| parm2=value2| ... | parmN=valueN"
    this function returns "code_id|value1|value2|...|valueN|"
    where code_id is the codes.id key to the codes.val
    "parm1= parm2= ... parmN="
    */
    function parm_name (p_parm in varchar2) return varchar2
    as -- returns "parm1= parm2= ... parmN="
    begin
    if p_parm is null then return null; end if;
    return
    regexp_replace(p_parm, '=[^=|]*(\||$)','=')
    ;
    end parm_name;

    function parm_val (p_parm in varchar2) return varchar2
    as -- returns "code_id|value1|value2|...|valueN|"
    begin
    if p_parm is null then return null; end if;
    return
    replace(
    regexp_replace(p_parm, '(^|(\| ))[^|= ]+(=|$)','|')
    ,'null','')
    ||'|';
    end parm_val;
    begin -- parm_id
    return
    to_char(code_id(parm_name(p_parm), p_cat)) ||
    parm_val(p_parm);
    end parm_id;

    function dbparm_id (p_parm in varchar2) return varchar2
    as
    begin
    return parm_id(p_parm, 'DBPARM');
    end dbparm_id;

    function webparm_id (p_parm in varchar2) return varchar2
    as
    begin
    return parm_id(p_parm, 'WEBPARM');
    end webparm_id;

    function parm_val (p_parm_id in varchar2) return varchar2
    as
    /*
    This function reconstructs the original parameter value
    "parm1=value1| parm2=value2| ... | parmN=valueN"
    from the compressed DB stored value
    "code_id|value1|value2|...|valueN|"
    */
    l_id number;
    i integer;
    l_val varchar2(2000);
    l_parm_name varchar2(2000);
    l_parm_val varchar2(2000);
    l_parm varchar2(4000);
    begin -- parm_val
    l_id := to_number(regexp_replace(p_parm_id,'\|.*$',''));
    l_parm_name := code_val(l_id);
    if l_parm_name = '' then return ''; end if;

    l_parm_val := regexp_replace(p_parm_id,'^[0-9]+\|','|');
    l_parm := l_parm_name;
    i := 1;
    while (instr(l_parm_name,'=',1,i) > 0)
    loop
    l_val := ltrim(regexp_substr(l_parm_val, '\|[^|]*',1,i),'|');
    l_parm := regexp_replace(l_parm,'=','='||nvl(l_val,'null')||'|',1,i);
    i := i + 1;
    end loop;
    l_parm := replace(l_parm, '#n', chr(10));
    l_parm := regexp_replace(l_parm, '\|$', '');
    return l_parm;
    end parm_val;

    end plog;

    /

    Apologies for long post. Appreciate any help.

    Cheers,

    Blesson
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    The message
    "ERROR: syntax error at or near "end""

    Means there is something missing before or after 'end', which usually means there is a semicolon missing before or after "end".

    I am a bit confused by your post, which code is the Oracle code and which is the generated PostgreSQL code?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    4
    Rep Power
    0
    Thanks for the reply and sorry for the confusion, All code above is oracle, I copied the code from oracle and copied on to postgres and executed the code, package created successfully but not package body. The function(which is bolded) within the package body is the problem. Syntax seems correct but I may be obviously missing something which I can't figure it out.

    Blesson
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    You can't just paste Oracle code into PostgreSQL, some conversion will have to be done.

    In this case, like the errors says, there is something wrong with the END line:

    end parm_name;

    PostgreSQL does not expect the parm_name bit there, just a semicolom:

    END;

    Have a look at the documentation for the exact syntax:
    http://www.postgresql.org/docs/9.1/static/sql-createfunction.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    4
    Rep Power
    0
    Thanks very much, back to some more reading. Hopefully I will get some idea from your link. It's just driving me insane, migration tool(runMTK.sh) works perfect for all objects of oracle to postgres except the package.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    I trust that you have contacted the creators of this tool also?
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    4
    Rep Power
    0
    Not yet but I may have to do that sooner, it's a free tool from EnterpriseDB mob, We are doing a proof of concept migrating oracle database to postgres for some of our applications.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    I feel very much like this is a bug, or an error in the manual that caused you to miss some conversion. Either way they will want to know about it so they can fix either the bug or the instructions.

    Good luck for now, let me know how you get on!

IMN logo majestic logo threadwatch logo seochat tools logo