Topic
2 replies Latest Post - ‏2011-10-25T11:00:08Z by SystemAdmin
SystemAdmin
SystemAdmin
708 Posts
ACCEPTED ANSWER

Pinned topic COMMIT and ROLLBACK statements in PL/SQL

‏2011-10-20T18:41:46Z |
Hi list, I have one issue I have been struggling with for a couple days without solution yet.

I have a PL/SQL package body more or less like this:

PROCEDURE PROC2 (...)
bla, bla, bla
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fallo al procesar la tabla:'||tab);
ROLLBACK; <---------------- This rollback fails
INSERT INTO gims_c1_tp.dummy VALUES(comando,Orden);
COMMIT; <---------------- This commit fails
RAISE_APPLICATION_ERROR(-20001,sqlerrm);
END;
FUNCTION FUNC1
...
PROC2(x,y,z)
return
PROCEDURE PROC1
...
A := FUNC1(a,b,c);
commit; <-------------- THIS commit works.
end
I can execute PROC1 but I get SQLCODE: -487 (not allowed to execute commit nor rollback) from PROC2. The program compiles OK but fails at runtime (what's the sense of allowing such a compilation if it is not allowed anyway?)

So I have extracted the failing block to an independent procedure (CREATE PROCEDURE) to call with
EXECUTE IMMEDIATE 'BEGIN MYTEST( :1, :2, :3 ); END' USING IN A, IN B, IN C;

But it fails due to the same. So the question is: Is it possible to use a commit/rollback coming from an oracle migrated PL/SQL in such a fashion? Well, I know I can't but maybe the question should be "How could I achieve the same effect as it of the original Oracle PL/SQL one?"

I have included this post here since it relates to Oracle to DB2 migration. However if this is not the right forum, please let me know and I will post it elsewhere.

Thanks in advance,
Raul Baron
Updated on 2011-10-25T11:00:08Z at 2011-10-25T11:00:08Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    708 Posts
    ACCEPTED ANSWER

    Re: COMMIT and ROLLBACK statements in PL/SQL

    ‏2011-10-20T19:33:33Z  in response to SystemAdmin
    Fascinating, this is the third time in a week this particular issue is being raised!

    Let me first explain the way things are in 9.7.4 and then look forward.
    1. The assumption of DB2 (or rather the humble developers behind DB2) is that functions are
    distinct from procedures:
    Procedures are application logic pushed onto the server to avoid
    client-server communication (amongst other things.
    Functions are extensions to SQL which are invoked in SELECT lists, WHERE clauses etc.
    Given that fundamental distinction it is obvious (to said developers) that certain things
    procedures do (such as updated the database, or doing commit or rollback)
    make no sense for functions because what would it mean to COMMIT in a where-clause?

    Now, still following that line of reasoning, if a procedure is CALLed directly or
    indirectly from the application then commit is allowed, and if it is called directly or
    indirectly from a function it is not allowed.
    So that's why Db2 allows you to compile a procedure with commit and rollback in it,
    but uses context to decide whether the commit or rollback is actually allowed.

    Makes all perfect sense if you are a DB2 developer....
    ... but makes no sense for an Oracle PL/SQL developer because PL/SQL developers make
    virtually no distinctions between functions and procedures.

    So since DB2 9.7 GA, with every fixpack we have tirelessly :-) worked to make DB2
    functions more like procedures.
    E.g. you can now to updates and deletes in functions as long as you "use them like"
    procedures (e.g. "x := foo()" rather than "WHERE c1 = foo()").
    ROLLBACK and COMMIT is the latest (and hopefully last) battle ground.
    Unfortunately it's not in the upcoming FP5 (any minute now!), but in a subsequent fixpack.
    If you desparately need the fix sooner: Please open a PMR and we'll see what we can do.
    2. BEGIN <somethingdoingROLLBACK> END
    A special friend of mine...
    DB2 compiles anonymous blocks into DB2 packages and then executes
    those. But when you ROLLBACK inside of this package to a point prior to when the
    anonymous block started you are essentially yanking the carpet from under your feet.
    In FP5 (any minute now, really!) Anonymous blocks are compiled into DB2 packages using an
    autonomous transaction. That way you can actually do that rollback within the block
    without falling over.

    Cheers
    Serge
    • SystemAdmin
      SystemAdmin
      708 Posts
      ACCEPTED ANSWER

      Re: COMMIT and ROLLBACK statements in PL/SQL

      ‏2011-10-25T11:00:08Z  in response to SystemAdmin
      Serge, thank you very much for your quick response and clearances which once again are the greatest help.

      Since I'm in the DB2 side of the migration I have to struggle with the Oracle DBAs and developers whenever they complain about the -in their opinion- insufficient degree of compatibility achieved since "functions don't behave like in Oracle". I try to keep things the most untouched possible but there are times when you just can't and must modify certain things, as in this case where the solution was as simple as converting the function in a procedure. Once I did this, the original problem was History.

      Also we have an important PL/SQL part which actually represents the harder part of the migration because we had an aprox. 50-60% of successful DMT compilation right out of the box. (when I say "per cent" I really mean it because we have aprox. 100 PL/SQL packages). So there is still a 40% (40 PL/SQLs that need work on them, which is what Oracle developers and DBAs complain about ignoring the fact that some 8000-9000 objects have been migrated without touching a comma. But that's the way the ball bounces, right?

      Once again thank you very much for your help and congratulations to the lab guys who are doing an excellent job !!

      Best Regards,
      Raul