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.

A couple months ago, I tried to install Oracle Database 10g, Release 2 for Mac OS X on Intel x86-64 on a MacBook running generic Snow Leopard. It didn’t work because you must have the Mac OS X Server. Somebody asked me to post more of what I found when I tried to hack the installation. Oracle’s documentation Oracle™ Database Installation Guide 10g Release 2 (10.2) for Apple Mac OS X (Intel), B25286-01 clearly states that the installation requires Mac OS X Server edition. By the way, this is one of the best installation guides I’ve seen from Oracle because it is very complete.

You can determine which version of Mac OS X you’re running with the following:

# sw_vers

It won’t work when you’re on a generic Snow Leopard release, like this:

ProductName:	Mac OS X
ProductVersion:	10.6.2
BuildVersion:	10C540

The first place you’ll get stopped cold is during the installation. You can specify the kern.sysv.shmmni value in the sysctl.conf file at the required 4096 but it won’t allow reconfiguration from the default value of 32. The inability to override this value stops the Oracle installer. You can see the default value of the shared memory parameters by running this command in a terminal session:

# /usr/sbin/sysctl -a | grep \\.shm

You should see the following:

kern.sysv.shmmax: 4194304
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 1024

I haven’t tested it on the Mac OS X Server edition yet. I’ve a hunch that it’ll work like a charm. When I do get to that, I’ll update the post. Here’s the Apple support note on kern.sysv.shmmni for the Server version.

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.

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.

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.

I was working on a code example for my database class, got in a hurry, and changed a table name without dropping the original table. Oops!

Naturally, I got this error message.

  LOB (administrator_photo) STORE AS admin_photo
                                     *
ERROR at line 5:
ORA-00955: name IS already used BY an existing object

The LOB segment name existed but why and where. It was in the table that I forgot to drop. This query find the latent table and column while illustrating the relationship (for my students and others) between an OBJECT_NAME and SEGMENT_NAME:

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');

Now, I can grab it later because it’ll happen again. 😉 At Samy mentioned in his comment you also have the option of using ALL_ or DBA_ views when you’re a DBA.

The rumor is that my database is hard, but I’ve always hoped it was fun and laid the foundation of success for my students. As I walked into class to give them their final exam, they had their jackets on, which isn’t uncommon for Rexburg, Idaho in December. However, that’s normal outside but unusual in the heated classroom. Then, they all took their jackets off to show their new t-shirts.

They thought it would be fun to post on the blog, so here it is. The shirts says:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    class= CIT 320;

It’s unfortunate that they missed the enclosing quote marks around the string literal. 😉 It should be like this:

SELECT   i.survived
FROM     michael_mclaughlin i
WHERE    i.class= 'CIT 320';

Here’s to a great group of students who know how to read, write, and think SQL. Any openings out there for internships, please drop me a note.

Happy holidays!

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.

Over a year ago, I put out a blog page on how to query the contents of a table from an Oracle database through Excel. I meant to get back to provide much more about this. Specifically, I want to cover the XML mechanism used to accomplish the task. However, a blog reader augmented my page with a solution comment. I’m thrilled he did because it tells me to get a basic thing out rather than hang folks up halfway. My thanks go to Vernon.

Here’s a quick addendum to the original post with screen shots because folks tell me they’re very useful. It demonstrates how you write queries in Microsoft Excel against the Oracle database. While I plan a full tutorial on a different URL, this should help everybody in the meantime. This content is dependent on the generic mechanism, which I covered in this older post.

It starts with an alternative to Step #9 in the older blog page, and therefore, I’ve started the numbering as 9 there.

9. The Import Data dialog asks you how and where you want to import it. When you don’t want the contents of a simple table, click the Properties button.

10. Clicking the prior dialog’s Property button brings you to the Connection Properties dialog. The Usage tab is first for a reason but the Definition tab is where you need to go to enter a free form query. Click the Definition tab highlighted in yellow below.

11. In this dialog, the most important boxes are the Command type (as you may guess, Table is the default value) and Command text boxes. The first thing to do, is click on the Command type multiple select widget and choose SQL.

Now, you can write any query that you’d like against the selected Oracle schema. You may reference any tables or views for which your user (schema) has SELECT permissions. You don’t need to limit yourself to a single table because ANSI SQL:89 (comma delimited tables), and ANSI SQL:92 (INNER JOIN, et cetera) are both supported. Don’t forget that Oracle uses a different concatenation method than Microsoft Access or SQL Server, you can find that here. After you’ve entered your query, click the OK button to proceed.

12. When you clicked OK, you’ve instructed Microsoft Excel 2007 to change the connection, it raises this dialog box (click on it if you want to read it). You click Yes to continue and save your query.

13. You’re back to where you began, more or less, this it the Import Data dialog. This is Step #9 but this time it’ll run your query not return a simple table content. Click OK to run the query. Don’t be surprised if you get a message saying no password was provided. It just means you’ll be prompted to enter one. You should never store that password because it’s stored in plain text inside an XML file.

You can find those data source files in this directory:

C:\Document and Settings\<some_user>\My Documents\My Data Sources