Getting error with converted SQL statement

Question

I'm trying to execute the below statement on PostgreSQL 12.4. This statement was converted from Oracle 10g to PostgreSQL 12.4 using the online SQL Lines tool.

select
        hid,
        pname,
        starting_from,
        ename
    from schema1.pet
    where hid=COALESCE(TO_NUMBER(sys_context('temp1','hid')),0);

I'm getting the below error:

ERROR:  function sys_context(unknown, unknown) does not exist
LINE 7:  where hid=COALESCE(TO_NUMBER(sys_context('temp1','hid...

I'm not sure what's the equivalent of sys_context in PostgreSQL.

How can I fix this statement to PostgreSQL?

You should add a description of what sys_context does, then you could also reach those people who are not familiar with Oracle. — Laurenz Albe
– Laurenz Albe, Commented Nov 2, 2021 at 4:56

Patrick · Accepted Answer · 2021-11-02 04:57:31Z

1

The PostgreSQL current_setting() function comes closest to the Oracle sys_context() function. PostgreSQL does not use the namespace parameter, however.

answered Nov 2, 2021 at 4:57

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting error with converted SQL statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related