 Oracle PL/SQL Tips of the Month -
2004
| January | Updating the Individual Elements of a Varray |
| February | Override Default Optimizer Behavior for SQL in PL/SQL Blocks |
| March | Inserting a "Generic Record Type" Using ANYDATA/ANYTYPE Types |
| April | Separating Comma Separated Strings |
| May | Use a Parameter List to Reference Variables Declared Outside Your Cursors |
| June | PL/SQL Upsert |
| July | Pseudo-IF and CASE Statements In SQL*Plus Scripts |
| August | Defining Subtypes for Variables with Identical Datatypes |
| September | Correcting Values When Writing to Excel Files |
| October | Proper Use of SELECT COUNT(*) |
| November | Oracle 10g Bulk Bind Improvements |
| December | Creating Surrogate Numeric Keys for Tables |
January's Tip of the Month
Updating the Individual Elements of a Varray
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).
The PL/SQL User's Guide and Reference (Release 9.2) contains the following:
"Currently, you cannot reference the individual elements of a varray in an INSERT,
UPDATE, or DELETE statement. You must retrieve the entire varray, use PL/SQL
procedural statements to add, delete, or update its elements, and then store the
changed varray back in the database".
However, there is a solution for updating individual elements of a varray in SQL.
First, let us consider a varray of scalar values:
CREATE TYPE array_of_scalars_t AS ARRAY (3) OF NUMBER (4)
/
CREATE TABLE table1 (
id NUMBER (4) NOT NULL PRIMARY KEY,
array1 array_of_scalars_t NOT NULL)
/
INSERT INTO table1 VALUES (10, array_of_scalars_t (101, 102, 103));
INSERT INTO table1 VALUES (20, array_of_scalars_t (201, 202, 203));
INSERT INTO table1 VALUES (30, array_of_scalars_t (301, 302, 303));
INSERT INTO table1 VALUES (40, array_of_scalars_t (401, 402, 403));
INSERT INTO table1 VALUES (50, array_of_scalars_t (501, 502, 503));
We want to do the following:
UPDATE table1
SET array1 (2) = 999
WHERE id = 30
We can do this with the following statement:
UPDATE table1
SET array1 =
CAST (MULTISET (SELECT DECODE (ROWNUM, 2, 999, COLUMN_VALUE)
FROM TABLE (SELECT array1
FROM table1
WHERE id = 30))
AS array_of_scalars_t)
WHERE id = 30
/
Explanation:
"TABLE (SELECT array1 FROM table1 WHERE id = 30)"
creates 3 rows of a "temporary table" from column array1 (in row with id = 30)
"SELECT (DECODE (ROWNUM, 2, 999, COLUMN_VALUE) FROM ..."
replaces old value (302) in the second row of "temporary table" with new value (999)
"CAST (MULTISET (... AS array_of_scalars_t)"
creates varray from "temporary table"
The following demonstrates how to UPDATE several rows:
UPDATE table1 outer_table
SET array1 =
CAST (MULTISET (SELECT DECODE (ROWNUM, 2, 999, COLUMN_VALUE)
FROM TABLE (SELECT array1
FROM table1
WHERE ROWID = outer_table.ROWID))
AS array_of_scalars_t)
WHERE id > 30
/
If, instead of an array of scalars, we have a varray of objects, then we can do the following:
CREATE TYPE pair_t AS OBJECT (
a NUMBER (4),
b NUMBER (4))
/
CREATE TYPE array_of_pairs_t AS ARRAY (3) OF pair_t
/
CREATE TABLE table2 (
id NUMBER (4) NOT NULL PRIMARY KEY,
array2 array_of_pairs_t NOT NULL)
/
INSERT INTO table2 VALUES
(10, array_of_pairs_t (pair_t (101, 102), pair_t (103, 104), pair_t (105, 106)));
INSERT INTO table2 VALUES
(20, array_of_pairs_t (pair_t (201, 202), pair_t (203, 204), pair_t (205, 206)));
INSERT INTO table2 VALUES
(30, array_of_pairs_t (pair_t (301, 302), pair_t (303, 304), pair_t (305, 306)));
INSERT INTO table2 VALUES
(40, array_of_pairs_t (pair_t (401, 402), pair_t (403, 404), pair_t (405, 406)));
INSERT INTO table2 VALUES
(50, array_of_pairs_t (pair_t (501, 502), pair_t (503, 504), pair_t (505, 506)));
UPDATE table2
SET array2 =
CAST (MULTISET (SELECT DECODE (ROWNUM, 2, pair_t (993, 994), VALUE (temp_tab))
FROM TABLE (SELECT array2
FROM table2
WHERE id = 30) temp_tab)
AS array_of_pairs_t)
WHERE id = 30
/
UPDATE table2 outer_table
SET array2 =
CAST (MULTISET (SELECT DECODE (ROWNUM, 2, pair_t (993, 994), VALUE (temp_tab))
FROM TABLE (SELECT array2
FROM table2
WHERE ROWID = outer_table.ROWID) temp_tab)
AS array_of_pairs_t)
WHERE id > 30
/
This works in both Oracle 8i and 9i.
February's Tip of the Month
Override Default Optimizer Behavior for SQL in PL/SQL Blocks
Compliments of Garry Robinson, (OracleAdvice.com).
Oracle's default behavior dictates that SQL statements embedded in a PL/SQL block are
always executed with the ALL_ROWS optimization method. For OLTP applications that rely on
packaged stored procedures, this can be bad news for response times. OLTP queries need to
return results back to the user as quickly as possible which is why OLTP instances generally
have parameter optimizer_mode set to FIRST_ROWS.
There are two ways that you can override this default behavior and force the SQL inside
PL/SQL blocks to use the FIRST_ROWS optimization method. The first way is to use a hint when
declaring the cursor. For example
cursor C1 is
select /*+ FIRST_ROWS */ empno, name
from emp;
This is fine when you are still developing or testing an application but in most cases it
won't be easy or straightforward to change the code in a production database; especially if
the application has not been developed in-house. A further disadvantage is that there is no
instance parameter that can "turn off" the hint if you decide to call the same procedure from
a batch job.
For this reason, it is useful to know about the undocumented parameter _optimizer_mode_force.
In Oracle 8i and 9i, the default setting for this parameter is TRUE, which forces Oracle to
ignore the optimizer_mode setting and behave in the following way when executing user recursive
SQL (SQL inside a PL/SQL block):
| INIT.ORA OPTIMIZER_MODE |
Mode used inside PLSQL block |
| RULE |
RULE |
| CHOOSE |
ALL_ROWS |
| ALL_ROWS |
ALL_ROWS |
| FIRST_ROWS |
ALL_ROWS |
Setting _optimizer_mode_force to FALSE allows Oracle to behave in the way you might expect
when executing SQL in stored procedures.
March's Tip of the Month
Inserting a "Generic Record Type" Using ANYDATA/ANYTYPE Types
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr).
As of Oracle 9, we can use the new datatypes ANYDATA, ANYTYPE and ANYDATASET. As of
Oracle 9.2, we can insert into or update a SQL table by specifying
a PL/SQL record variable rather than specifying each record attribute separately.
This PL/SQL tip shows (ine Example 2) how to use both features together.
How can we build something like the following generic procedure for inserting a record
into any table?
PROCEDURE gen_insert (
p_table VARCHAR2,
p_record "generic_record_type")
IS
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO ' || p_table || ' VALUES (:record)'
USING IN p_record;
END;
So, if p_table = 'EMP' then p_record is of type EMP%ROWTYPE, and if p_table = 'DEPT' then
p_record is of type DEPT%ROWTYPE, etc.
Example 1: If we have "pure" object tables (not object-relational or "pure" relational tables),
then the solution is relatively simple:
CREATE TYPE dept_obj_type AS OBJECT (
deptno NUMBER (2),
dname VARCHAR2 (20)
)
/
CREATE TABLE dept_obj_table OF dept_obj_type
/
CREATE TYPE emp_obj_type AS OBJECT (
empno NUMBER (4),
ename VARCHAR2 (20),
deptno NUMBER (2)
)
/
CREATE TABLE emp_obj_table OF emp_obj_type
/
CREATE OR REPLACE PROCEDURE gen_insert1 (
p_table VARCHAR2,
p_anydata ANYDATA)
IS
l_statement VARCHAR2 (32000);
BEGIN
l_statement :=
' DECLARE' ||
' l_anydata ANYDATA := :p_anydata;' ||
' l_object ' || p_anydata.GetTypeName || ';' ||
' l_result_code PLS_INTEGER;' ||
' BEGIN ' ||
' l_result_code := l_anydata.GetObject (l_object);' ||
' INSERT INTO ' || p_table || ' VALUES (l_object);' ||
' END;';
EXECUTE IMMEDIATE l_statement USING IN p_anydata;
END;
/
DECLARE
l_dept dept_obj_type;
l_emp emp_obj_type;
BEGIN
l_dept := dept_obj_type (10, 'dept 10');
l_emp := emp_obj_type (2000, 'emp 2000', 10);
gen_insert1 (
p_table => 'dept_obj_table',
p_anydata => ANYDATA.ConvertObject (l_dept));
gen_insert1 (
p_table => 'emp_obj_table',
p_anydata => ANYDATA.ConvertObject (l_emp));
END;
/
Example 2: If we have "pure" relational tables, then the solution is not so simple:
CREATE TABLE dept_rel_table (
deptno NUMBER (2),
dname VARCHAR2 (20)
)
/
CREATE TABLE emp_rel_table (
empno NUMBER (4),
ename VARCHAR2 (20),
deptno NUMBER (2)
)
/
CREATE OR REPLACE PACKAGE create_anydata IS
g_dept_rel_table dept_rel_table%ROWTYPE;
FUNCTION dept_anytype RETURN ANYTYPE;
FUNCTION dept_anydata (
p_anytype ANYTYPE,
p_deptno NUMBER,
p_dname VARCHAR2)
RETURN ANYDATA;
g_emp_rel_table emp_rel_table%ROWTYPE;
FUNCTION emp_anytype RETURN ANYTYPE;
FUNCTION emp_anydata (
p_anytype ANYTYPE,
p_empno NUMBER,
p_ename VARCHAR2,
p_deptno NUMBER)
RETURN ANYDATA;
END create_anydata;
/
CREATE OR REPLACE PACKAGE BODY create_anydata IS
FUNCTION dept_anytype RETURN ANYTYPE IS
l_dept_anytype ANYTYPE;
BEGIN
ANYTYPE.BeginCreate (
typecode => DBMS_TYPES.TYPECODE_OBJECT,
atype => l_dept_anytype);
l_dept_anytype.AddAttr (
aname => 'deptno',
typecode => DBMS_TYPES.TYPECODE_NUMBER,
prec => 2,
scale => 0,
len => NULL, csid => NULL, csfrm => NULL);
l_dept_anytype.AddAttr (
aname => 'dname',
typecode => DBMS_TYPES.TYPECODE_VARCHAR2,
len => 20,
prec => NULL, scale => NULL, csid => NULL, csfrm => NULL);
l_dept_anytype.EndCreate;
RETURN l_dept_anytype;
END;
FUNCTION dept_anydata (
p_anytype ANYTYPE,
p_deptno NUMBER,
p_dname VARCHAR2)
RETURN ANYDATA
IS
l_anytype ANYTYPE := p_anytype;
l_anydata ANYDATA;
BEGIN
ANYDATA.BeginCreate (
dtype => l_anytype,
adata => l_anydata);
l_anydata.SetNumber (num => p_deptno);
l_anydata.SetVarchar2 (c => p_dname);
l_anydata.EndCreate;
RETURN l_anydata;
END;
FUNCTION emp_anytype RETURN ANYTYPE IS
l_emp_anytype ANYTYPE;
BEGIN
ANYTYPE.BeginCreate (
typecode => DBMS_TYPES.TYPECODE_OBJECT,
atype => l_emp_anytype);
l_emp_anytype.AddAttr (
aname => 'empno',
typecode => DBMS_TYPES.TYPECODE_NUMBER,
prec => 4,
scale => 0,
len => NULL, csid => NULL, csfrm => NULL);
l_emp_anytype.AddAttr (
aname => 'ename',
typecode => DBMS_TYPES.TYPECODE_VARCHAR2,
len => 20,
prec => NULL, scale => NULL, csid => NULL, csfrm => NULL);
l_emp_anytype.AddAttr (
aname => 'deptno',
typecode => DBMS_TYPES.TYPECODE_NUMBER,
prec => 2,
scale => 0,
len => NULL, csid => NULL, csfrm => NULL);
l_emp_anytype.EndCreate;
RETURN l_emp_anytype;
END;
FUNCTION emp_anydata (
p_anytype ANYTYPE,
p_empno NUMBER,
p_ename VARCHAR2,
p_deptno NUMBER)
RETURN ANYDATA
IS
l_anytype ANYTYPE := p_anytype;
l_anydata ANYDATA;
BEGIN
ANYDATA.BeginCreate (
dtype => l_anytype,
adata => l_anydata);
l_anydata.SetNumber (num => p_empno);
l_anydata.SetVarchar2 (c => p_ename);
l_anydata.SetNumber (num => p_deptno);
l_anydata.EndCreate;
RETURN l_anydata;
END;
END create_anydata;
/
CREATE OR REPLACE PROCEDURE gen_insert2 (
p_table VARCHAR2,
p_anydata ANYDATA)
IS
l_anydata ANYDATA;
l_anytype ANYTYPE;
l_typeid PLS_INTEGER;
l_attr_typeid PLS_INTEGER;
l_prec PLS_INTEGER;
l_scale PLS_INTEGER;
l_len PLS_INTEGER;
l_csid PLS_INTEGER;
l_csfrm PLS_INTEGER;
l_schema_name VARCHAR2(30);
l_type_name VARCHAR2(30);
l_version VARCHAR2(30);
l_count PLS_INTEGER;
l_attr_elt_type ANYTYPE;
l_aname VARCHAR2(30);
l_statement VARCHAR2 (32000);
l_result_code PLS_INTEGER;
l_number NUMBER;
l_string VARCHAR2 (32000);
l_date DATE;
BEGIN
l_anydata := p_anydata;
l_typeid := l_anydata.GetType (typ => l_anytype);
l_typeid := l_anytype.GetInfo (
l_prec, l_scale, l_len, l_csid, l_csfrm,
l_schema_name, l_type_name, l_version, l_count);
l_anydata.PieceWise;
FOR pos IN 1..l_count LOOP
l_attr_typeid := l_anytype.GetAttrElemInfo (
pos,
l_prec, l_scale, l_len, l_csid, l_csfrm,
l_attr_elt_type, l_aname);
l_statement :=
' BEGIN ' ||
' create_anydata.g_' || p_table || '.' || l_aname || ' := :l_column;' ||
' END;';
CASE l_attr_typeid
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
l_result_code := l_anydata.GetNumber (l_number);
EXECUTE IMMEDIATE l_statement USING IN l_number;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
l_result_code := l_anydata.GetVarchar2 (l_string);
EXECUTE IMMEDIATE l_statement USING IN l_string;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
l_result_code := l_anydata.GetDate (l_date);
EXECUTE IMMEDIATE l_statement USING IN l_date;
ELSE
NULL;
END CASE;
END LOOP;
l_statement :=
' BEGIN' ||
' INSERT INTO ' || p_table ||
' VALUES create_anydata.g_' || p_table || ';' ||
' END;';
EXECUTE IMMEDIATE l_statement;
END;
/
DECLARE
l_dept_anydata ANYDATA;
l_emp_anydata ANYDATA;
BEGIN
l_dept_anydata :=
create_anydata.dept_anydata (
p_anytype => create_anydata.dept_anytype,
p_deptno => 10,
p_dname => 'dept 10');
l_emp_anydata :=
create_anydata.emp_anydata (
p_anytype => create_anydata.emp_anytype,
p_empno => 2000,
p_ename => 'emp 2000',
p_deptno => 10);
gen_insert2 (
p_table => 'dept_rel_table',
p_anydata => l_dept_anydata);
gen_insert2 (
p_table => 'emp_rel_table',
p_anydata => l_emp_anydata);
END;
/
April's Tip of the Month
Separating Comma Separated Strings
Compliments of Ramesh Periyasamy, for Oracle Magazine.
This function will split an input string separated by commas into a string enclosed with
single quotes. This is helful in applying conditions in dynamic SQL.
dyn_string1 Function:
CREATE OR REPLACE FUNCTION dyn_string1 (ab VARCHAR2)
RETURN VARCHAR2
AS
t VARCHAR2 (2) := ',';
t1 VARCHAR2 (2) := '''';
t2 VARCHAR2 (32767);
t_check NUMBER (10);
a VARCHAR2 (32767);
BEGIN
a := ab;
-- dbms_output.put_line('The string passed is '||a);
t_check := INSTR (a, ',');
-- dbms_output.put_line('The instr value is '||t_check);
IF t_check != 0
THEN
t2 := t1 || t || t1;
a := REPLACE (a, ',', t2);
a := '(' || '''' || a || '''' || ')';
-- dbms_output.put_line(a);
ELSE
a := a;
a := '(' || '''' || a || '''' || ')';
END IF;
RETURN a;
END;
Example:
select dyn_string1('2334,3! 45,5656,6767,7878,78989,8989,8989,
9090,9090,90,90,9090,90,090,,9090,909)
from dual
Output:
DYN_STRING1('2334,345,5656,6767,7878,78989,8989,8989,
9090,9090,90,90,9090,90,090,9090,909)
---------------------------------------------------------
('2334','345','5656','6767','7878','78989','8989','8989',
'9090','9090','90','90','9090','90','090','','9090','909')
To use this dyn_string1 function along with dynamic SQL:
CREATE OR REPLACE PROCEDURE test(actcode IN VARCHAR2,fr_date IN DATE,to_date IN DATE,)
AS
B date;
C date;
actcode VARCHAR2(32767);
sql_stmt varchar2(32767);
BEGIN
a:=dyn_string1(actcode);
b:=fr_date;
c:=to_date;
sql_stmt:='INSERT INTO Table2 select A1.NYCWAY_CASE_NUMBER
FROM table1 a
WHERE A.column1 in '||a||' AND
A.column2 BETWEEN '||''''||b||''''||' AND '||''''||c||''''||' ;
execute immediate sql_stmt ;
END;
May's Tip of the Month
Use a Parameter List to Reference Variables Declared Outside Your Cursors
Compliments of Steven Feuerstein, Pipeline SYSOP.
If your cursor needs to reference data from within your program (whether a local variable, program
parameter or packaged/global variable), you should pass that value into the cursor as a parameter.
Avoid direct reference to variables declared outside your cursor definition.
By relying on parameters, your cursors will be easier to debug and maintain. You are also more likely
to reuse those cursors in a variety of contexts.
The following procedure declares two cursors. Cursor C2 is parameterized and thus more readable and
maintainable (e.g. changing v_name to v_input_name would require only changing the declaration and its
usage within the code – with no effect whatsoever on the SQL). C2 is also more reusable (i.e. more
easily cut and paste into some other code with a reduced chance of error).
CREATE PROCEDURE parameterized_cursor (v_name IN VARCHAR, v_count OUT INTEGER)
AS
CURSOR c1
IS
SELECT COUNT (*)
FROM all_objects
WHERE object_name = v_name OR owner = v_name;
CURSOR c2 (name_in IN VARCHAR2)
IS
SELECT COUNT (*)
FROM all_objects
WHERE object_name = name_in OR owner = name_in;
BEGIN
OPEN c1;
FETCH c1
INTO v_count;
CLOSE c1;
OPEN c2 (v_name);
FETCH c2
INTO v_count;
CLOSE c2;
END;
June's Tip of the Month
PL/SQL Upsert
Compliments of OracleDBA.co.uk.
If you're on version 9 of Oracle, you should be using the MERGE command to perform an
efficient upsert. Even before version 9, the most efficient mechanism for doing an upsert is to run
just pure SQL. However, time and time again, we often see PL/SQL being used to upsert records
with the following pseudo-code
- See if the record exists
- If yes, then issue an update statement
- If no, then issue an insert statement
Here's an example (with some name changing to protect the guilty party):
CURSOR cur_recipient IS
SELECT ...
FROM my_table
WHERE ...
FOR UPDATE OF mast_id;
OPEN cur_recipient;
FETCH cur_recipient
INTO v_recip;
IF cur_recipient%FOUND THEN
UPDATE my_table
SET mast_id = :b1
WHERE CURRENT OF cur_recipient;
CLOSE cur_recipient;
ELSE
CLOSE cur_recipient;
INSERT INTO my_table
VALUES ....
END IF;
Even if, for some obtuse reason, this cannot be re-done in pure SQL, you still do NOT need three
steps to perform this process in PL/SQL. You can use cursor attributes more intelligently to
reduce this to two steps.
- Attempt the update statement
- If the update 'fails', then issue an insert statement
The example above would be re-written as follows:
UPDATE my_table
SET mast_id = :b1
WHERE ...
IF SQL%NOTFOUND then
INSERT INTO my_table
VALUES ....
END IF;
Easier to code, easier to read, and does less work... You can then improve it further by coding
based on whether an update or insert is more probable - if you will typically be updating, then
you code the example as above. If you are typically inserting, then you code the insert first,
catch the constraint exception and then issue an update.
July's Tip of the Month
Pseudo-IF and CASE Statements In SQL*Plus Scripts
Compliments of Stephen Rea.
You can trick SQL*Plus into bypassing sections of your script by generating a comment section indicator (/*) at the point to start the bypass, which acts like a pseudo-IF block, and embedding the comment end indicator (*/) in the SQL where the script should continue on from after bypassing that section. In the following, I am bypassing most of my script if the field type variable (fieldtyp) is set to undefined ('U'):
... initial part of script ...
set termout off feedback off heading off echo off
spool bypass.sql
select '/*' from dual where '&fieldtyp' = 'U';
spool off
@bypass.sql
... rest of script ...
-- */
... reset variables and other cleanup ...
set termout on feedback 6 heading on
Note: "heading off", "feedback off", and "--" before "*/" keeps errors from occurring from the bypass script (must be set before the spool commands and after bypass.sql), and "termout off" keeps other messages from being displayed (such as DOC statements; should be reset only after "-- */").
A similar trick also be used to conditionally execute a subscript based on the value tested in the Where portion of the Select, such as if I had wanted to split the above script up into a rest_of_script script file instead of a "rest of script" block:
spool runit.sql
select '@rest_of_script.sql' from dual where '&fieldtyp' <> 'U';
spool off
@runit.sql
... reset variables and other cleanup ...
Enlarging on the Pseudo-IF code above, you can also do a "case"-like block by embedding several Select SQL generators in the Spool block, with each one uniquely identified in the Where portion of the Select. The snippet below generates an Accept command based on the field type entered earlier (fieldtyp 'C' for character or 'N' for number; note the pairs of single quotes embedded in the string to generate, such as after 'prompt', for which the Select statement generates a single quote, instead of seeing a non-paired single quote as the string terminator):
spool case.sql
select 'set termout on verify on' from dual;
select 'accept fieldstr char prompt ''Enter string to find (like Myvalue): '''
from dual where '&fieldtyp' = 'C';
select 'accept fieldnum num prompt ''Enter number to find (like 15.00): '''
from dual where '&fieldtyp' = 'N';
select 'set termout off verify off' from dual;
spool off
@case.sql
August's Tip of the Month
Defining Subtypes for Variables with Identical Datatypes
Compliments of Steven Feuerstein, Pipeline SYSOP.
In general, you should avoid hard-coded declarations such as the following:
DECLARE
v_ename VARCHAR2(20); - column information
v_totsal NUMBER(12,2); - dollar amount
v_note VARCHAR2(32767); - maximum size string, for now
v_salary NUMBER(10,2); - column information
BEGIN
In most cases, when you declare a variable it is based on some existing type or has the same type as other variables that you will declare. In the above example, we declare a variable to hold an employee name and hard-code the maximum length. What if the DBA expands that maximum length? The code can break.
The basic rules to follow are: Whenever you declare a variable to hold information from the column in a table, use the %TYPE anchor. Whenever you declare a derived value or one that is not related to a table structure use SUBTYPE.
These rules are demonstrated in the following code and rewrite of the above example:
CREATE OR REPLACE PACKAGE config
IS
SUBTYPE dollar_amt_t IS NUMBER (12, 2);
SUBTYPE big_string_t IS VARCHAR2(32767);
END config;
/
DECLARE
v_ename emp.ename%TYPE;
v_totsal config.dollar_amt_t;
v_note config.big_string_t;
v_salary emp.salary%TYPE;;
BEGIN
September's Tip of the Month
Correcting Values When Writing to Excel Files
Compliments of Hector Ulloa Ligarius, Santiago, Chile.
Sometimes we want to write archives to Excel files (xls). The values that are written to these
files in strings may need to be in a specific format such as:
'00001'
But when generating the file, Excel takes that value
and changes it, representing it as '1', when in fact it had to be written as '00001'.
Here there is a solution for PL/SQL and Forms Builder6i
Objects in Forms
- One Block , called bloque1
- One text item , called tei_archivo
- One Button and the code inside button is below
Code:
declare
in_file Text_IO.File_Type;
linebuf VARCHAR2(1800);
filename VARCHAR2(30);
var_dir_dde pls_integer;
BEGIN
filename:='c:\'||:bloque1.tei_archivo||'.xls';
in_file := Text_IO.Fopen(filename, 'w');
linebuf := '=TEXTO(1,"00000")';
Text_IO.put_Line(in_file, linebuf);
Text_IO.New_Line;
Text_IO.Fclose(in_file);
var_dir_dde := DDE.App_Begin('c:\program files\microsoft office\office\excel.exe
'||filename,DDE.App_Mode_Maximized);
DDE.App_Focus(var_dir_dde);
END;
October's Tip of the Month
Proper Use of SELECT COUNT(*)
Compliments of Steven Feuerstein, Pipeline SYSOP.
A common error among developers is to answer the question "Do I have at least
one of ___ ?" by executing a SELECT COUNT(*) against the table/view. This is a
very inefficient way to answer that question. SELECT COUNT answers a different
question: "How many do I have?" If you really only need to find out if you have
N rows, use an explicit cursor and FETCH once. If you need to find out if you
have more than one row, use an explicit cursor and FETCH N times.
Use COUNT only when the actual number of occurrences is needed.
Do not use the COUNT function to answer either of the following questions:
- Is there at least one row matching certain criteria?
- Is there more than one row matching certain criteria?
Instead, use
an explicit cursor inside a function.
You should only use COUNT when you need to answer the question: "How many
rows match a certain criteria?"
Suppose that you have been asked to write a program that returns TRUE if
there is at least one book in a given category. You could write it like this: CREATE OR REPLACE FUNCTION atleastone (
category_in IN book.category%TYPE)
RETURN BOOLEAN
IS
numbooks INTEGER;
BEGIN
SELECT COUNT(*) INTO numbooks
FROM book
WHERE category = category_in;
RETURN (numbooks > 0);
END;
But you are asking the RDBMS to do lots of unnecessary work. It might find,
for instance, that there are 12 million books in the NON-FICTION category. A
better approach is: CREATE OR REPLACE FUNCTION atleastone (
category_in IN book.category%TYPE)
RETURN BOOLEAN
IS
retval BOOLEAN;
CURSOR category_cur IS
SELECT 1
FROM book
WHERE category = category_in;
category_rec NUMBER;
BEGIN
OPEN category_cur;
FETCH category_cur INTO category_rec;
retval := category_cur%FOUND;
CLOSE category_cur;
RETURN retval;
END;
In other words: all you have to do is see if there is a single row and you
are done. With this practice, you get optimal performance out of your query and
the readability of your code also improves, since it is a more accurate
translation of the requirement.
November's Tip of the Month
Oracle 10g Bulk Bind Improvements
Compliments of Robert Freeman, Pipeline SYSOP.
Bulk bind operations help to improve the performance of PL/SQL operations. Oracle 10g
introduces two new types of functionality for bulk binds, including:
- Processing of sparse collections
- New exception handling
Processing Sparse Collections
In earlier versions of Oracle, it was not efficient to process collections that contained
sparse element collections. Without the save exceptions clause specified, the statement being
executed would terminate when it reached the first deleted element. With save exceptions
specified, the operation would not fail, but if there were a number of deleted elements, the
performance of the process was much worse than it would have been if save exceptions had not
been used.
Oracle 10g introduces the indices keyword, which allows for much faster processing of sparse
collections. Performance of statements with the new indices keyword is much better than was
possible with the save exceptions clause. Below is an example of this new functionality in use.
In this example, there is a table called FAMILIES as the source data. There is a PL/SQL
procedure that will remove all dependents from the FAMILIES table, and then populate a table
called employee. Since it is likely that there will be a number of dependents for each employee,
it is probable that the bulk bind will be very sparse by the time the employees are ready to be
inserted, so the example uses the new indices of clause to speed up the process. Here is the
example:
CREATE TABLE FAMILIES
( id_ssn NUMBER,
Last_name VARCHAR2(30),
First_name VARCHAR2(30),
Emp_dep_code NUMBER);
CREATE TABLE employees AS
SELECT * FROM FAMILIES WHERE 1=2;
CREATE UNIQUE INDEX u_employees_idx
on EMPLOYEES(id_ssn);
-- Now, insert some records
INSERT INTO families VALUES (123456780, 'Freeman','Robert',0);
INSERT INTO families VALUES (123456781, 'Freeman','Lisa',1);
INSERT INTO families VALUES (123456782, 'Freeman','Felicia',2);
INSERT INTO families VALUES (123456783, 'Freeman','Sarah',3);
INSERT INTO families VALUES (123456784, 'Freeman','Jacob',4);
INSERT INTO families VALUES (123456785, 'Freeman','Jared',5);
INSERT INTO families VALUES (123456786, 'Freeman','Lizzie',6);
INSERT INTO families VALUES (123456787, 'Bundy','Al',0);
INSERT INTO families VALUES (123456788, 'Bundy','Peg',1);
INSERT INTO families VALUES (123456789, 'Jetson','George',0);
INSERT INTO families VALUES (123456790, 'Jetson','Jane',1);
COMMIT;
-- Now, here is the PL/SQL used to load the employee table.
DECLARE
TYPE typ_famtyp IS TABLE OF families%ROWTYPE;
v_fam typ_famtyp;
BEGIN
SELECT *
BULK COLLECT INTO v_fam
FROM families;
-- loop through the collection and remove
-- unneeded entries.
FOR rec IN 1..v_fam.LAST()
LOOP
IF v_fam(rec).emp_dep_code!=0
THEN
v_fam.delete(rec);
END IF;
END LOOP;
FORALL inds IN INDICES OF v_fam
INSERT INTO employees VALUES v_fam(inds);
END;
/
Exception Handling
In Oracle 10g, the PL/SQL engine makes it possible to handle exceptions raised during the
execution of a forall statement. This allows code to be written in such a way that an error
does not need to halt its execution. It is also possible to save information about the error
after it occurs, write it to a table, or display it. The Oracle documentation provides some
good examples of this feature.
December's Tip of the Month
Creating Surrogate Numeric Keys for Tables
Compliments of Tom Reid, The Big O.
A common feature of database systems is that certain tables are required to have a unique
numerical key that can be used to identify records. A lot of effort is sometimes expended using
all sorts of convoluted coding schemes to ensure that this happens. What a lot of people don't
know is that Oracle can do all this for you automatically, seamlessly and with no locking
contention problems using sequences and triggers. Here's how:
Create the table you want to generate keys for:
create table mytable(col1 number, col2 etc ..., s_key number not null);
Create a sequence used to populate the s_key column. This is your surrogate key.
create sequence myseq start with 1 increment by 1
Once created, a sequence has two pseudocolumns which can be used - CURRVAL and NEXTVAL - and
are self-explanatory. To get to them use them in a select as follows:
SELECT myseq.nextval from dual;
Set up a database trigger on your table that ensures a sequence number is entered into
it for each record inserted.
create or replace trigger mytrig
before insert on mytable
for each row
begin
select myseq.nextval into :new.s_key from dual;
end;
/
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
