MacLochlainns Weblog

While working through examples for my students on uploading data, I thought it would be interesting to demonstrate how to create a re-runnable upload. Especially when chatting with a friend who was unaware that you could use joins inside the source element of a MERGE statement. Naturally, the MERGE statement seemed like the best approach in an Oracle database because with my criteria:

• The source file would not include any surrogate key values.
• The source file would have denormalized record sets with data that should belong to parent and child tables, technically unnormalized form (UNF).
• Primary and foreign key values would be determined on load to the tables.
• There could be a one-to-many relationship between the parent and child tables in the original source.
• Subsequent data sets may replicate data already seeded or not in the tables.
• Avoid any complex PL/SQL structures.

Step #1 : Create a Virtual Directory

You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a C:\Data\Download file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user. The syntax for these steps is:

CREATE DIRECTORY download AS 'C:\Data\Download';
GRANT READ, WRITE ON DIRECTORY download TO student;

If you want more detail on these steps, check this older post on the blog.

Step #2 : Create the Target and External Tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP 
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP 
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create normalized kingdom table.
CREATE TABLE kingdom
( kingdom_id    NUMBER
, kingdom_name  VARCHAR2(20)
, population    NUMBER);
 
-- Create a sequence for the kingdom table.
CREATE SEQUENCE kingdom_s1;
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             NUMBER
, knight_name           VARCHAR2(24)
, kingdom_allegiance_id NUMBER
, allegiance_start_date DATE
, allegiance_end_date   DATE);
 
-- Create a sequence for the knight table.
CREATE SEQUENCE knight_s1;
 
-- Create external import table.
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR2(20)
, population            NUMBER
, knight_name           VARCHAR2(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'kingdom_import.bad'
      DISCARDFILE 'DOWNLOAD':'kingdom_import.dis'
      LOGFILE     'DOWNLOAD':'kingdom_import.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('kingdom_import.csv'))
REJECT LIMIT UNLIMITED;

Step #3 : Create a Procedure to ensure an all or nothing transaction

The procedure ensures that an all or nothing transaction occurs to both tables. Inside the procedure you have two MERGE statements.

The first MERGE statement uses a LEFT JOIN to ensure that any new kingdom_name will be added to the kingdom table. The kingdom_name and population columns are the natural key in this model. The second MERGE statement uses an INNER JOIN to ensure that knight rows are only inserted when they belong to an existing kingdom_name. Naturally, the primary key capture occurs in this statement and it maps the primary key to the foreign key column in the knight table.

The complete procedure code follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- Create a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_kingdom IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO kingdom target
  USING (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) source
  ON (target.kingdom_id = source.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET kingdom_name = source.kingdom_name
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( kingdom_s1.NEXTVAL
  , source.kingdom_name
  , source.population);
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO knight target
  USING (SELECT   k.kingdom_id
         ,        kki.knight_name
         ,        kki.allegiance_start_date AS start_date
         ,        kki.allegiance_end_date AS end_date
         FROM     kingdom_knight_import kki INNER JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) source
  ON (target.kingdom_allegiance_id = source.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET allegiance_start_date = source.start_date
  ,          allegiance_end_date = source.end_date
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( knight_s1.NEXTVAL
  , source.knight_name
  , source.kingdom_id
  , source.start_date
  , source.end_date);
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Step #4 : Test the Process

You can test it by calling the procedure. Rerunning it will demonstrate that it doesn’t violate any of the rules.

EXECUTE upload_kingdom;

As always, I hope this is useful to somebody besides me.

I got a request Saturday for me to post code for the Oracle Database 11g PL/SQL Programming Workbook. You can download the book code here. It should also be on the McGraw-Hill web site tomorrow.

The irony for me is the timing of the request. I didn’t get it until late Saturday night when I had to make an early plane to Dallas, Texas on Sunday morning. It teaches me once again, that I should keep my book updates in one place and backup in a convenient carry-anywhere location.

I also found out that the Bulletin Board I’d set up wasn’t accessible. At least, accessible to anybody but bots. I uninstalled and re-installed it, and configured it. Now I’ll start maintaining it. You can find it at http://plsql11g.net.

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 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

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;
/

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.

1. 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');

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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;
/

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

http://localhost:8181/sampledb/helloworld

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.

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

+-------------------------------------------------------------------+
| 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

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;
/

5. 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;

6. 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');

7. 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;

8. 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;

SQL> ALTER USER anonymous IDENTIFIED BY NULL;

9. 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

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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:

1
2
3
4
5
6

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.

1. 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;

2. 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));
/

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

CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/

4. 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;
/

5. 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:

1
2
3
4
5
6
7

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

<?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.