msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Logger: a PL/SQL Logging and Debugging Framework ↗

Martin D’Souza:

Logger is a PL/SQL logging and debugging framework. It’s used in many organizations to instrument code in their Oracle applications. Tyler Muth created Logger a few years ago and has since released several upgrades, the last being 1.4.0. After some great feedback, I’m pleased to announce that we’ve just launched Logger 2.0.0 Beta.

Instrumentation, logging and debugging utilities are essential in any PL/SQL development project.

Comments Off | Filed in Oracle | Tags: ,


Oracle Magazine PL/SQL Columns by Steven Feuerstein, the Whole List ↗

Dating back to January 2003.

Comments Off | Filed in Oracle | Tags:


See How Easily You Can Improve Performance by Using These Five Data Caching Techniques

I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:

  • SQL Query Result Cache
  • PL/SQL Function Result Cache
  • Package-Based Cache
  • Deterministic Function Cache
  • Scalar Subquery Cache

Click here to register.

Update:
The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:

  • Power Point presentation
  • Corresponding white paper
  • All the demo scripts
3 Comments | Filed in Oracle | Tags: , ,


Row versus Set Processing, Surprise! ↗

Craig Shallahamer:

1. Set based processing will likely be much faster than row based processing. Our experiment of processing 100K rows showed row based processing was 3700 times slower than set based processing. Not twice as slower or even 10 times slower… 3700 times slower!

2. Compared to set based processing, row based processing times degrade much quicker than set based processing. That is, row based processing does not scale nearly as well as set based processing. We saw this in that the linear trend line for row based processing was 0.00259 compared to 0.00000 for set based processing.
Comments Off | Filed in Oracle | Tags: ,


Bind, don’t concatenate, to optimize performance and simplify dynamic string construction ↗

Steven Feuerstein gives us a great and simple example of how to use bind variables to avoid concatenation of variable values into dynamic SQL strings and, at the same time, improve performance by order of magnitude:

When you use bind variables, you greatly simplify the task of writing the dynamic SQL string. You don’t have to write all that concatenating code and you don’t have to perform datatype conversions. The USING clause automatically performs native binding of the appropriate types.

He also notes:

You can bind only variable values. You can’t bind in the names of tables or columns, nor can you bind in parts of a SQL statement structure, such as the entire WHERE clause. In these cases, you must use concatenation.
Comments Off | Filed in Oracle | Tags: ,


User Exceptions Gotcha, When an Error Appears as a Success

This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.

Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.

The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:

Continue reading…

3 Comments | Filed in Oracle, Tips | Tags: , , , , ,


Working with Microsoft Office 2007 (OOXML) files using Oracle PL/SQL ↗

Morten Braten:

I have written a package for working with OOXML documents. It’s called OOXML_UTIL_PKG and you can download it as part of (you guessed it) the Alexandria utility library for PL/SQL.
Comments Off | Filed in Links, Oracle | Tags: ,


Create an Excel file with PL/SQL ↗

Anton Scheffer:

For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.
Comments Off | Filed in Links, Oracle | Tags: ,


7 Golden Rules That Make You a Better Programmer

Steven Feuerstein published a new presentation titled: Golden Rules for Developers [PDF].

Here is a summary of his seven “golden rules” that will help you write better code:

  1. Don’t repeat anything.
  2. Hide everything.
  3. Don’t take shortcuts.
  4. Embrace standards.
  5. Build on a foundation.
  6. Never lose information.
  7. Don’t write code alone.

Agreed with all. I would also add an eighth one: Understand your data.

What would you add?

11 Comments | Filed in Oracle, Tips | Tags:


Please Request a Reasonable Conversion

Here is something you need to be aware of when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements in PL/SQL.

Let’s execute this simple anonymous PL/SQL block:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4000);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4000);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  8      INTO l_string USING l_string;
  9  END;
 10  /

PL/SQL procedure successfully completed.

The EXECUTE IMMEDIATE and OPEN FOR statements are used with the USING clause. USING supplies a bind argument for the SQL string. In this example the value of the bind argument is a 4,000 character string.

Now, let’s pass a string greater than 4,000 characters to the OPEN FOR statement:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_string := RPAD (' ', 4001);
  6    OPEN l_rc FOR 'select :1 from dual' USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6

Oops, got an error. Let’s also try the EXECUTE IMMEDIATE statement with a string greater than 4,000 characters:

eddie@db11gr2> DECLARE
  2    l_string VARCHAR2 (4001);
  3  BEGIN
  4    l_string := RPAD (' ', 4001);
  5    EXECUTE immediate 'select :1 from user_objects where rownum = 1'
  6      INTO l_string USING l_string;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

Got the same error: “ORA-01460: unimplemented or unreasonable conversion requested”.

How about if we pass a NULL:

eddie@db11gr2> DECLARE
  2    l_rc sys_refcursor;
  3  BEGIN
  4    OPEN l_rc FOR 'select :1 from dual' USING NULL;
  5  END;
  6  /
  OPEN l_rc FOR 'select :1 from dual' USING NULL;
                                            *
ERROR at line 4:
ORA-06550: line 4, column 45:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 4, column 3:

In this case we get “PLS-00457: expressions have to be of SQL types”.

Well, there is a restriction on what values you can bind: When binding values to dynamic SQL, only SQL datatypes are supported. You can bind strings, numbers, dates, collections, LOBs, XML documents… However, you cannot bind values having a PL/SQL specific datatype such as Booleans, associative arrays and varchar2 values greater than 4000.

Make sure you keep the above restriction in mind when you use the EXECUTE IMMEDIATE … USING and OPEN FOR … USING statements.

Source and resources:

Comments Off | Filed in Oracle, Tips | Tags: ,