cancel
Showing results for 
Search instead for 
Did you mean: 

EXECUTE IMMEDIATE

SOLVED
Adventurer

EXECUTE IMMEDIATE

Hi

VERSION: EDB 9.6

I have script that has "excecute immediately" using in out parameters.  It is giving error when migrating from Oracle to Edb.  I checked the execute immediately and dynamic sql in the site: https://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guid...

I can't seem to find example of excecute immediately" using in out parameters like Oracle.  

 

For example:

EXECUTE IMMEDIATE variableb USING IN id, OUT v_count or

EXECUTE IMMEDIATE variableb USING IN  OUT id

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Explorer

Re: EXECUTE IMMEDIATE

You can try something like given below as a workaround:

DECLARE
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
    plsql_block     TEXT;
BEGIN
    v_deptno := 30;
    v_empno  := 7900;
    v_ename  := '';
    plsql_block := 'exec emp_query(:v_deptno, :v_empno, :v_ename, :v_job, :v_hiredate, :v_sal)';
    EXECUTE IMMEDIATE plsql_block USING v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal ;
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
END;
3 REPLIES
EDB Team Member

Re: EXECUTE IMMEDIATE


angie wrote:

Hi

VERSION: EDB 9.6

I have script that has "excecute immediately" using in out parameters.  It is giving error when migrating from Oracle to Edb.  I checked the execute immediately and dynamic sql in the site: https://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guid...

I can't seem to find example of excecute immediately" using in out parameters like Oracle.  

 

For example:

EXECUTE IMMEDIATE variableb USING IN id, OUT v_count or

EXECUTE IMMEDIATE variableb USING IN  OUT id

 


Currently, EXECUTE IMMEDIATE USING IN, IN OUT is not supported. You need to workaround to get the similar behavior or you can reach out to EDB support for more assistance. 

Currently supported syntax https://www.enterprisedb.com/docs/en/10.0/Ora_Compat_Dev_Guide/Database_Compatibility_for_Oracle_Dev...

 

Regards

Raghav

Adventurer

Re: EXECUTE IMMEDIATE

Hi,

What's the workaround to get the similar behavior: 

EXECUTE IMMEDIATE variableb USING IN id, OUT v_count or

EXECUTE IMMEDIATE variableb USING IN  OUT id

Highlighted
Explorer

Re: EXECUTE IMMEDIATE

You can try something like given below as a workaround:

DECLARE
    v_deptno        NUMBER(2);
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER;
    plsql_block     TEXT;
BEGIN
    v_deptno := 30;
    v_empno  := 7900;
    v_ename  := '';
    plsql_block := 'exec emp_query(:v_deptno, :v_empno, :v_ename, :v_job, :v_hiredate, :v_sal)';
    EXECUTE IMMEDIATE plsql_block USING v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal ;
    DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
END;