Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter