 |
|
EnterpriseDB: Commit and Rollback
Oracle Tips by
Burleson
|
Transaction control statements are not allowed in SPL code. If your
code makes heavy use of COMMIT and ROLLBACK, you will need to make
architectural changes. EnterpriseDB automatically commits changes.
While
this code will compile:
CREATE OR REPLACE PROCEDURE test_commit AS
BEGIN
COMMIT;
END;
Any
attempt to run it:
BEGIN
test_commit;
END;
Results
in an error:
ERROR: EDB-XX000: SPI_execute_plan failed executing
query "COMMIT": SPI_ERROR_TRANSACTION
There is
no current work around beyond commenting or removing any transaction
control.
Decode
This is
actually a SQL coding issue but I first discovered it during
procedural coding so I am listing it here. The good news is that
decode is supported in EnterpriseDB. The bad news is that it always
returns a character value.
This can
cause problems when calling overloaded procedures, when using UNIONS
and when using some other SQL features. For example, let's say I have
two functions named abc, one that takes a number and one that takes a
varchar2. Well, what do you know, I just happen to have those
functions laying around.
I will
execute those functions using a decode to see our results:
edb=# select abc(decode('lewis', 'lewis', 1, 0));
abc
------------------------------
This is a character function
(1 row)
I
expected this to resolve the decode as 1 and call the number function.
I will see if it was because 'Lewis' can only be a character:
edb=# select abc(decode('2', '1', 1, 0));
abc
------------------------------
This is a character function
(1 row)
In this
case, '2' and '1' could be resolved as numbers and I would expect the
0 value to call the numeric function. That didn't happen. I can also
call the function with only numeric values:
edb=# select abc(decode(2, 1, 1, 0));
abc
------------------------------
This is a character function
(1 row)
No
matter how it looks at or resolves this decode, it should be calling
the number function and it is not. In Oracle, if I packaged this code
up to allow overloading, it works as expected:
SQL> select overloaded.abc(decode('lewis', 'lewis', 1,
0))
2 from dual;
OVERLOADED.ABC(DECODE ('LEWIS','LEWIS',1,0))
----------------------------------------------------------------
This is a number function
SQL> select overloaded.abc(decode('2', '1', 1, 0))
2 from dual;
OVERLOADED.ABC(DECODE ('2','1',1,0))
------------------------------------------------------------
This is a number function
SQL> select overloaded.abc(decode(2, 1, 1, 0))
2 from dual;
OVERLOADED.ABC(DECODE (2,1,1,0))
-------------------------------------------------------
This is a number function
Nested Functions
In
Oracle PL/SQL and EnterpriseDB, code blocks are allowed to nest
additional blocks. In Oracle PL/SQL, stored procedures are allowed to
nest functions and procedures. This feature is not supported in
EnterpriseDB.
For
example, the procedure below is allowed in PL/SQL:
CREATE PROCEDURE display_output
AS
PROCEDURE nested_proc
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('In the nested proc');
END;
BEGIN
nested_proc;
END;
In
Oracle, this works as expected:
Procedure created.
SQL> set serveroutput on
SQL> exec display_output
In the nested proc
PL/SQL procedure successfully completed.
SQL>
In EnterpriseDB, we cannot do this:
edb=# CREATE PROCEDURE display_output
edb-# AS
edb$# PROCEDURE nested_proc
edb$# AS
edb$# BEGIN
edb$# DBMS_OUTPUT.PUT_LINE('In the nested proc');
edb$# END;
CREATE PROCEDURE
edb=# BEGIN
edb-#
edb-# nested_proc;
edb$#
edb$# END;
ERROR: EDB-00302: procedure 'NESTED_PROC' not found
The work
around for this would be to put this code in a package and unnest the
nested procedure. If that is only a single procedure, it is not such
a large undertaking. If you have used this feature heavily, it may be
a cost sink.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
