Pl/sql | MacLochlainns Weblog

My co-author got his author copies about a week before me. The technical editor got his complementary book too the same day. I was wondering where mine got routed but now I’ve got mine, a few days after it was available on Amazon.

I think the new book looks good. John and I were probably a real pain during the editing process because we kept asking for more control during the proof copy to get things fixed. I think we messed up the release schedule by a couple weeks.

John and I think that the mastery questions in each chapter work well. My students who’ve seen it think it’s great because of the review sections and its smaller size. I’m re-verifying the code now, and it should be on McGraw-Hill’s website next week.

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.

Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.

You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.

Simple loop with local variables ↓

This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare local variables that are anchored to column data types.
  lv_title     item.item_title%TYPE;
  lv_subtitle  item.item_subtitle%TYPE;
  lv_rating    item.item_rating%TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the three local variables.
    FETCH c
    INTO  lv_title
    ,     lv_subtitle
    ,     lv_rating;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variables on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||lv_title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||lv_rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local record structure variable ↓

This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a local record data type, with explicit data types (you could use %TYPE here too).
  TYPE title_type IS RECORD
  ( title     VARCHAR2(60)
  , subtitle  VARCHAR2(60)
  , rating    VARCHAR2(8));
 
  -- Declare a local variable of the local record structure data type.
  item_record TITLE_TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local cursor structure variable ↓

This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

For loop with an implicit record structure variable ↓

This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block.
  FOR i IN c LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

For loop without a declaration block ↓

This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block with the static cursor in the definition.
  FOR i IN (SELECT   i.item_title AS title
	        ,        i.item_subtitle AS subtitle
	        ,        i.item_rating AS rating
	        FROM     item i) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

WHILE loop with a cursor guard on entry statement ↓

This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.

Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.

Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Fetch the first record to put into context the cursor attributes, like %FOUND.
  FETCH c INTO item_record;
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (c%FOUND) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
    -- Fetch the second and subsequent rows of the cursor and assign it to a local variables.
    FETCH c
    INTO  item_record;
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (TRUE) LOOP
 
    -- Fetch the record set into a user-defined variable.
    FETCH c INTO item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.

I’m sure this will help my students and hope it helps somebody else.

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.

-- This resets the default port so that it doesn't conflict with other environment.
EXECUTE dbms_xdb.SETHTTPPORT(8181);
 
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
 
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');

Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.

CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
/

Open a browser of your choice, and enter the following URL.

http://localhost:8181/sampledb/helloworld

You then see (or should see) the following Basic HTTP Authentication dialog box. Enter the STUDENT user as the User Name and the Password for the database account. Then, click the OK button.

Provided you enter the User Name and Password correctly, you should see the following inside the browser’s display panel. The USER name is a system session scope variable, which will always return the owner of the package because its created as a Definers Rights procedure.

You have now successfully configured your Basic HTTP Authentication XDB, which may offer you some possibilities outside of using Oracle APEX.

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:

SQL> @?/rdbms/admin/epgstat.sql

If it returns the following as the last element of the output, you’ll need to run the PL/SQL block below.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+
 
Allow repository anonymous access?
----------------------------------
false
 
1 row selected.

When you run this script, make sure you’re the privileged SYS user. Then, rerun the epgstat.sql script to verify that you’ve enabled anonymous access to the repository. You may also need to refresh your browser cache before retesting it.

SET SERVEROUTPUT ON
DECLARE
  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  lv_configxml := DBMS_XDB.cfg_get();
 
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element inserted.');
  ELSE
    -- Update existing element.
    SELECT updateXML
           ( DBMS_XDB.cfg_get()
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()'
           , lv_value
           , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  -- Configure the element.
  DBMS_XDB.cfg_update(lv_configxml);
  DBMS_XDB.cfg_refresh;
END;
/

These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.

-- Unlock the user account.
ALTER USER anonymous ACCOUNT UNLOCK;
-- Ensure a password is assigned to the account so you can create a synonym later.
ALTER USER anonymous IDENTIFIED BY ANONYMOUS;

These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.

-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
 
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
 
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');

Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.

SQL> GRANT EXECUTE ON helloworld TO anonymous;

Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)

SQL> CREATE SYNONYM helloworld FOR student.helloworld;

After you’ve created the synonym, you want to remove the password from the ANONYMOUS account. The following syntax lets you do that as the privileged SYSTEM user.

SQL> ALTER USER anonymous IDENTIFIED BY NULL;

Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.

http://localhost:8181/db/helloworld

You should see the same browser panel information as that shown by step #3 above, except one thing. The difference is the user name, which should now be ANONYMOUS. The execution occurs with the permissions of the invoker. This means you’ll see the data you’re allowed to see by the owning schema.

Somebody asked me why the DBMS_EPG.GET_DAD_LIST is a procedure because you can’t just simply list the DAD values. I answered that Oracle chose to implement it that way. Then, they asked how they could query it. I suggested they just run the epgstat.sql diagnostic script provided in the $ORACLE_HOME/rdbms directory, which provides those values and much more.

You can run the diagnostic script as the SYS privileged user, or as any user that has been granted the XDBADMIN role, like this:

SQL> @?/rdbms/admin/epgstat.sql

Notwithstanding the diagnostic script, they asked how you could wrap the OUT mode PL/SQL data type in the procedure call, and return the list of values in a SQL query. Because the formal parameter is a PL/SQL data type, this requires two key things. One is a local variable that maps to the DBMS_EPG package collection data type, and a pipelined table function. Here’s one way to solve the problem:

CREATE OR REPLACE TYPE dad_list AS TABLE OF VARCHAR2(4000);
/
 
CREATE OR REPLACE FUNCTION get_dbms_epg_dads
RETURN dad_list
PIPELINED IS
  -- Deine a local variable as the OUT mode target of GET_DAD_LIST procedure.
  SOURCE DBMS_EPG.VARCHAR2_TABLE;
  -- Declare a local variable of the SQL collection data type.  
  list DAD_LIST := dad_list(); 
BEGIN
  -- Call the procedure to populate the source.
  dbms_epg.get_dad_list(SOURCE);
  -- Extend space for all defined DAD values.
  list.EXTEND(SOURCE.COUNT);
  -- Assign values from PL/SQL collection to SQL collection.
  FOR i IN 1..source.COUNT LOOP
    list(i) := SOURCE(i);
    PIPE ROW(list(i));
  END LOOP;
  RETURN;
END get_dbms_epg_dads;
/
 
-- Set SQL*Plus width.
SET LINESIZE 79
 
-- Query collection.
SELECT column_value AS "DAD LIST"
FROM   TABLE(get_dbms_epg_dads);

Marco Gralike provided a simpler approach them the Pipelined Table Function here. I’ve copied the code example below:

SELECT u.dad AS "PL/SQL DAD List"
FROM   XMLTable(XMLNAMESPACES ( DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd' )
       , '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-language="PL/SQL"]'
       PASSING DBMS_XDB.CFG_GET()
       COLUMNS DAD VARCHAR2(15)
       PATH '/servlet/servlet-name/text()') u;

Hope this proves handy to somebody else, too.

While working on one of the labs, my students wanted a quick way to seed their CALENDAR table. Here’s a small script to seed twenty years of a calendar.

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME = 'MOCK_CALENDAR') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE mock_calendar
( short_month  VARCHAR2(3)
, long_month   VARCHAR2(9)
, start_date   DATE
, end_date     DATE );
 
-- Seed the table with 10 years of data.
DECLARE
  -- Create local collection data types.
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  long_month  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');
 
  -- Declare base dates.
  start_date DATE := '01-JAN-79';
  end_date   DATE := '31-JAN-79';
 
  -- Declare years.
  years      NUMBER := 20;
 
BEGIN
 
  -- Loop through years and months.
  FOR i IN 1..years LOOP
    FOR j IN 1..short_month.COUNT LOOP
      INSERT INTO mock_calendar VALUES
      ( short_month(j)
      , long_month(j)
      , add_months(start_date,(j-1)+(12*(i-1)))
      , add_months(end_date,(j-1)+(12*(i-1))));
    END LOOP;
  END LOOP;
 
END;
/
 
-- Format set break for output.
SET PAGESIZE 16
 
-- Format column output.
COL short_month FORMAT A5 HEADING "Short|Month"
COL long_month  FORMAT A9 HEADING "Long|Month"
COL start_date  FORMAT A9 HEADING "Start|Date"
COL end_date    FORMAT A9 HEADING "End|Date" 
 
SELECT * FROM mock_calendar;

The output will look like this with a page break by year:

Short Long      Start     End
Month Month     Date      Date
----- --------- --------- ---------
JAN   January   01-JAN-79 31-JAN-79
FEB   February  01-FEB-79 28-FEB-79
MAR   March     01-MAR-79 31-MAR-79
APR   April     01-APR-79 30-APR-79
MAY   May       01-MAY-79 31-MAY-79
JUN   June      01-JUN-79 30-JUN-79
JUL   July      01-JUL-79 31-JUL-79
AUG   August    01-AUG-79 31-AUG-79
SEP   September 01-SEP-79 30-SEP-79
OCT   October   01-OCT-79 31-OCT-79
NOV   November  01-NOV-79 30-NOV-79
DEC   December  01-DEC-79 31-DEC-79

I’m back from vacation and will start blogging again. I’ve got a couple items folks have asked me to cover and I’ll try to work those in during the next couple of weeks.

My son went over to Beijing to study Chinese this summer. I picked him up at the Airport last Friday. When he was there he found a copy of my Oracle Database 11g PL/SQL Programming in Chinese. He told me about it via Skype. I asked him to bring home a copy since my royalty statements don’t show the book was translated into Chinese. It cost about $12 US (click on the image to see a larger copy). The statements indicate it has only been translated into Russian, Portuguese, and Tata’s Indian Press at this point.

I thought it was an illegal copy, even though it was sold in a mainline store in Beijing as an original. Anyway I sent the scans of the book and ISBN to McGraw-Hill so they could sort it out. They told me it’s a legal copy, translation licensed, and that not all translations fees post to royalty statements before the translation. It’s only the fifth book, and I continue to learn about the process. Tsinghua University Press produces it. If you want a copy in Chinese, you’ll find it at their web site and my book is here. My son said they had an impressive collection of Oracle Press books on sale there too.

The fact that you could raise an ugly error when you query an external table always bothered me. I looked at Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy.

If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don’t know about objects and object collection, you can refer to this post. Having provided you with the context, here’s an example that eliminates errors when querying an external table without an external file.

Create an external file, like this character table.

CREATE TABLE CHARACTER
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'character.bad'
      DISCARDFILE 'DOWNLOAD':'character.dis'
      LOGFILE     'DOWNLOAD':'character.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;

Create a user-defined object type that mirrors your external table defintion, like this:

CREATE OR REPLACE TYPE character_obj IS OBJECT
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20));
/

Create a user-defined collection of your object type, like

CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/

Create a function that returns the user-defined collection of your object type, like

CREATE OR REPLACE FUNCTION character_source
RETURN character_obj_table IS
  c          NUMBER;
  collection CHARACTER_OBJ_TABLE := character_obj_table();
BEGIN
  FOR i IN (SELECT * FROM CHARACTER) LOOP
    collection.EXTEND;
    collection(c) := character_obj( i.character_id
                                  , i.first_name
                                  , i.last_name);
    c := c + 1;
  END LOOP;
  RETURN collection;
EXCEPTION
  WHEN OTHERS THEN
    RETURN collection;
END;
/

Query the function not the table, which returns no rows found when the file doesn’t physically exist, or the file contains no data. Lastly, the function returns the data when it is there.

SELECT * FROM TABLE(character_source);

Hope this helps those using external tables to avoid the typical error stack:

SELECT * FROM CHARACTER
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file CHARACTER.csv IN CHARACTER NOT found

I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.

I saw a post in the OTN forum that asked a simple question and had no simple example as an answer, so I thought it would be nice to provide one. Basically, somebody wanted to know how to call into a stored procedure and return a value with more or less one pass-by-value and another pass-by-reference variable.

This defines a simple echo procedure, which takes a message and returns a formatted message:

CREATE OR REPLACE PROCEDURE echo
( message IN     VARCHAR2
, reply      OUT VARCHAR2 ) IS
BEGIN
  reply := 'Message ['||message||'] received.';
END;
/

The following PHP calls the procedure and returns the value. It uses the required connection syntax for the Zend Server Community Edition.

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN echo(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you need to size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

You can then test it with or without a parameter, like this example with a parameter:

http://localhost/message_reply.php?msg="Sample message"

I put a link in the forum to this, and I hope it helps a few folks.

I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.

If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!

The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.

Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.

I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PL/SQL: Item ignored
4/26     PLS-00307: too many declarations OF 'HELLO_THERE' MATCH this CALL
6/5      PL/SQL: Statement ignored
6/13     PLS-00320: the declaration OF the TYPE OF this expression IS incomplete OR malformed

All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.

Broken Code ↓

This shows you the broken code and explains why it’s broken.

The broken code has an object type like the following. Interestingly enough, the object type will compile fine but the object body fails when the attribute variable name differs from a constructor function parameter value.

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( pv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

The broken code has an object body like the following. The difference between the parameter name in the constructor and the object type attribute name causes the PLS-00307 exception. It took some playing around to figure out what it was really complaining about.

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (pv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := pv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

Working Code ↓

This shows you the working code and explains why it works.

The working code has an object type like the following. You should notice that the only difference renames the pv_who in the overriding constructor’s parameter list to iv_who. While this doesn’t throw an exception creating the user-defined object type, it does throw an exception when you try to create the object body or implementation of the object type.

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( iv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

The working code has an object body like the following. Like the object type before, the pv_who as a formal parameter of the constructor now uses the same variable name as the attribute for the object type.

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    SELF := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (iv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    SELF.iv_who := iv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    SELF.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||SELF.iv_who);
  END to_string;
 
END;
/

This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.

MacLochlainns Weblog

Archive for the ‘pl/sql’ Category

My author’s copies came

Sample PL/SQL Cursor Loops

Oracle 11g XDB Shake & Bake

Oracle 11g XDB DADs

Seeding a Calendar Table

Copyright or not?

External Table Query Fix

PHP OUT mode Parameter

PHP, LOBs, and Oracle

Object constructor quirk

Recent Posts

Things Written About

Pages

Blogroll

Archives