| Please Note: Unless otherwise noted, all of the
PL/SQL
Pipeline's monthly tips are contributed by Steven Feuerstein.
|
 Oracle PL/SQL Tips of the
Month - 1999
January
February
March
April
May
June
July
August
September
October
November
December
1997 Tips
1998 Tips
January's Tip of the Month
As of Oracle 7.3.4 and Oracle 8.0.4, you can now call DBMS_OUTPUT and
DBMS_PIPE from within functions which are then called from within SQL.
This allows you to build a trace feature into your SQL. The following
function, for example, uses DBMS_OUTPUT.PUT_LINE to display the ROWID
of each row touched by a query:
CREATE OR REPLACE FUNCTION traceit (
tab IN VARCHAR2,
rowid_in IN ROWID)
RETURN INTEGER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE (tab || '-' ||
ROWIDTOCHAR (rowid_in));
RETURN 0;
END;
/
This function returns a "dummy" value of zero; it has to be a function to
be callable directly in SQL, but its return value is irrelevant. I can then
use this function inside a query as follows:
SELECT ename, traceit ('emp', ROWID)
FROM emp;
But, but, but! There is one little complication to keep in mind. If I run
the above query on my standard demo emp table, I see this output:
ENAME TRACEIT('EMP',ROWID)
---------- --------------------
SMITH 0
ALLEN 0
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
JAMES 0
FORD 0
MILLER 0
Well, that's odd...where is my trace information? Here, I will produce it
by executing a "do nothing" PL/SQL block:
SQL> BEGIN NULL; END;
2 /
emp-AAAAfBAACAAAAEqAAA
emp-AAAAfBAACAAAAEqAAB
emp-AAAAfBAACAAAAEqAAC
emp-AAAAfBAACAAAAEqAAD
emp-AAAAfBAACAAAAEqAAE
emp-AAAAfBAACAAAAEqAAF
emp-AAAAfBAACAAAAEqAAG
emp-AAAAfBAACAAAAEqAAH
emp-AAAAfBAACAAAAEqAAI
emp-AAAAfBAACAAAAEqAAJ
emp-AAAAfBAACAAAAEqAAK
emp-AAAAfBAACAAAAEqAAL
emp-AAAAfBAACAAAAEqAAM
emp-AAAAfBAACAAAAEqAAN
Ah! Now we see the trace information (and the new Oracle8 extended ROWID
format). Remember that the DBMS_OUTPUT buffer is only flushed to the screen
by SQL*Plus when the PL/SQL block terminates. The "SELECT ... FROM emp"
query I executed is not a PL/SQL block, so when it finished running, the
DBMS_OUTPUT buffer was not flushed. It was then necessary to run some PL/SQL
block after the SQL statement.
Of course, if you run the above query from within a PL/SQL program,
its output will be displayed when the program finishes.
February's Tip of the Month
Used to be that the legal range for date values in Oracle was January
1, 4712 BCE to January 1, 4712 AD. Now, we will all probably be ready
to agree that seems like enough of a date range, but Oracle has expanded
even this large range. How do I know? Well, a Pipeliner recently asked
the what the maximum date allowed really is, because Dave found that he
could go past that 4712 limit.
Rather than look up the value in some reference book or other (including
mine), I decided to determine the maximum date by running a test (surely
a more definitive answer can be found that way):
CREATE OR REPLACE PROCEDURE showmaxdate
IS
old_date DATE;
curr_date DATE := SYSDATE;
BEGIN
LOOP
old_date := curr_date;
curr_date := curr_date + 1;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
DBMS_OUTPUT.PUT_LINE ('Last date: ' ||
TO_CHAR (old_Date, 'MM-DD-YYYY'));
END;
/
And here are the results:
SQL> exec showmaxdate
ORA-01841: (full) year must be between -4713 and +9999,
and not be 0
Last date: 12-31-9999
I don't have Oracle7 running in my office, so I cannot test this same script
under 7.3. If anyone reading this does run the test, though, we will post
the results.
March's Tip of the Month
Umesh Patel of Oracle Corporation contributed a very useful technique
for sending email using PL/SQL, when you don't have the wonders of Oracle8
available to you. Here is what he wrote:
You can easliy implement this one [email from PL/SQL] if you work in
the Unix environment. The following steps show you how.
Create the table mail_data with following the definition:
mailid number not null
seqno varchar2 not null,
body long
Then insert values into the above table as your PL/SQL code executes.
Here is an example of the kind of rows that might be placed in this table
for an email message:
100,1,'/usr/lib/sendmail
-ba -t < 100,2,'FROM : {actual_from text)'
100,3,'TO : ' {actual_to_text}'
100,4,'Cc : ' {actual_cc_text}'
100,5,'Subject : {acutal_subject_text}'
100,6,'body_text'
100,7,'EOF!'
So you've got the data sitting in the table. Now the trick is to extract
that text within a program that can send some email. Here is Umesh's suggestion
for doing this:
Create and execute the following shell script send_mail.sh as frequently
as necessary by submitting it to cron job.
export ORACLE_SID=
orasid
export ORAENV_ASK=
NO.
oraenv
-d! -f1 dbmail.sh > /path/dbmail.sh
chmod 700 /path/dbmail.sh
/path/dbmail.sh
As you can see, this Unix shell script executes a SQL*Plus script named
genmail.sql. Here is the definition of that script:
set serveroutput on size 1000000
set feedback off
set echo off
spool /path/dbmail.sh
exec getmail
spool off
set serveroutput off
set feedback on
set echo on
But wait! This script runs a stored procedure, defined as follows:
CREATE OR REPLACE PROCEDURE getmail
IS
BEGIN
FOR mail IN (SELECT body
FROM mail_data
ORDER BY mailid, seqno)
LOOP
DBMS_OUTPUT.put_line (mail.body);
END LOOP;
END;
With all of these pieces in place, you will be sending email in Unix
from PL/SQL in no time at all!
April's Tip of the Month
In-line Conditional Logic for PL/SQL
PL/SQL does not support DECODE natively; instead, you must call it inside
a SELECT statement. That's too bad. DECODE can be very handy because (a)
it implements a CASE statement, unavailable in PL/SQL and (b) allows you
to do "in-line" conditional logic.
In other words, rather than write an IF statement like this:
IF myvar = 'A'
THEN
mydate := SYSDATE;
ELSE
mydate := SYSDATE + 10;
END IF;
You could use DECODE like this:
SELECT DECODE (myvar, 'A', SYSDATE, SYSDATE+10)
INTO mydate
FROM dual;
Well, it's not like we're really saving much code, are we? And personally
I hate using SELECT FROM dual in my code. It is such an awkward work-around.
So I came up with my one DECODE-like program in PL/SQL: the ifelse function.
Here it is:
CREATE OR REPLACE FUNCTION ifelse
(bool_in IN BOOLEAN, tval_in IN VARCHAR2, fval_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF bool_in
THEN
RETURN tval_in;
ELSE
RETURN fval_in;
END IF;
END;
Now I can transform the earlier IF statement to nothing more than this:
mydate := ifelse (myvar = 'A', SYSDATE, SYSDATE+10);
This is especially handy when you are constructing a string from multiple
components, some of which may be null. Here is an example of my use of
the ifelse function to do just that in a "watch" or debug package available
in the demo.zip file in the Archives (see PL/SQL Seminar Files entry):
msg :=
'***WATCHing at: ' || PLV.now || CHR(10) ||
' Context: ' || prog || CHR(10) ||
ifelse (val IS NOT NULL, ' Message: ' || val || CHR(10), 'NO VAL') ||
ifelse (using_cs, ' Callstack: ' || DBMS_UTILITY.FORMAT_CALL_STACK,
NULL);
Without ifelse, I would have to write something like this:
msg :=
'***WATCHing at: ' || PLV.now || CHR(10) ||
' Context: ' || prog || CHR(10);
IF val IS NOT NULL
THEN
msg := msg || ' Message: ' || val || CHR(10);
ELSE
msg := msg || 'NO VAL';
END IF;
IF using_cs
THEN
msg := msg || ' Callstack: ' || DBMS_UTILITY.FORMAT_CALL_STACK;
END IF;
Finally, if you like the ifelse function, I suggest you overload it into
a package, as I have done in the PLV package of PL/Vision:
PACKAGE PLV
IS
FUNCTION ifelse (bool_in IN BOOLEAN, tval_in IN BOOLEAN,
fval_in IN BOOLEAN)
RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (ifelse, WNDS);
FUNCTION ifelse (bool_in IN BOOLEAN, tval_in IN DATE,
fval_in IN DATE)
RETURN DATE;
PRAGMA RESTRICT_REFERENCES (ifelse, WNDS);
FUNCTION ifelse (bool_in IN BOOLEAN, tval_in IN NUMBER,
fval_in IN NUMBER)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (ifelse, WNDS);
FUNCTION ifelse (bool_in IN BOOLEAN, tval_in IN VARCHAR2,
fval_in IN VARCHAR2)
RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (ifelse, WNDS);
END;
From: Solomon Yakobson
To: Steven Feuerstein
function IFELSE mimicking DECODE is quite handy, however it does not
mimic it 100%. There is a difference between decoded expressions and the
function IFELSE. Actually, the same difference exists between IF statement
mimicking DECODE and function IFELSE. I completely agree that DECODE can
be 100% mimicked by IF statement:
SELECT DECODE (myvar, 'A', SYSDATE, SYSDATE+10)
INTO mydate
FROM dual;
is same as:
IF myvar = 'A'
THEN
mydate := SYSDATE;
ELSE
mydate := SYSDATE + 10;
END IF;
However, it is different from:
mydate := ifelse (myvar = 'A', SYSDATE, SYSDATE+10);
I will explain why. In both DECODE and IF statement above the logic is
as follows:
- Compare myvar with 'A'
- If result is true calculate and return SYSDATE
- If result is false calculate and return SYSDATE + 10
Now lets take a look at function call to IFELSE:
- Compare myvar to 'A' and assign it to some temp variable to be passed
to IFELSE as first argument.
- Calculate sysdate and assign it to some temp variable to be passed
to IFELSE as second argument.
- calculate sysdate+10 and assign it to some temp variable to be passed
to IFELSE as third argument.
As you can see all decoded expressions are calculated BEFORE applying
function IFELSE logic, another words before decoding. This means that
if decoded expressions are interdependent we will get wrong results. I
agree, it is not a good practice to use interdependent expressions in
one statement, but... I just simply wanted to point this out. So if someone
is using interdependent objects, function IFELSE will not work same way
as DECODE (or IF statement) and will produce wrong results. For example:
SQL> create or replace package pkg1
2 as
3 X number := 0;
4 function f1 return number;
5 pragma restrict_references(f1,wnds);
6 end;
7 /
Package created.
SQL> create or replace package body pkg1
2 as
3 function f1 return number
4 is
5 begin
6 x := x + 1;
7 return x;
8 end;
9 end;
10 /
Package body created.
SQL> select decode('X','Y',pkg1.f1,pkg1.f1+10) from dual;
DECODE('X','Y',PKG1.F1,PKG1.F1+10)
------------------------------------
11
SQL> set serveroutput on
SQL> begin
2 pkg1.x := 0;
3 dbms_output.put_line('DECODE via IF Statement');
4 if 'X' = 'Y'
5 then
6 dbms_output.put_line(pkg1.f1);
7 else
8 dbms_output.put_line(pkg1.f1+10);
9 end if;
10 end;
11 /
DECODE via IF Statement
11
PL/SQL procedure successfully completed.
SQL> begin
2 pkg1.x := 0;
3 dbms_output.put_line('DECODE via IFELSE Function.');
4 dbms_output.put_line(ifelse('X' = 'Y',pkg1.f1,pkg1.f1+10));
5 end;
6 /
DECODE via IFELSE Function.
12
PL/SQL procedure successfully completed.
As you can see, function IFELSE returned wrong (12 not 11) result. Why
12? DECODE and IF statement checked 'X' = 'Y' and since it is FALSE calculated
expression PKG1.F1+10 only. Therefore, package variable PKG1.X was 0 when
PKG1.F1+10 was calculated and PKG1.F1+10 resulted in 11. Function IFELSE
call calculated 'X'='Y', which resulted in FALSE, then calculated PKG1.F1
which resulted in 1, however it incremented PKG1.X to 1. Then PKG1.F1+10
was calculated and since PKG1.X was changed to 1, resulted in 12. Only
then function IFELSE was actually executed. Since first argument was FALSE
it returned whatever was passed as third argument - 12.
There is one more aspect in the difference between DECODE and IFELSE.
Since (as I explained in the posting) DECODE calculates only one of the
decoded expressions and IFELSE calculates all of them, in can, in case
of complex expressions, create some performance degradation.
May's Tip of the Month
Some Tips on Handling Mutating Table Errors
Susan Kleinfelter (skleinfelter@logility.com)
offers these useful thoughts about dealing with mutating table errors
(with some minor editing by that Feuerstein guy):
I noticed recently at least three questions in Pipetalk about the ORA-4091
error (table is mutating), where an insert or update trigger on table
A tries to write to table B, but B has a FK back to A, causing the problem.
A traditional to this problem solution (as offered, for example, in Scott
Urman's Oracle8 PL/SQL Programming) involves using two triggers on A,
one row level and one statement level. This approach often does not take
into account any error handling, so I am suggest below some improvements
(and some remaining problems).
The main idea is this: when the row level trigger on A fires, save the
needed info from A into a PL/SQL table defined in a package. Then when
the after statement level trigger fires (and A is no longer mutating),
write the accumulated info from the PL/SQL table out to table B. After
writing to B, delete all the rows from PL/SQL table so that it is ready
for the next DML operation.
Problem 1: let's say the row-level trigger stored some info from A in
the PL/SQL table, but when statement-level trigger attempts to write to
B you get an error, such as unique constraint violated (may indicate a
design flaw elsewhere, but I often had this problem during testing when
I failed to blow all my test data away). You will not arrive at the line
of code that empties the PL/SQL table, so old data will remain in it from
this statement when it is called for the next statement.
Solution: add an error handler, and always empty the PL/SQL table when
any error occurs. Then re-raise the error to the caller unless it is something
you can handle. However, this still leaves...
Problem 2: If your row-level trigger on A is before insert or update,
you may add something to the PL/SQL table, but then the insert or update
to A may fail, due perhaps to a constraint violation. This error does
not propagate out to the before trigger, which has already completed execution
for this row, so there is no way to empty the PL/SQL table in response
to this error. For this reason it is better to add the info to PL/SQL
table in an after row-level trigger only.
Problem 3: Imagine a single statement is inserting 10 rows to A, and
first 5 succeed, so 5 rows get added to PL/SQL table, then 6th row fails
and caller gets the error (no trigger gets the error if it is on the actual
insert or update, because triggers only execute before or after, not during).
The after statement trigger does not fire in this case because the statement
didn't finish, so the 5 rows don't get written from PL/SQL table to B
and also don't get removed from PL/SQL table. Next time an insert or update
succeeds on A, they will be written to B even though their corresponding
A rows did not get inserted. Solution to this: I added a before insert
or update statement level trigger to A that empties out the PL/SQL table,
so it never begins a statement with data left over from an earlier statement
that errored out.
Just thought I would share my experience with this, since I have found
a number of the published solutions tantalizing but woefully incomplete.
- Susan
June's Tip of the Month
Problem: Generate PDF files and HTML pages from Oracle data for publication
on the Internet.:
Solution from Stanislav Pichmanov: From the PL/SQL cartridge application,
we place requests in an Oracle database table. Then we use the Report
Queue Engine or Oracle Reports, running outside of the application server,
to generates reports in HTML and PDF format whenever it finds requests
in that Oracle table. Once the HTML or PDF file is generated, we can display
it easily in our browser
July's Tip of the Month
The Dangers of Flushing
To a file, that is. You use UTL_FILE.PUT_LINE to write a line to a file,
and while your program is still running and generating more output, you
look at the file. To your severe disappointment, you don't see anything
(or at least not the latest data) in the file. What's the problem? The
actual write to disk is usually an asynchronous process; the operating
system decides when to "flush" the data to disk to improve throughput.
UTL_FILE offers the FFLUSH procedure to force all information physically
out to the specified file, as in:
DECLARE
fID UTL_FILE.FILE_TYPE;
BEGIN
...
UTL_FILE.FFLUSH (fID);
If you want to make sure you see data in the file as soon as it is written,
you could call FFLUSH after every PUT_LINE call. You could even create
a little "combo" program to handle this for you:
CREATE PROCEDURE file_put (
fID IN UTL_FILE.FILE_TYPE,
line IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUT_LINE (fID, line);
UTL_FILE.FFLUSH (fID);
END;
/
You should, however, be judicious in your calls to FFLUSH. It could slow
down quite dramatically the performance of your application. This experience
was related in Pipetalk this month in the File I/O Conference, under the
title "SLOOOOOOOOOOOW".
You might want to build an occasional flush into your writes to a file.
You could build a package about UTL_FILE that sets a counter to 0 when
you open the file. Then every time you call PUT_LINE, it increments the
counter. When the counter hits the specified threshold, FFLUSH is called
automatically.
August's Tip of the Month
A common request in the world of SQL*Plus is to change the prompt to
reflect information about the environment, such as the Oracle instance.
Mike Ault, RevealNet Oracle Administration Knowledge Base author, offers
this code to accomplish the prompt change:
column xdb_name new_value xdb noprint;
select name||': ' xdb_name from v$database;
set sqlprompt &xdb
You can put these three lines inside your glogin.sql or login.sql scripts
so that the prompt is set when you start up SQL*Plus (note that when you
CONNECT from within SQL*Plus to another schema you will have to run one
of these files manually to reset the prompt).
So there's one example of a SQL*Plus prompt change. Do any of our brilliant
and creative Pipeliners from around the world have any other useful prompt
settings that they use and would like to share? If so, please go to Pipetalk
and add your entry in the new FAQ conference under the "Modifying the
SQL*Plus prompt" posting.
Thanks, SF
September's Tip of the Month
Passing Environmental Variables to an External Procedure
When you call an external procedure, Oracle passes only three environment
variables - ORACLE_HOME, ORACLE_SID, and LD_LIBRARY_PATH - to that external
procedure. If you want to pass any other environment variable besides
those three, you need to edit the listener.ora file and add a line of
this format:
(ENVS='environment_variable=value,environment_variable=
value,...environment_variable=value')
to the extproc service. When you define these environmental variables,
you should type them all on the same line, separating each by a comma
without any whitespace characters, such as spaces or tabs.
Note: NLS_NUMERIC_CHARACTERS must not be the last entry in the ENVS list.
Suppose (as was asked on Pipetalk and answered by Solomon Yakobson, who
provides this month's tip) that you want to pass the location of the external
procedure so that it does not have to be hard-coded in your PL/SQL application.
In this case you would add the following line:
(ENVS='PATH=/usr/bin')
October's Tip of the Month
Querying from an Index-By Table
So you've defined an index-by table (formerly known as PL/SQL tables),
and you want to be able use SQL to query the results. Sadly, index-by
tables are 100% programmatic PL/SQL constructs and cannot be referenced
natively from within SQL. There is, however, a workaround, one that was
suggested by several people on the PL/SQL Pipeline (most notably and with
content for this tip drawn from Susanna Batson, at smbatson@technologist.com):
Instead of referencing the index-by table contents directly, you can
call a function that returns the value in a row.
Here is one such example:
SELECT Col1, Col2
FROM My_DB_Table
WHERE Col3 IN (
SELECT ibtable_row (ColId)
FROM Place_holder_table);
where Place_holder_table is a table that simply provides a context in
which to reference the ibtable_row function, which would look something
like this..
CREATE OR REPLACE FUNCTION ibtable_row (colid IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN mypkg.ibtable (colid);
END;
Where mypkg is the package that contains the declaration of ibtable. If
the function is defined in the same package, you do not have to qualify
the reference to the index-by table.
The place_holder_table might be dual, so that it only returns one
value/row. You could also correlate the row number passed to ibtable_row
with information in that table.
November's Tip of the
Month
Oracle Support for PL/SQL...
As I travel to different locales for seminars and
presentations on PL/SQL, I am asked rather consistently the following
questions: Is Oracle going to drop support for PL/SQL and just move
to offering Java in the database? For Oracle's official positioning
on this, check out the following URL:
Regrettably, Oracle has removed this URL from their web site and this link is no longer valid. We apologize for the inconvenience.
RevealNet Technical Support
March 31, 2000
http://www.oracle.com/java/8i/faq/815faq0005.html
December's Tip of the Month
Specifying UTL_FILE Directories
With the help of my friend and Oracle Technologist, Jay Smith
(jaysmith@tri-smith.com), I discovered something new and different
about the way the UTL_FILE_DIR parameter is handled, at least in Oracle8i.
The UTL_FILE_DIR parameter specifies one or more directories in which
you can read/write files with the UTL_FILE package. You can list multiple
directories in one statement, separated by a semi-colon, as in:
UTL_FILE_DIR=/tmp;/tmp/accounts;/apps/oracle/ar
You can also specify these directories in separate parameter entries,
as in:
UTL_FILE_DIR=/tmp
UTL_FILE_DIR=/tmp/accounts
UTL_FILE_DIR=/apps/oracle/ar
Our recent discovery is that if you do not list these multiple entries
CONTIGUOUSLY (ie, if you separate the entries with other parameter
settings), then only the last set (one or more) processed by Oracle at
instance startup will be recognized.
So if I specify my three directories shown above in this fashion:
UTL_FILE_DIR=/tmp
compatible = 8.1.0
UTL_FILE_DIR=/tmp/accounts
UTL_FILE_DIR=/apps/oracle/ar
Then the settings in the database will not include /tmp, as can be
verified with this query:
SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir';
VALUE
----------------------------------------------------------------
/tmp/accounts, /apps/oracle/ar
|
