| 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 - 2001
| January | ROLLUP and CUBE Features of SELECT |
| February | Build a Function to Provide a DBMS_OUTPUT Trace |
| March | Differing Results when running PL/SQL Code in an Anonymous Block vs Procedure |
| April | Improving Performance with FORALL |
| May | Use of the New CASE Function |
| June | Use of the New NVL2 Function |
| July | Loading a Lookup Table in Memory to Speed Up Queries |
| August | A Clever ORDER BY |
| September | Designing a Two-Level Tab Canvas Form |
| October | Bulk Collecting Multiple Rows |
| November | Truncating a Table from a Remote Database |
| December | Database Triggers Containing :NEW and :OLD |
2000 Tips
1999 Tips
1998 Tips
1997 Tips
January's Tip of the Month
ROLLUP and CUBE Features of SELECT
Compliments of Fawwad-uz-Zafar Siddiqi (fuzs_98@yahoo.com).
New with Oracle 8i, the ROLLUP and CUBE features greatly simplify SQL coding by eliminating the need to design your own UNION statements. The examples below demonstrate this:
A listing of the rows in the EMP table of Oracle's demo account, SCOTT:
select deptno, empno, ename, job, sal
from emp order by deptno, empno;
DEPTNO EMPNO ENAME JOB SAL
------ ----- ----- ---- ----
10 7782 CLARK MANAGER 2450
10 7839 KING PRESIDENT 5000
10 7934 MILLER CLERK 1300
20 7369 SMITH CLERK 800
20 7566 JONES MANAGER 2975
20 7788 SCOTT ANALYST 3000
20 7876 ADAMS CLERK 1100
20 7902 FORD ANALYST 3000
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
30 7654 MARTIN SALESMAN 1250
30 7698 BLAKE MANAGER 2850
30 7844 TURNER SALESMAN 1500
30 7900 JAMES CLERK 950
14 rows selected.
If we wish to find the number of employees by
department, we issue the query:
select deptno, count(*)
from emp
group by deptno;
DEPTNO COUNT(*)
------ --------
10 3
20 5
30 6
If we also wish to include the total number of employees in the output, we
need to include the UNION clause, as shown here:
select deptno, count(*)
from emp
group by deptno
union
select to_number(null), count(*)
from emp;
DEPTNO COUNT(*)
------ --------
10 3
20 5
30 6
14
As of Oracle8i, we can use the ROLLUP option to achieve the same
result:
select deptno, count(*)
from emp
group by rollup(deptno);
DEPTNO COUNT(*)
------ --------
10 3
20 5
30 6
14
By simply invoking the ROLLUP facility in the GROUP BY clause, we
direct Oracle to summarize the data at levels above the columns specified,
all the way to the grand total.
Note that Oracle reports the grand
total by leaving the GROUP BY column blank. If the GROUP BY column also
contains null values, it might be difficult to distinguish those values
from the grand total row. Fortunately, we can use a special function called
GROUPING to report the current summarization level. The function returns
two values: 0 indicates the current row is grouped at the specified GROUP BY
level, and 1 indicates the row is grouped at a higher level.
select deptno, count(*), grouping(deptno)
from emp
group by rollup(deptno);
DEPTNO COUNT(*) GROUPING(DEPTNO)
------ -------- ----------------
10 3 0
20 5 0
30 6 0
14 1
Now we can take advantage of the GROUPING function to format the
output:
select decode(grouping(deptno),0,to_char(deptno),'Total') deptno, count(*)
from emp
group by rollup(deptno);
DEPTNO COUNT(*)
------ --------
10 3
20 5
30 6
Total 14
The ROLLUP feature can in fact be applied to multiple columns. The result is
multiple levels of rollup, as illustrated here:
select deptno, job, count(*), grouping(deptno), grouping(job)
from emp
group by rollup(deptno, job);
DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
------ ---- -------- ---------------- -------------
10 CLERK 1 0 0
10 MANAGER 1 0 0
10 PRESIDENT 1 0 0
10 3 0 1
20 ANALYST 2 0 0
20 CLERK 2 0 0
20 MANAGER 1 0 0
20 5 0 1
30 CLERK 1 0 0
30 MANAGER 1 0 0
30 SALESMAN 4 0 0
30 6 0 1
14 1 1
As shown in this example, we're able to count the employees by 1) department
and job; 2) department; and 3) grand total.
Similar to ROLLUP is the
CUBE function. CUBE groups data at multiple dimensions. In particular, it
summarizes data based on all possible combinations of the columns
specified in the GROUP BY clause. Let's see how this works:
select deptno, job, count(*), grouping(deptno), grouping(job)
from emp
group by cube(deptno, job);
DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
------ ---- -------- ---------------- -------------
10 CLERK 1 0 0
10 MANAGER 1 0 0
10 PRESIDENT 1 0 0
10 3 0 1
20 ANALYST 2 0 0
20 CLERK 2 0 0
20 MANAGER 1 0 0
20 5 0 1
30 CLERK 1 0 0
30 MANAGER 1 0 0
30 SALESMAN 4 0 0
30 6 0 1
ANALYST 2 1 0
CLERK 4 1 0
MANAGER 3 1 0
PRESIDENT 1 1 0
SALESMAN 4 1 0
14 1 1
The CUBE function includes the output generated by ROLLUP. In addition, it
includes the number of employees by job - independent of the department
number.
February's Tip of the Month
Build a Function to Provide a DBMS_OUTPUT Trace
Build a function that can be called from within a SQL statement that provides a DBMS_OUTPUT trace of
each row returned by a query, showing the table name and the ROWID.
The following implementation of this trace function is really just a “pass-through” to
DBMS_OUTPUT.PUT_LINE:
CREATE OR REPLACE FUNCTION traceit (tab IN VARCHAR2, rowid_in IN ROWID)
RETURN INTEGER
IS
PROCEDURE showinfo
IS
BEGIN
DBMS_OUTPUT.put_line (tab || ‘-‘ || ROWIDTOCHAR (rowid_in));
END;
BEGIN
showinfo;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.enable (1000000);
showinfo;
END;
/
I cannot call DBMS_OUTPUT directly in SQL, so I put it inside a function
that always returns 0, but in the process put information into the
DBMS_OUTPUT buffer. I also add an exception section to trap and
automatically correct for an error that might arise from the DBMS_OUTPUT
buffer's being too small.
Note that if you call this function in an SQL statement that is executed
outside of a PL/SQL block, you will not see any output UNTIL you execute a
PL/SQL block. That is because SQL*Plus (and other execution environments
for PL/SQL) does not automatically "dump" the DBMS_OUTPUT buffer when a SQL
statement completes, only when a PL/SQL block terminates.
March's Tip of the Month
Differing Results when running PL/SQL Code in an Anonymous Block vs Procedure
Overheard on the PL/SQL Pipeline...
Have you ever run code in an anonymous block and then used the identical code in a stored procedure
and gotten different results? The problem may be due to the fact that when running a stored procedure,
it runs as the owner, not the caller. It may also be that objects referenced in the code have had
privileges granted via a role rather than direct grants. Stored procedure and functions require all
database objects being "granted" explicitly to the stored object owner.
April's Tip of the Month
Improving Performance with FORALL
Compliments of Jeff Thomas (jsthomas@ixl.com)
I was recently experimenting with an Oracle 8.1.6 feature called "Bulk Binding",
and was amazed at the performance gain. Any developer who is not aware of
this capability is really missing out. I packaged my little test, and here
is the result. Of course, your results will vary, but the point should be clear enough.
The script and the output are shown for both the normal PL/SQL method and the FORALL method.
SQL Script
set echo on
set feedback on
spool forall
EXAMPLE 1: REGULAR PL/SQL WAY, NOT USING A BULK BIND
set timing on
DECLARE
type t1_type is table of test1.testnum%type index by binary_integer;
t1_tab t1_type;
t1_idx binary_integer := 0;
BEGIN
FOR n in 1 .. 100000
LOOP
t1_tab(n) := n;
t1_idx := t1_idx + 1;
END LOOP;
FOR n in 1 .. t1_idx
LOOP
insert into test1 (testnum) values (t1_tab(n));
END LOOP;
END;
/
set timing off
select count(*) from test1;
truncate table test1 reuse storage;
set timing on
EXAMPLE 2: USING FORALL
DECLARE
type t1_type is table of test1.testnum%type index by binary_integer;
t1_tab t1_type;
t1_idx binary_integer := 0;
BEGIN
FOR n in 1 .. 100000
LOOP
t1_tab(n) := n;
t1_idx := t1_idx + 1;
END LOOP;
FORALL n in 1 .. t1_idx
insert into test1 (testnum) values (t1_tab(n));
END;
/
set timing off
select count(*) from test1;
truncate table test1 reuse storage;
spool off
Output
EXAMPLE 1: REGULAR PL/SQL WAY, NOT USING A BULK BIND
set timing on
DECLARE
2 type t1_type is table of test1.testnum%type index by binary_integer;
3 t1_tab t1_type;
4 t1_idx binary_integer := 0;
5
6 BEGIN
7
8
9 FOR n in 1 .. 100000
10 LOOP
11 t1_tab(n) := n;
12 t1_idx := t1_idx + 1;
13 END LOOP;
14
15
16 FOR n in 1 .. t1_idx
17 LOOP
18 insert into test1 (testnum) values (t1_tab(n));
19 END LOOP;
20
21 END;
22 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:25.97
set timing off
select count(*) from test1;
COUNT(*)
--------
100000
1 row selected.
truncate table test1 reuse storage;
Table truncated.
set timing on
EXAMPLE 2: USING FORALL
DECLARE
2 type t1_type is table of test1.testnum%type index by binary_integer;
3 t1_tab t1_type;
4 t1_idx binary_integer := 0;
5
6 BEGIN
7
8
9 FOR n in 1 .. 100000
10 LOOP
11 t1_tab(n) := n;
12 t1_idx := t1_idx + 1;
13 END LOOP;
14
15 FORALL n in 1 .. t1_idx
16 insert into test1 (testnum) values (t1_tab(n));
17
18 END;
19 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.73
set timing off
select count(*) from test1;
COUNT(*)
--------
100000
1 row selected.
truncate table test1 reuse storage;
Table truncated.
spool off
May's Tip of the Month
Use of the New CASE Function
Compliments of Andrew Simkovsky (asimkovsky@msn.com)
Oracle version 8.1.6 provided the Oracle community with a wealth of new features. One of which is the new searched CASE function. This feature works very similar to the DECODE function, but surpasses its ability tenfold. It is much more flexible and
often performs much better. The classic example offered by Oracle documentation gives this statement:
SELECT AVG(foo(e.sal)) FROM emps e;
In this statement FOO is a function that will return its input if the input is greater than 2000, but will return 2000 otherwise. This implementation requires the use of PL/SQL code, which takes much longer to write and will probably run much slower. With the CASE functionality built into the RDBMS, the logic can be built straight into the SQL statement quickly and easily as shown:
SELECT AVG (CASE
WHEN e.sal > 2000
THEN e.sal
ELSE 2000
END)
FROM emps e;
The CASE function can be nested, extending the conditional logic even further. But what if you wanted to use this type of statement from within PL/SQL? Being on the bleeding edge, this functionality has not yet been implemented within the PL/SQL engine. For example, if you tried this from within PL/SQL you would receive an error:
DECLARE
vNum number;
begin
select sum(case
when e.sal>2000
then e.sal
else 2000
end)
into vNum from emps e;
end;
The error you would receive is “PLS-00103: Encountered the symbol "CASE" when expecting…”. This is because the PL/SQL engine does not yet recognize the CASE function. The work-around to this would be to assign the entire statement to a variable, and execute the statement using the EXECUTE IMMEDIATE command:
DECLARE
sqlStr varchar2(500);
vNum number;
begin
sqlStr:= 'select avg(case '||
'when e.sal>2000 '||
'then e.sal '||
'else 2000 '||
'end) '||
'from emps e';
EXECUTE IMMEDIATE sqlStr INTO vNum;
DBMS_OUTPUT.PUT_LINE('Average salary: '||vNum)
end;
Assigning the statement to a variable, then executing it with the EXECUTE IMMEDIATE command will avoid the error from the PL/SQL engine.
June's Tip of the Month
Use of the New NVL2 Function
Compliments of Adinath Kamode (adi_kamode@usa.net)
Most of us are familiar with the NVL function which checks for the existence of NULL values. Oracle
provides a new function called NVL2 which checks for the existence of NOT NULL. The syntax for this
function is as follows.
NVL2(expr1,expr2,expr3);
If expr1 is not null then the function will return expr2. Otherwise, the function will return expr3.
The expr1 can have any datatype and arguments expr2 and expr3 can be of any datatype other than LONG.
The datatype of the return value is that of expr2.
For example:
SELECT EMPNO,ENAME,SAL,COMM,NVL2(COMM,1,0) COMMPRE
FROM EMP
ORDER BY EMPNO;
EMPNO ENAME SAL COMM COMMPRE
-------- ---------- ---------- -------- -----------
7499 ALLEN 5000 300 1
7521 WARD 3906.5 500 1
7566 JONES 9297 0
7654 MARTIN 3906.5 1400 1
7698 BLAKE 8906.5 0
7782 CLARK 7656.5 0
7788 SCOTT 9375 0
7839 KING 15625 0
7844 TURNER 4687.5 0
7876 ADAMS 3437.5 0
7900 JAMES 2969 0
7902 FORD 9375 0
7934 MILLER 4062.5 0
The above query checks for the presence of NOT NULL in the COMM field, and returns 1 where COMM
is NOT NULL and 0 where it is NULL.
Here are the differences between the NVL and NVL2 functions:
- NVL takes 2 arguments while NVL2 takes 3.
- NVL returns the first argument if the first argument is not null, whereas NVL2 returns the second
argument if the first argument is not null and returns the third argument if the first argument is null.
- In NVL, the datatype of the return is that of the first argument, in NVL2 the datatype of the
return is that of the second argument.
Dan Clamage, Pipeline SYSOP, had the following to add:
DECODE can also detect NULL/NOT NULL, but it must
return a value of the same data type as the expression (at least in
earlier database versions - in 8i it seems to be more flexible). Of
course, DECODE can only be used in SQL.
select
DECODE('ha',
null, 'ho',
'he')
from dual;
returns 'he'.
select
DECODE(to_char(null),
null,'ho',
'he')
from dual;
returns 'ho'.
declare
b varchar2(10);
c date;
begin
select
DECODE(c,
null,'ha',
'ho') into b
from dual;
dbms_output.put_line(b);
end;
/
returns 'ha'
July's Tip of the Month
Loading a Lookup Table in Memory to Speed Up Queries
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP (danielj@clamage.com)
Recently, I worked with a developer who faced the challenge of speeding up his
Pro*C application. It was taking about 2 hours to run. After examining
his application, I discovered that he had one table on which every row
returned in the driving loop required a random lookup.
Furthermore, he could expect nearly every row in this lookup table
to be accessed at some point over the life of the program. I instructed
him to preload this moderately sized table (~50k rows) into a persistent
PL/SQL table. In order to be persistent, the PL/SQL table had to
be declared in the package body, outside of any routine:
CREATE OR REPLACE
PACKAGE BODY lookup
IS
TYPE typ_val_data IS TABLE OF tbl_lookup.val_data%TYPE
INDEX BY BINARY_INTEGER;
tab_val_data typ_val_data;
I explained to him that the numeric primary key could be used as
the index offset, and the column value he needed could then be stored
at that offset. For example:
Lookup Table
OID NUMBER(10) VAL_DATA VARCHAR2(4)
3 'GHGL'
27 'ACBF'
101 'LEFP'
157 'KPOI'
PL/SQL Table (also known as index-by table or array)
Array Offset Array Value
3 'GHGL'
**no data in array elements 4-26**
27 'ACBF'
**no data in array elements 28-100**
101 'LEFP'
**no data in array elements 158+**
157 'KPOI'
This would result in a sparsely populated array. He populated the
array up front, as part of his program initialization, using a packaged
procedure. This procedure simply read every row in his lookup table
in a cursor FOR loop, and used the primary key as the element offset
into the PL/SQL table. For example:
PROCEDURE populate_lookup
IS
CURSOR get_all_data IS
SELECT oid, val_data
FROM tbl_lookup;
BEGIN
FOR rec IN get_all_data LOOP
tab_val_data(rec.oid) := rec.val_data;
END LOOP;
END populate_lookup;
Then he provided a packaged function to interface with this array.
He could use this function in PL/SQL or in SQL. For example:
FUNCTION get_val_data(p_oid IN tbl_lookup.oid%TYPE)
RETURN tbl_lookup.val_data%TYPE
IS
BEGIN
RETURN(tab_val_data(p_oid));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(NULL);
END get_val_data;
Inside his main loop, he coded something like this:
INSERT INTO tbl_target (val_data, ...)
VALUES (lookup.get_val_data(fk_oid), ...);
He had to fully qualify the function, since it's being called from
SQL. He reported that his program now runs in 20 minutes, a 5x speed
improvement.
August's Tip of the Month
A Clever ORDER BY
Compliments of Jeffrey Pearse, Middlesex University (j.pearse@mdx.ac.uk)
This script returns the most recently created row in a table followed by an alphabetical listing of the
remaining rows in the table.
For this task I have created a table named TEST with three columns: id, alpha and created_when. The small
amount of data held in this table is as follows:
ID ALPHA CREATED_WHEN
1 A 10-JUL-99
2 B 02-SEP-99
3 C 18-AUG-99
4 D 08-SEP-99
5 S 10-JUL-99
6 N 13-AUG-99
In order to select the most recent row data followed by alpha records I used the following select:
--
1 COLUMN 1 NOPRINT
2 --
3 select id
4 ,alpha
5 ,created_when
6 ,1
7 from test
8 where created_when = (select max(created_when)
9 from test)
10 union all
11 select id
12 ,alpha
13 ,created_when
14 ,2
15 from test
16 where created_when <> (select max(created_when)
17 from test)
18 order by 4
19* ,alpha
Explanation:
Line 1: COLUMN 1 NOPRINT
This ensures that column 4 is not displayed as it is only used to order the results returned from each
part of the UNION ALL. (UNION ALL is preferable to UNION because UNION
returns distinct rows selected by both queries while UNION ALL returns all
the rows. Therefore, if the table has duplicates, UNION will remove them, which is
not what we want. If the table has no duplicates, UNION will force a sort and
cause performance degradation as compared to UNION ALL.)
Line 6: select 1
This ensures that this part of the UNION ALL is returned first as the final
order by is by column 4.
Line 14: select 2
This ensures that this part of the UNION ALL is returned second as the final
order by is by column 4 followed by the alpha column.
The key to this is that the order by ensures that the max date, which is returned from the upper select
in the UNION ALL, selects the numeric value 1 and is returned first followed by the ordering on the column
alpha. The column 1 noprint ensures that the fourth column is not selected, as these values are only useful
for the order by clause.
The script returns the following output with the most recent row followed by the others ordered by the
column alpha.
ID ALPHA CREATED_WHEN
---------- ----- ------------
4 D 08-SEP-99
1 A 10-JUL-99
2 B 02-SEP-99
3 C 18-AUG-99
6 N 13-AUG-99
5 S 10-JUL-99
September's Tip of the Month
Designing a Two-Level Tab Canvas Form
Compliments of Alexey Gorbenko, (avg@webmercs.com)
In my work, I use Oracle Developer/2000 v6.0. and very often I have to use Forms with multi-level
tab-canvases. Unfortunately, however, Forms doesn’t allow the inclusion of a tab-canvas in the tab-page
of another tab-canvas. Here is a way to work around this.
Suppose that we need to design a two-level tab-canvas form:
- Create the content canvas (e.g. MAIN)
- Create the tab-canvas of the first level (e.g. T_GEN, Note: For this example, let the names of tab-canvases and the names of
tab-pages which will be included in sub-tab-canvases start with the letter T and names of tab-pages which
will be included in stacked canvases with information start with the letter C)
- Minimize the height of the canvas so that you only see the tabs.
- Create as many tab-pages as you need. Rename them (e.g. T_DEP_EMP, T_CUST_ORD_IT…).
- Create the second level tab-canvases. Their names must be equal to the names of their corresponding tab-pages of the first-level tab canvas (in our case: T_DEP_EMP, T_CUST_ORD_IT).
- Also minimize the height of canvases to only see the tabs. Set their Y positions so that you can see them under the first-level tab canvas.
- Create tab-pages in the second level tab canvases. Rename them (e.g. C_DEP, C_EMP, C_CUST, C_ORDER, C_ITEMS).
- Create the stacked canvases (information canvases). Their names must be equal to the names of their corresponding tab-pages of the second-level tab canvases (in our case: C_DEP, C_EMP, C_CUST, C_ORDER, C_ITEMS). Set the Y positions so that you can see them under the second-level tab canvas.
- Create the data blocks (e.g. EMP, DEP, CUSTOMER, ORD, ITEMS) and place the information into the corresponding stacked-canvases.
- Add two form-level triggers:
WHEN-NEW-FORM-INSTANCE
go_block('emp');
execute_query;
go_block('customer');
execute_query;
go_block('ord');
execute_query;
go_block('item');
execute_query;
go_block('dept');
execute_query;
show_view('t_dep_emp');
show_view('c_dep');
:global.cnv:='C_DEP';
WHEN-TAB-PAGE-CHANGED
hide_view(:global.cnv);
:global.cnv:=:SYSTEM.TAB_NEW_PAGE;
if substr(:global.cnv,1,1)='T' then
show_view(:global.cnv);
:global.cnv:=GET_CANVAS_PROPERTY(:global.cnv,TOPMOST_TAB_PAGE);
end if;
show_view(:global.cnv);
- The form is ready!
This method may also be used for creating tab canvas forms with many levels.
October's Tip of the Month
Bulk Collecting Multiple Rows
Compliments of Dan Clamage, PL/SQL Pipeline SYSOP (danielj@clamage.com)
I needed to perform a query that bulk collected multiple rows for all the
keys stored in another collection. But you can't use the SELECT..BULK
COLLECT statement in a FORALL statement, and using an array in a SELECT's
WHERE clause is unsupported. So I faked it out by updating a non-indexed
column to itself. The only alternative would have been to load the
collection one key at a time in a loop.
In this example, I needed to collect the old indicator values for a set of
primary keys before updating them to new values. Inputs are two strings
representing comma-delimited lists of values. The parse_list routine parses
out these values and loads them into a collection. Cold Fusion does not
support Oracle's Collection datatype, but it does have a routine for quickly
building delimited list strings. I have removed all of the error handling and input
validation for readability.
PROCEDURE update_xref_brand(
p_new_ind_list IN VARCHAR2
,p_xref_brand_oid_list IN VARCHAR2)
IS
c_delim CONSTANT CHAR(1) := ',';
a_xref_brand_oid utl.ARR_STR;
a_new_ind utl.ARR_STR;
a_old_ind utl.ARR_STR;
BEGIN
utl.parse_list(p_new_ind_list, c_delim, a_new_ind);
utl.parse_list(p_xref_brand_oid_list, c_delim, a_xref_brand_oid);
FORALL i IN a_xref_brand_oid.FIRST..a_xref_brand_oid.LAST
UPDATE xref_brand
SET promo_verification_ind = promo_verification_ind
WHERE xref_brand_oid = a_xref_brand_oid(i)
RETURNING promo_verification_ind
BULK COLLECT INTO a_old_ind;
FORALL i IN a_xref_brand_oid.FIRST..a_xref_brand_oid.LAST
UPDATE xref_brand
SET promo_verification_ind = a_new_ind(i)
WHERE xref_brand_oid = a_xref_brand_oid(i);
FORALL i IN a_xref_brand_oid.FIRST..a_xref_brand_oid.LAST
INSERT INTO hist_xref_brand_update (
hist_xref_brand_update_oid
,xref_brand_oid
,original_value
,new_value
) VALUES (
hist_xref_brand_update_oid.NEXTVAL
,a_xref_brand_oid(i)
,a_old_ind(i)
,a_new_ind(i)
);
END update_xref_brand;;
/
November's Tip of the Month
Truncating a Table from a Remote Database
Compliments of Giovanni Jaramillo, (gjaram@hotmail.com)
Recently I
was trying to process data from one table and insert it into another. Prior to the insert, I wanted to
truncate the receiving table. In the past I had been able to truncate a table under the same schema,
or under a different schema but on the same database. This time I had to truncate a table on a remote
database. So I tried the usual truncate and received the following error:
ORA-02021: DDL operations are not allowed on a remote database.
I had a database link to this remote database so I could see objects there and execute
them (e.g. procedures, functions, packages, triggers, etc). So the
solution was to create the following procedure on the remote database, then execute it from the local
one.
CREATE OR REPLACE PROCEDURE
Truncate_Remote_Table(p_table_name VARCHAR2) AS
v_sql_error_code PLS_INTEGER;
v_sql_error_message VARCHAR2(512);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table_name;
EXCEPTION
WHEN OTHERS THEN
v_sql_error_code := SQLCODE;
v_sql_error_message := SQLERRM(v_sql_error_code);
DBMS_OUTPUT.ENABLE(5000);
DBMS_OUTPUT.PUT_LINE('OTHER ERROR');
DBMS_OUTPUT.PUT_LINE(v_sql_error_message);
END Truncate_Remote_Table;
To execute the procedure, use the following from the local database:
BEGIN
Truncate_Remote_Table@db_link(‘remote_table_name’);
END;
December's Tip of the Month
Database Triggers Containing :NEW and :OLD
Compliments of Zlatko Sirotic, (zlatko.sirotic@iii.hr)
We often want to create and execute 'generic' dynamic statements in database triggers
containing :NEW, referring to new values of current records,
or :OLD, referring to old values of current records. In execution time, however, :OLD and :NEW are
flagged as bad bind variables.
One workaround is to use AFTER STATEMENT triggers in the following manner:
- Create a database package with a PL/SQL table for storing ROWID values
- Clear the PL/SQL table in the BEFORE STATEMENT trigger
- Populate the PL/SQL table with ROWID values in the BEFORE (or AFTER) ROW trigger
- Read the rows in the AFTER STATEMENT trigger with dynamic PL/SQL, using ROWIDs from the PL/SQL table
and using dynamically selected column names with this statement:
SELECT column_name
FROM user_tab_columns
WHERE UPPER (table_name) = UPPER (p_table);
In this way, you can read :NEW values of columns and, with AUTONOMOUS_TRANSACTION,
:OLD values as well.
Example (scott.dept table):
- DATABASE TRIGGERS (UPDATE)
CREATE OR REPLACE TRIGGER bus_dept
BEFORE UPDATE ON dept
BEGIN
plsql_table.clear;
END;
/
CREATE OR REPLACE TRIGGER bur_dept
BEFORE UPDATE ON dept
FOR EACH ROW
BEGIN
plsql_table.populate_with_rowid (:OLD.ROWID);
END;
/
CREATE OR REPLACE TRIGGER aus_dept
AFTER UPDATE ON dept
DECLARE
v_current_rowid ROWID;
BEGIN
dynamic_new_old.set_table_name ('dept');
dynamic_new_old.create_column_names;
WHILE plsql_table.rowid_exists LOOP
v_current_rowid := plsql_table.current_rowid;
DBMS_OUTPUT.PUT_LINE ('OLD VALUES:');
dynamic_new_old.display_old_values (v_current_rowid);
DBMS_OUTPUT.PUT_LINE ('NEW VALUES:');
dynamic_new_old.display_new_values (v_current_rowid);
DBMS_OUTPUT.PUT_LINE ('*****');
END LOOP;
END;
/
- PACKAGES
CREATE OR REPLACE PACKAGE plsql_table IS
PROCEDURE clear;
PROCEDURE populate_with_rowid (p_rowid ROWID);
FUNCTION rowid_exists RETURN BOOLEAN;
FUNCTION current_rowid RETURN ROWID;
END;
/
CREATE OR REPLACE PACKAGE BODY plsql_table IS
TYPE type_plsql_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
m_plsql_table type_plsql_table;
m_rec_number BINARY_INTEGER;
PROCEDURE clear IS
BEGIN
m_rec_number := 0;
END;
PROCEDURE populate_with_rowid (p_rowid ROWID) IS
BEGIN
m_rec_number := m_rec_number + 1;
m_plsql_table (m_rec_number) := p_rowid;
END;
FUNCTION rowid_exists RETURN BOOLEAN IS
BEGIN
RETURN (m_rec_number > 0);
END;
FUNCTION current_rowid RETURN ROWID IS
v_rowid VARCHAR2 (18);
BEGIN
v_rowid := m_plsql_table (m_rec_number);
m_rec_number := m_rec_number - 1;
RETURN v_rowid;
END;
END;
/
CREATE OR REPLACE PACKAGE dynamic_new_old IS
PROCEDURE set_table_name (p_table VARCHAR2);
PROCEDURE create_column_names;
PROCEDURE display_old_values (p_rowid ROWID);
PROCEDURE display_new_values (p_rowid ROWID);
END;
/
CREATE OR REPLACE PACKAGE BODY dynamic_new_old IS
m_table VARCHAR2 (30);
m_columns VARCHAR2 (32000);
PROCEDURE set_table_name (p_table VARCHAR2) IS
BEGIN
m_table := UPPER (p_table);
END;
PROCEDURE create_column_names IS
v_first_column BOOLEAN;
BEGIN
v_first_column := TRUE;
FOR rec IN
(SELECT column_name
FROM user_tab_columns
WHERE table_name = m_table)
LOOP
IF v_first_column THEN
v_first_column := FALSE;
m_columns := 'v_record.' || rec.column_name;
ELSE
m_columns := m_columns ||
'||' || '''--''' || '|| v_record.' || rec.column_name;
END IF;
END LOOP;
END;
PROCEDURE display_values (p_rowid ROWID) IS
v_statement VARCHAR2 (32000);
BEGIN
v_statement :=
' DECLARE ' ||
' v_record ' || m_table || '%ROWTYPE;' ||
' BEGIN' ||
' SELECT * INTO v_record' ||
' FROM ' || m_table ||
' WHERE ROWID = :rowid_p;' ||
' DBMS_OUTPUT.PUT_LINE (' || m_columns || ');' ||
' END;';
EXECUTE IMMEDIATE v_statement USING p_rowid;
END;
PROCEDURE display_old_values (p_rowid ROWID) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
display_values (p_rowid);
END;
PROCEDURE display_new_values (p_rowid ROWID) IS
BEGIN
display_values (p_rowid);
END;
END;
/
If you need :OLD values only, you can write a simpler solution
(without statement triggers and 'plsql_table' package), using 'dynamic_new_old' package
and AUTONOMOUS_TRANSACTION in the BEFORE (or AFTER) ROW trigger.
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
