I wrote this simple SQL*Plus script to loop through all the user’s tables and report how many rows were in them at this point in time. Normally I might estimate this information based on the table statistics, but in this case I had to know if any rows had been inserted since the last stats were gathered. Also, I preferred not to just run the stats gathering job. Obviously, it doesn’t pick up any uncommitted data.
It doesn’t list the tables which have zero (0) rows, but it report how many tables it counted, and the total number of rows across the entire schema.
If run on an 11g database, it will skip the count on tables which have had no segment created yet.
WARNING: this script is suitable for development environments only. Use at your own risk.
SET SERVEROUT ON FEEDBACK OFF
DECLARE
c INTEGER;
tc INTEGER := 0;
tr INTEGER := 0;
BEGIN
$IF DBMS_DB_VERSION.VER_LE_10 $THEN
DBMS_OUTPUT.enable(1000000);
$ELSE
DBMS_OUTPUT.put_line('Only tables with segment created are counted.');
$END
FOR r IN (
SELECT table_name
FROM user_tables
$IF DBMS_DB_VERSION.VER_LE_10 $THEN
$ELSE
WHERE segment_created = 'YES'
$END
ORDER BY table_name
)
LOOP
tc := tc + 1;
BEGIN
EXECUTE IMMEDIATE REPLACE(
'SELECT COUNT(*) FROM "#TABLE#"'
,'#TABLE#',r.table_name)
INTO c;
tr := tr + c;
IF c > 0 THEN
DBMS_OUTPUT.put_line(
RPAD(r.table_name,31,' ')
|| TO_CHAR(c,'9G999G999G990'));
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(r.table_name);
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
END LOOP;
DBMS_OUTPUT.put_line('counted '
|| tc || ' table(s)');
DBMS_OUTPUT.put_line('total rows '
|| TO_CHAR(tr,'9G999G999G990'));
END;
/
SET SERVEROUT OFF FEEDBACK ON
Example output:
SQL> @count_all_tables
Only tables with segment created are counted.
counted 1 table(s)
total rows 0
This tells me that there are no rows yet, and of all the tables, only one has had a segment created for it (e.g. perhaps an INSERT was attempted but rolled back, or the row was deleted). I’m sure you’ll be able to adapt the script to suit your obsessive record-counting needs.
An interesting discussion on the PL/SQL Challenge blog here has led to me changing my mind about “the best way” to loop through a sparse PL/SQL associative array.
Normally, if we know that an array has been filled, with no gaps in indices, we would use a simple FOR LOOP:
DECLARE
TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
a t;
BEGIN
SELECT x BULK COLLECT INTO a FROM mytable;
FOR i IN a.FIRST..a.LAST LOOP
-- process a(i)
END LOOP;
END;
If, however, the array may be sparsely filled (i.e. there might be one or more gaps in the sequence), this was “the correct way” to loop through it:
Method A (First/Next)
DECLARE
TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
a t;
i BINARY_INTEGER;
BEGIN
...
i := a.FIRST;
LOOP
EXIT WHEN i IS NULL;
-- process a(i)
i := a.NEXT(i);
END LOOP;
END;
Method A takes advantage of the fact that an associative array in Oracle is implemented internally as a linked list – the fastest way to “skip over” any gaps is to call the NEXT operator on the list for a given index.
Alternatively, one could still just loop through all the indices from the first to the last index; but the problem with this approach is that if an index is not found in the array, it will raise the NO_DATA_FOUND exception. Well, Method B simply catches the exception:
Method B (Handle NDF)
DECLARE
TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
a t;
BEGIN
...
FOR i IN a.FIRST..a.LAST LOOP
BEGIN
-- process a(i)
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END;
This code effectively works the same (with one important proviso*) as Method A. The difference, however, is in terms of relative performance. This method is much faster than Method A, if the array is relatively dense. If the array is relatively sparse, Method A is faster.
* It must be remembered that the NO_DATA_FOUND exception may be raised by a number of different statements in a program: if you use code like this, you must make sure that the exception was only raised by the attempt to access a(i), and not by some other code!
A third option is to loop through as in Method B, but call the EXISTS method on the array to check if the index is found, instead of relying on the NO_DATA_FOUND exception.
Method C (EXISTS)
DECLARE
TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
a t;
BEGIN
...
FOR i IN a.FIRST..a.LAST LOOP
IF a.EXISTS(i) THEN
-- process a(i)
END IF;
END LOOP;
END;
The problem with this approach is that it effectively checks the existence of i in the array twice: once for the EXISTS check, and if found, again when actually referencing a(i). For a large array which is densely populated, depending on what processing is being done inside the loop, this could have a measurable impact on performance.
Bottom line: there is no “one right way” to loop through a sparse associative array. But there are some rules-of-thumb about performance we can take away:
- When the array is likely often very sparsely populated with a large index range, use Method A (First/Next).
- When the array is likely often very densely populated with a large number of elements, use Method B (Handle NDF). But watch how you catch the NO_DATA_FOUND exception!
- If you’re not sure, I’d tend towards Method A (First/Next) until performance problems are actually evident.
You probably noticed that I haven’t backed up any of these claims about performance with actual tests or results. You will find some in the comments to the afore-mentioned PL/SQL Challenge blog post; but I encourage you to log into a sandpit Oracle environment and test it yourself.
This is a comment on jagatheesh.ramakrishnan@oracle.com’s article “Generate DML/DDL/QUERY from SQL” – comments are not enabled on their blog so here’s my addendum.
The following query is offered as a means of generating a script to ONLINE all datafiles in a database:
select 'ALTER DATABASE DATAFILE '''|| name || ''' online ;'
from v$datafile;
I suggest an alternative method, which is both easier to write and easier to maintain:
select REPLACE(q'[
ALTER DATABASE DATAFILE '#NAME#' online ;
]','#NAME#',name) from v$datafile;
This way, the syntax within the DDL is unmuddied by the syntax required by the query to generate it. It’s immediately obvious that only single quotes will surround the name of the datafile in the generated DDL.
If you’re on a pre-10g database, you can still use this method, but you’ll need to revert to the old quote-escape:
select REPLACE('
ALTER DATABASE DATAFILE ''#NAME#'' online ;
','#NAME#',name) from v$datafile;
UTL_FILE, unfortunately, does not allow you to query the contents of a directory.
If you’re on Oracle 11g you can use the method described here: listing files with the external table preprocessor in 11g.
If you’re on Oracle 10g another option is to create a java routine. Note: this is not my code – a colleague sent the basics to me and I just modified it somewhat to suit my purpose at the time.
CREATE OR REPLACE AND RESOLVE
JAVA SOURCE NAMED "DirectoryLister" AS
import java.io.File;
import java.util.Arrays;
public class DirectoryLister
{
public static String getFileList(String idir, String sep)
{
File aDirectory = new File(idir);
File[] filesInDir = aDirectory.listFiles();
String result = "";
for ( int i=0; i<filesInDir.length; i++ )
{
if ( filesInDir[i].isFile()
&& !filesInDir[i].isHidden() )
{
result = result + sep + filesInDir[i].getName();
}
}
return result;
}
};
/
CREATE OR REPLACE
FUNCTION dirlist_csv
(p_dir IN VARCHAR2, p_sep IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA NAME
'DirectoryLister.getFileList
(java.lang.String, java.lang.String)
return String';
/
BEGIN
DBMS_JAVA.grant_permission('USER',
'java.io.FilePermission', '<>', 'read');
DBMS_JAVA.grant_permission('USER',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor', '');
DBMS_JAVA.grant_permission('USER',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor', '');
END;
/
(replace “USER” in the above commands with the user name)
CREATE OR REPLACE
FUNCTION get_path (dir IN VARCHAR2) RETURN VARCHAR2 IS
o_path ALL_DIRECTORIES.directory_path%TYPE;
BEGIN
SELECT directory_path INTO o_path
FROM ALL_DIRECTORIES d
WHERE d.directory_name = dir;
RETURN o_path;
END get_path;
Sample script:
DECLARE
csv VARCHAR2(32767);
filename VARCHAR2(1000);
BEGIN
csv := dirlist_csv(get_path('MYDIR'), ',');
LOOP
EXIT WHEN csv IS NULL;
filename := SUBSTR(csv, INSTR(csv, ',', -1) + 1);
dbms_output.put_line(filename);
csv := SUBSTR(csv, 1, INSTR(csv, ',', -1) - 1);
END LOOP;
END;
/
Note: If the number of files is expected to be large, it would be better to get the java program to insert the file names into a global temporary table, then query that, instead of returning the list as a CSV string.
Want to purge all the recyclebins for all users in your database?
Can’t (or don’t want to) log in as each user individually to run this, one by one:
PURGE RECYCLEBIN
You could log in as SYSDBA and run:
PURGE DBA_RECYCLEBIN
But, what if you don’t have SYSDBA access? (Yes, this has happened to me, with a client-supplied VM – they told me the SYSTEM password but not the SYS password)
You can use the PURGE TABLESPACE x USER y option – run this to generate the commands to run, then run the output as a script:
SELECT DISTINCT
REPLACE(REPLACE('PURGE TABLESPACE "#TS#" USER "#OWNER#";'
,'#TS#',ts_name)
,'#OWNER#',owner)
FROM dba_recyclebin
WHERE ts_name IS NOT NULL;
PURGE TABLESPACE "USER_DATA" USER "SCOTT";
PURGE TABLESPACE "USER_DATA" USER "LARRY";
WARNING: this command will result in irreversible loss of data for all users on your database.
Tested on Oracle 10gR2.
This is just a post to document a standard utility functions that I like to create in a Oracle Forms library, if equivalents are not already available. Suggestions or critiques are welcome.
“No warranty is express or implied about the suitability of this code for any purpose.”
This package could do with some improvements:
- use a database table type (instead of one defined in the forms package) so that we can just send it straight to the database package
- perhaps a “level” for each log message (Debug/Info/Warning/Error/Fatal) plus the ability to switch the level up or down for each session
Database Objects
CREATE TABLE DEBUG_LOG
(
log_id NUMBER(12,0) NOT NULL
,ts TIMESTAMP NOT NULL
,text VARCHAR2(4000)
,sid NUMBER
DEFAULT SYS_CONTEXT('USERENV','SID')
,sessionid NUMBER
DEFAULT SYS_CONTEXT('USERENV','SESSIONID')
,module VARCHAR2(48)
DEFAULT SYS_CONTEXT('USERENV','MODULE')
,action VARCHAR2(32)
DEFAULT SYS_CONTEXT('USERENV','ACTION')
,client_info VARCHAR2(64)
DEFAULT SYS_CONTEXT('USERENV','CLIENT_INFO')
,username VARCHAR2(30)
DEFAULT SYS_CONTEXT('USERENV','SESSION_USER')
)
CREATE SEQUENCE DEBUG_LOG_SEQ;
CREATE OR REPLACE PACKAGE DB_FORMS_DEBUG IS
TYPE logtable_type IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
TYPE date_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
PROCEDURE msg
(text IN VARCHAR2
,ts IN TIMESTAMP := NULL);
PROCEDURE insert_form_debug_log
(logtable IN logtable_type
,date_table IN date_table_type
,username IN VARCHAR2
,module IN VARCHAR2
);
END DB_FORMS_DEBUG;
CREATE OR REPLACE PACKAGE BODY DB_FORMS_DEBUG IS
PROCEDURE msg
(text IN VARCHAR2
,ts IN TIMESTAMP := NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 0 .. LENGTH(text) / 4000 LOOP
INSERT INTO DEBUG_LOG
(log_id
,ts
,text)
VALUES
(DEBUG_LOG_SEQ.NEXTVAL
,NVL(ts, SYSTIMESTAMP)
,SUBSTR(text, i * 4000 + 1, 4000));
END LOOP;
COMMIT;
END msg;
PROCEDURE insert_form_debug_log
(logtable IN logtable_type
,date_table IN date_table_type
,username IN VARCHAR2
,module IN VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FORALL i IN logtable.FIRST..logtable.LAST
INSERT INTO DEBUG_LOG
(log_id
,ts
,text
,module
,username)
VALUES
(DEBUG_LOG_SEQ.NEXTVAL
,date_table(i)
,logtable(i)
,insert_form_debug_log.module
,insert_form_debug_log.username);
COMMIT;
END insert_form_debug_log;
END DB_FORMS_DEBUG;
Form Package Specification
PACKAGE PKG_DEBUG IS
-- store the log contents in a database table, then reset the log
PROCEDURE flush_to_table;
-- debug log message
PROCEDURE msg (text IN VARCHAR2);
-- spit out debug info when an unexpected error ocurs
PROCEDURE on_error;
-- spit out debug info when a message is issued to the user
PROCEDURE on_message;
--popup a window showing the contents of the debug log
PROCEDURE show_log;
-- log current useful status info
PROCEDURE status;
END PKG_DEBUG;
Form Package Body
PACKAGE BODY PKG_DEBUG IS
-- the debug log is a circular buffer of debug log messages
TYPE debug_log_table_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TYPE log_ts_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
con_debug_log_size CONSTANT PLS_INTEGER := 200;
debug_log_table debug_log_table_type;
log_ts_table log_ts_table_type;
debug_log_pos PLS_INTEGER;
PROCEDURE flush_to_table IS
-- send the debug log buffer to database table DEBUG_LOG
c INTEGER := 0;
i INTEGER := 1;
logtable DB_FORMS_DEBUG.logtable_type;
ts_table DB_FORMS_DEBUG.ts_table_type;
BEGIN
LOOP
EXIT WHEN i IS NULL;
c := c + 1;
logtable(c) := debug_log_table(i);
ts_table(c) := log_ts_table(i);
i := i + 1;
--wrap to the top of the buffer
IF i = con_debug_log_size THEN
i := 1;
END IF;
EXIT WHEN i = debug_log_pos OR NOT debug_log_table.EXISTS(i);
END LOOP;
IF logtable.COUNT > 0 THEN
--this log message will only survive if the db call fails
DB_FORMS_DEBUG.insert_form_debug_log
(logtable => logtable
,date_table => ts_table
,module => NAME_IN('SYSTEM.CURRENT_FORM')
,username => [user name/id...]);
debug_log_table.DELETE;
log_ts_table.DELETE;
debug_log_pos := NULL;
END IF;
END flush_to_table;
PROCEDURE msg (text IN VARCHAR2) IS
-- add debug log message to the scrolling log
BEGIN
debug_log_pos := MOD(NVL(debug_log_pos,0) + 1, con_debug_log_size);
debug_log_table(debug_log_pos) := SUBSTR(text,1,4000);
log_ts_table(debug_log_pos) := PKG_FORM.system_datetime;
END msg;
PROCEDURE on_error IS
-- Catch-all error handler for unexpected errors.
BEGIN
-- This should only ever be called when an *unexpected* error occurs.
--
-- If an error is expected in some circumstance, it should be handled (e.g. by putting
-- code in the on-error trigger to do something intelligent instead of calling this
-- catch-all procedure).
--
msg('DBMS ERROR : ' || DBMS_ERROR_CODE || ' ' || RTRIM(DBMS_ERROR_TEXT,CHR(10)));
status;
flush_to_table; -- so that support personnel may investigate
PKG_MESSAGE.error(ERROR_TYPE || '-' || ERROR_CODE || ' ' || ERROR_TEXT, 'System Error ' || ERROR_TYPE || '-' || ERROR_CODE);
END on_error;
PROCEDURE on_message IS
BEGIN
status;
MESSAGE(SUBSTR(MESSAGE_TYPE || '-' || MESSAGE_CODE || ' ' || MESSAGE_TEXT, 1, 200), ACKNOWLEDGE);
END on_message;
PROCEDURE show_log IS
i INTEGER := debug_log_pos;
BEGIN
-- fill the onscreen item CONTROL.DEBUGLOG starting
-- with the most recent debug line, and go backwards until
-- it is filled or there are no more debug log messages to show
-- It would be better for this to be done in a separate
-- form specifically for this purpose, to avoid problems
-- when we want to show the debug log without
-- firing validation triggers.
:CONTROL.debuglog := NULL;
LOOP
EXIT WHEN i IS NULL;
-- "64K should be enough for everybody"
IF :CONTROL.debuglog IS NOT NULL THEN
:CONTROL.debuglog := SUBSTR(CHR(10) || :CONTROL.debuglog, 1, 65534);
EXIT WHEN LENGTH(:CONTROL.debuglog) = 65534;
END IF;
:CONTROL.debuglog := SUBSTR(
TO_CHAR(i,'FM0000')
|| ' ' || TO_CHAR(log_ts_table(i),'HH24:MI:SS')
|| ' ' || debug_log_table(i)
|| :CONTROL.debuglog
, 1, 65534);
EXIT WHEN LENGTH(:CONTROL.debuglog) = 65534;
i := i - 1;
--wrap to the top of the buffer
IF i = 0 THEN
i := con_debug_log_size - 1;
END IF;
EXIT WHEN i = debug_log_pos OR NOT debug_log_table.EXISTS(i);
END LOOP;
GO_ITEM('CONTROL.debuglog');
check_package_failure;
END show_log;
PROCEDURE status IS
BEGIN
msg('FORM STATUS : ' || :SYSTEM.FORM_STATUS);
msg('RECORD STATUS : ' || :SYSTEM.RECORD_STATUS);
msg('TRIGGER ITEM : ' || NVL(:SYSTEM.TRIGGER_ITEM, :SYSTEM.TRIGGER_BLOCK)
|| ' RECORD #' || :SYSTEM.TRIGGER_RECORD);
msg('CURSOR ITEM : ' || :SYSTEM.CURSOR_ITEM
|| ' RECORD #' || :SYSTEM.CURSOR_RECORD);
END status;
END PKG_DEBUG;
This is just a post to document a standard utility functions that I like to create in a Oracle Forms library, if equivalents are not already available. Suggestions or critiques are welcome.
“No warranty is express or implied about the suitability of this code for any purpose.”
I’ll only create those things which I find become generally useful for a particular site. If something’s not used, get rid of it. That said, most of these things I’ve created because I use them frequently.
PROCEDURE assert (truth IN BOOLEAN, module IN VARCHAR2) IS
BEGIN
IF truth THEN
NULL;
ELSE --truth may be null or false
PKG_MESSAGE.error('Assertion failed in ' || module);
END IF;
END assert;
PROCEDURE check_package_failure IS
-- (this is generated automatically by Oracle Forms when a block
-- relation is created)
-- This should be called after various builtins that, instead of
-- doing the honourable thing (raising an exception), set
-- FORM_SUCCESS. These builtins are*:
-- CALL_FORM CALL_QUERY CANCEL_QUERY CLEAR_BLOCK CLEAR_FORM
-- CLEAR_ITEM CLEAR_RECORD COUNT_QUERY DELETE_RECORD DOWN
-- DO_KEY DUPLICATE_ITEM DUPLICATE_RECORD EDIT_TEXTITEM ENTER
-- ENTER_QUERY EXECUTE_QUERY EXECUTE_TRIGGER EXIT_FORM
-- FETCH_RECORDS FIRST_RECORD FORMS_DDL GO_BLOCK GO_FORM
-- GO_ITEM GO_RECORD HOST INSERT_RECORD LAST_RECORD
-- LIST_VALUES LOCK_RECORD LOGON LOGON_SCREEN LOGOUT
-- NEW_FORM NEXT_BLOCK NEXT_ITEM NEXT_FORM NEXT_KEY
-- NEXT_RECORD NEXT_SET OPEN_FORM PREVIOUS_BLOCK
-- PREVIOUS_FORM PREVIOUS_ITEM PREVIOUS_BLOCK UP
-- * if you notice any builtins missing from, or that should not be
-- included in this list - please let me know!
BEGIN
IF NOT ( Form_Success ) THEN
RAISE Form_Trigger_Failure;
END IF;
END check_package_failure;
PROCEDURE msg (text IN VARCHAR2) IS
-- just a convenient wrapper for PKG_DEBUG.msg
BEGIN
PKG_DEBUG.msg(text);
END msg;
PKG_DEBUG definition
Form-level trigger: pre-select
msg(:SYSTEM.LAST_QUERY);
Form-level trigger: on-error
msg('on-error ' || ERROR_TYPE || '-' || ERROR_CODE || ' ' || ERROR_TEXT);
-- add handlers here for errors that we don't want to spit the dummy on (i.e. handle gracefully)
CASE
WHEN ERROR_TYPE||'-'||ERROR_CODE IN
('FRM-41105' --"You cannot query records without a saved parent record."
) THEN
-- the default message is a little obtuse - give a standard response
MESSAGE('That function is not allowed here.', ACKNOWLEDGE);
RAISE FORM_TRIGGER_FAILURE;
WHEN ERROR_TYPE||'-'||ERROR_CODE IN
('FRM-40202' --"Field must be entered."
,'FRM-40203' --"Field must be entered completely."
,'FRM-40207' --"Must be in range x to y"
,'FRM-40209' --"Field must be of form x."
,'FRM-40212' --"Invalid value for field x."
,'FRM-40356' --"Invalid number in example record. Query not issued."
,'FRM-40357' --"Invalid string in example record. Query not issued."
,'FRM-40358' --"Invalid date in example record. Query not issued."
,'FRM-40359' --"Invalid date or time in example record. Query not issued."
,'FRM-40501' --"ORACLE error: unable to reserve record for update or delete."
,'FRM-40654' --"Record has been updated by another user. Re-query to see change."
,'FRM-40657' --"Record changed or deleted by another user."
,'FRM-41106' --"You cannot create records without a parent record."
) THEN
-- show the error message in a popup and raise FTF
PKG_FORM.error(ERROR_TEXT);
WHEN ERROR_TYPE||'-'||ERROR_CODE
BETWEEN 'FRM-50000' AND 'FRM-51000' THEN
-- almost all the FRM-5xxxx errors are to do with data format issues, e.g. date
-- fields, numbers, etc
PKG_FORM.error(ERROR_TEXT);
WHEN ERROR_TYPE||'-'||ERROR_CODE IN
('FRM-40100' --"At first record."
,'FRM-40102' --"Record must be entered or deleted first."
,'FRM-40110' --"At first block."
,'FRM-40111' --"At last block."
,'FRM-40200' --"Field is protected against update."
,'FRM-40201' --"Field is full. Can't insert character."
,'FRM-40401' --"No changes to save."
,'FRM-40405' --"No changes to apply."
,'FRM-41026' --"Field does not understand operation." (e.g. F9 on item with no list)
,'FRM-41050' --"You cannot update this record."
,'FRM-41051' --"You cannot create records here."
) THEN
-- just display the default error message on the status bar (or popup if >1 message in quick succession)
MESSAGE(ERROR_TEXT, ACKNOWLEDGE);
RAISE FORM_TRIGGER_FAILURE;
ELSE
PKG_DEBUG.on_error;
END CASE;
Form-level trigger: on-message
msg('on-message ' || MESSAGE_TYPE || '-' || MESSAGE_CODE || ' ' || MESSAGE_TEXT);
-- trap some messages and replace with more user-friendly message
CASE
WHEN MESSAGE_TYPE||'-'||MESSAGE_CODE IN
('FRM-40350' --"Query caused no records to be retrieved."
) THEN
PKG_FORM.note('No matching receipts found.', 'Search Results');
WHEN MESSAGE_TYPE||'-'||MESSAGE_CODE IN
('FRM-41800' --"List of Values not available for this field."
,'FRM-40400' --"Transaction complete: %s records applied and saved."
) THEN
-- the default message is user-friendly enough to show on the status bar
MESSAGE(MESSAGE_TEXT, ACKNOWLEDGE);
ELSE
PKG_DEBUG.on_message;
END CASE;
This is just a post to document the standard library that I like to create in Oracle Forms, if equivalents are not already available. Suggestions or critiques are welcome.
“No warranty is express or implied about the suitability of this code for any purpose.”
Forms Package Specification: PKG_MESSAGE
PACKAGE PKG_MESSAGE IS
PROCEDURE error
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Error');
PROCEDURE note
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Note');
PROCEDURE warning
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Warning');
END PKG_MESSAGE;
Forms Package Body: PKG_MESSAGE
PACKAGE PKG_MESSAGE IS
PROCEDURE error
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Error') IS
-- requires a suitable alert called "ERROR"
n NUMBER;
BEGIN
msg('PKG_MESSAGE.error('''||message||''','''||popup_title||''')');
SET_ALERT_PROPERTY('ERROR', TITLE, popup_title);
SET_ALERT_PROPERTY('ERROR', ALERT_MESSAGE_TEXT, message);
n := SHOW_ALERT('ERROR');
RAISE FORM_TRIGGER_FAILURE;
END error;
PROCEDURE note
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Note') IS
--requires a suitable alert "NOTE"
n NUMBER;
BEGIN
msg('PKG_MESSAGE.note('''||message||''','''||popup_title||''')');
SET_ALERT_PROPERTY('NOTE', TITLE, popup_title);
SET_ALERT_PROPERTY('NOTE', ALERT_MESSAGE_TEXT, message);
n := SHOW_ALERT('NOTE');
END note;
PROCEDURE warning
(message IN VARCHAR2
,popup_title IN VARCHAR2 := 'Warning') IS
-- requires a suitable alert "WARNING"
n NUMBER;
BEGIN
msg('PKG_MESSAGE.warning('''||message||''','''||popup_title||''')');
SET_ALERT_PROPERTY('WARNING', TITLE, popup_title);
SET_ALERT_PROPERTY('WARNING', ALERT_MESSAGE_TEXT, message);
n := SHOW_ALERT('WARNING');
END warning;
END PKG_MESSAGE;
Today, grasshopper, you will learn the Way of the Template. The Templating Way is the path by which complex output is produced in a harmonious fashion.
The Templating Way does not cobble a string together from bits and pieces in linear fashion.
htp.p('<HTML><HEAD><TITLE>'||:title
||'</TITLE></HEAD><BODY>'
||:body||'</BODY></HTML>');
The Templating Way separates the Template from the Substitutions; by this division is harmony achieved.
DECLARE
template VARCHAR2(200)
:= q'[
<HTML>
<HEAD>
<TITLE> #TITLE# </TITLE>
</HEAD>
<BODY> #BODY# </BODY>
</HTML>
]';
BEGIN
htp.p(
REPLACE( REPLACE( template
,'#TITLE#', :title)
,'#BODY#', :body)
);
END;
It is efficient – each substitution expression is evaluated once and once only, even if required many times within the template.
The Templating Way makes dynamic SQL easy to write and debug. It makes bugs shallower.
SELECT REPLACE(REPLACE(REPLACE(q'[
CREATE OR REPLACE TRIGGER #OWNER#.#TABLE#_BI
BEFORE INSERT ON #OWNER#.#TABLE#
FOR EACH ROW
BEGIN
IF :NEW.#COLUMN# IS NULL THEN
SELECT #TABLE#_SEQ.NEXTVAL
INTO :NEW.#COLUMN#
FROM DUAL;
END IF;
END;
]', '#OWNER#', USER)
, '#TABLE#', cc.table_name)
, '#COLUMN#', cc.column_name) AS ddl
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_type = 'P'
AND c.constraint_name = cc.constraint_name
AND cc.column_name like '%NO';
The Templating Way is simple, but looks complex to the uninitiated. It is readable, and affords maintainability.
If you have a multi-record block, and you use the handy Current Record Visual Attribute (CRVA) to set, say, a different background colour on all the items in the currently selected record, you may have run into this problem.
If you want to conditionally switch the visual attribute for certain items, at the item instance (i.e. record) level, this causes the CRVA to be overwritten, and the current record won’t be consistently highlighted. To get around this problem, a bit more code is required.
For example, let’s say you have a multi-record block called EMP, and it has two items that are sometimes gray, sometimes white – SALARY and ROLE. You have defined the following visual attributes:
EDITABLE_ITEM – white background
READONLY_ITEM – gray background
CURRENT_RECORD – blue background
Package Specification
PACKAGE EMP_BLOCK IS
PROCEDURE highlight_current_record;
PROCEDURE when_clear_block;
PROCEDURE when_remove_record;
END EMP_BLOCK;
Package Body
PACKAGE EMP_BLOCK IS
record_highlighted INTEGER;
PROCEDURE highlight_current_record IS
rec INTEGER := GET_BLOCK_PROPERTY('EMP', CURRENT_RECORD);
PROCEDURE set_visattr
(itemn IN VARCHAR2
,rec IN NUMBER
,visattr IN VARCHAR2) IS
BEGIN
IF visattr IS NULL THEN
-- (we could, if needed, make this more intelligent about
-- detecting whether the record is NEW/INSERT/CHANGED,
-- and examine the INSERT_ALLOWED/UPDATE_ALLOWED
-- properties accordingly)
IF GET_ITEM_INSTANCE_PROPERTY
(itemn
,record_highlighted
,UPDATE_ALLOWED) = 'TRUE' THEN
set_visattr(itemn, record_highlighted, 'EDITABLE_ITEM');
ELSE
set_visattr(itemn, record_highlighted, 'READONLY_ITEM');
END IF;
ELSE
SET_ITEM_INSTANCE_PROPERTY
(itemn
,rec
,VISUAL_ATTRIBUTE
,visattr);
END IF;
END set_visattr;
BEGIN
-- Note: if record_highlighted is null, then no record
-- is currently highlighted
IF rec != record_highlighted THEN
--un-highlight the record that was highlighted
set_visattr('EMP.SALARY', record_highlighted);
set_visattr('EMP.ROLE', record_highlighted);
END IF;
--highlight the newly selected record
set_visattr('EMP.SALARY', rec, 'CURRENT_RECORD');
set_visattr('EMP.ROLE', rec, 'CURRENT_RECORD');
record_highlighted := rec;
END highlight_current_record;
PROCEDURE when_clear_block IS
BEGIN
record_highlighted := NULL;
END when_clear_block;
PROCEDURE when_remove_record IS
BEGIN
IF record_highlighted = :SYSTEM.TRIGGER_RECORD THEN
record_highlighted := NULL;
END IF;
END when_remove_record;
END EMP_BLOCK;
Block-level triggers on EMP:
when-new-record-instance
EMP_BLOCK.highlight_current_record;
when-clear-block
EMP_BLOCK.when_clear_block;
when-remove-record
EMP_BLOCK.when_remove_record;
Also, whenever your code modifies the UPDATE_ALLOWED property on SALARY or ROLE, it must then call EMP_BLOCK.highlight_current_record again.
