Month: August 2010

Editing Oracle Reports

If you’re like me, the above picture will probably have given you a frisson of dread. Just a little. The example here is not a very complicated report, just a few repeating groups based on quite a nice data model. What catches me every time, however, is a few little apparent quirks in Oracle Reports – and I’ve decided to post them here so that I’ll remember them next time I have to maintain someone’s reports. I keep forgetting them because I don’t have to work with Oracle Reports very often.

(These instructions are for Oracle Reports 10g – 9.0.4.0.33)

  1. When you change the structure of the query, it will often change the order of the labels in the model editor – and the order is important. So, don’t forget to check that the order of the columns matches the order of the columns in the query.
  2. Don’t forget to add the data links between the query groups. Otherwise, you may find the report takes a looooong time to run – if the status page says something like “Generating page 557,634…” for a report you expected to fit on 1 page, this may be the cause.
  3. If you modify a query, you may find that some or all data links to that group will now be invalid (e.g. if you add a GROUP BY to a query that is a child group, you may find that at runtime it will simply ignore the data link entirely – leading to the dreaded million-page report). You may have to delete the data links and recreate them. To create a data link:
    1. Click the Data Link icon (View -> Tool Palette)
    2. Mouse down on the join column in the parent group
    3. Drag the line to the join column in the child group, and release.
      If the Data Link cannot be created for some reason (e.g. because of a Group By in the child group), Reports will then create a generic “group link” – which means you need to add the join criteria manually to the WHERE clause in the child query.
  4. When adding a calculated field, don’t forget to change the default data type from Number to Char or Date if applicable. Otherwise, you’ll probably get something like “Invalid value for CF_BLA” at runtime.
  5. When editing the print layout, don’t forget to review these important modes accessible from the toolbar. In particular: Confine On / Confine Off, Flex On / Flex Off, Select Parent Frame. For example, to move something from one parent frame to another:
    1. Select the object.
    2. Set Confine Off.
    3. Move the object to the desired location.
    4. Set Confine On.
    5. See if the hierarchy is now all messed up. If so, Undo and try again – make sure the object is fully within the target parent before you set Confine On.

Directory File List in PL/SQL (Oracle 10g)

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.

Purge all Recyclebins without SYSDBA

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.

Forms Library: 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.”

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;

In Oracle, ” = NULL but NULL != ”

When I get the result of my PL/SQL quiz for the day, I’m pleased when I got it right, but if I got it wrong, I’m either annoyed or overjoyed:

  1. If I disagreed with the result, I’m annoyed.
  2. If I agreed with the result, I’m overjoyed – because I learned something new, or I was reminded of something I should have remembered.

Option #2 was my experience this morning – yesterday’s quiz featured the following code snippet:

...
   EXECUTE IMMEDIATE 'update my_table set my_column = :value'
   USING NULL;
...

This was one of four other, very similar, options – and I failed to notice that this version was binding NULL directly into the statement, instead of using a variable as any normal, reasonable, rational human being would. This snippet raises PLS-00457: expressions have to be of SQL types, which in this case is due to the fact that NULL is of no particular SQL type.

If one wanted to bind a literal NULL into a statement such as the one above, you don’t necessarily need a variable:

...
   EXECUTE IMMEDIATE 'update my_table set my_column = :value'
   USING '';
...

Proving that while is NULL, NULL is not – they are not always interchangeable.

P.S. please ignore the post title – I know it is incorrect to write ” = NULL or NULL != ” – but it wasn’t meant to be code, ok?