PostgreSQL Question posted in
The official documentation can be found here.

Equivalent of ORACLE signature with bind variable IN PostgreSQL

I am in the process of migrating from Oracle to Postgres with Ora2pg but I find this problem that the signatures on the Postgres side do not exist
can you help me find the right solution ?

What will be equivalent of following oracle’s query in Postgres?

case
        when varSqlSignature = '1239678541' then 
            execute immediate varSql using 
                 /*1*/rowData.CODE_MOUVEMENT        ,/*2*/rowData.TYPE_CALCUL           ,/*3*/rowData.MONTANT_CALCUL
                ,/*9*/rowData.MONTANT_CALCUL_DV     ,/*6*/rowData.DATE_CALCUL           ,/*7*/rowData.CHAINE_CALCUL
                ,/*8*/rowData.RESULTAT_CALCUL       ,/*5*/varActionType                 ,/*4*/varLoadDate       
                ,/*1*/rowData.CODE_MOUVEMENT
            ;
Tags:

2

Answers


  1. 0

    The code you have presented has invalid syntax in Oracle.

    1. It starts with a CASE expression which has the syntax:

      CASE WHEN expression THEN expression END

      Your statement has CASE, WHEN and THEN but no expression after the THEN (EXECUTE IMMEDIATE is not an expression) and no END keyword;

    2. A CASE expression is not, on its own, a valid SQL or PL/SQL statement;

    3. Then the code goes into what should be a separate PL/SQL statement to EXECUTE IMMEDIATE some dynamic SQL passing some bind variables from a record and should have a ; before the EXECUTE IMMEDIATE to separate it from the previous statement.

    4. A PL/SQL statement should be included in a DECLARE ... BEGIN ... END; block but this is not.

    5. The variables varSqlSignature, varSql and rowData have not been declared and so we have no idea what what dynamic code would do or what the record contains as it is not included in the question.

    Therefore, since the statement is invalid in Oracle due to multiple reasons then the equivalent in PostgreSQL would be another invalid statement (or even the same statement).

    Login or Signup to reply.
  2. 0

    The code you are showing must be PL/SQL, not SQL.

    The incomplete code snippet you are showing would work just fine in PostgreSQL’s PL/pgSQL procedural language if you remove the IMMEDIATE keyword (which is just noise anyway).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search