# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## DBMS_JAVA Privilege Error?

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read'); 6 END; 7 /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM TABLE(list_files(get_directory_path('STAGE'))) * ERROR AT line 2: ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION: JAVA.security.AccessControlException: DIRECTORY permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read,write,delete'); 6 END; 7 /

You would get this message when trying to delete an external file:

BEGIN * ERROR AT line 1: ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION: JAVA.security.AccessControlException: the Permission (JAVA.io.FilePermission c:\data\LOG\item_import.LOG read) has NOT been granted TO IMPORTER. The PL/SQL TO GRANT this IS dbms_java.grant_permission( 'IMPORTER', 'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' ) ORA-06512: AT "IMPORTER.DELETE_FILE", line 1 ORA-06512: AT line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload\item_import.log' 5 ,'read,write,delete'); 6 END; 7 /

As always, I hope this helps other developers.

Written by maclochlainn

March 29th, 2014 at 1:59 pm

## Hidden DBMS_JAVA Nuance

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN * ERROR at line 1: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: oracle.aurora.vm.IdNotFoundException: importer : USER OR ROLE id does NOT exist ORA-06512: at "SYS.DBMS_JAVA", line 705 ORA-06512: at line 2

The problem was simple, while not published, you must enter the user/schema name in uppercase text when calling DBMS_JAVA.GRANT_PERMISSION procedure. Here’s a proper example:

SQL> BEGIN 2 DBMS_JAVA.GRANT_PERMISSION('IMPORTER' 3 ,'SYS:java.io.FilePermission' 4 ,'C:\Data\Upload' 5 ,'read'); 6 END; 7 /

If you change the IMPORTER string to lower or mixed case, you raise the aforementioned error. Quite an interesting tidbit that I missed from Oracle Database 10g until Oracle Database 12c. Hope this helps you avoid wondering what’s happening when your line 2 looks like this:

 2 DBMS_JAVA.GRANT_PERMISSION('Importer'

As always, I hope this helps others. If you like more on writing Java inside the Oracle database you can check this older post.

Written by maclochlainn

March 21st, 2014 at 12:44 am

## Deprecated mysqli Functions

Having noticed the release of PHP 5.5.10 last week while I was speaking at UTOUG, I checked the deprecated mysqli functions web page. There weren’t any deprecated by 5.5. Unfortuantely, there were six mysqli functions deprecated in 5.3 and removed in 5.4. Unfortunately, many of my posted code examples use 5.2 or 5.3 where they would have worked. The deprecated mysqli functions are:

• mysqli_bind_param
• mysqli_bind_result
• mysqli_client_encoding
• mysqli_fetch
• mysqli_param_count
• mysqli_send_long_data

Unfortunately, that puts a lot of updates on my queue of things to do. I imagine it likewise imposes limits on those running commercially on PHP 5.3 or older that plan an upgrade.

It does remind me that I need to pay close attention to the deprecation of mysqli functions with each release. These are actually the largest change since PHP 5.0.

Written by maclochlainn

March 18th, 2014 at 12:28 pm

Posted in MySQL,mysqli,PHP

Tagged with , ,

## Capture MySQL Foreign Keys

with one comment

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' ' ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' ' ,'FOREIGN KEY (',kcu.column_name,')',' ' ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' ,'(',kcu.referenced_column_name,');') AS script FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' ORDER BY tc.TABLE_NAME , kcu.column_name;

You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:

AND tc.table_schema = 'your_mysql_database' AND tc.table_name = 'your_table_name'

Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.

The command_list table that stores the values is:

CREATE TABLE command_list ( command_list_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , sql_command VARCHAR(6) NOT NULL , sql_object VARCHAR(10) NOT NULL , sql_constraint VARCHAR(11) , sql_statement VARCHAR(768) NOT NULL);

This is the storedForeignKeys procedure:

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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 CREATE PROCEDURE storeForeignKeys ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64)) BEGIN   /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE sql_stmt VARCHAR(1024);   /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0;   /* Declare local cursor for foreign key table, it uses null replacement because the procedure supports null parameters. When you use null parameters, you get all foreign key values. */ DECLARE foreign_key_table CURSOR FOR SELECT tc.table_schema , tc.table_name , tc.constraint_name FROM information_schema.table_constraints tc WHERE tc.table_schema = IFNULL(lv_schema_name, tc.table_schema) AND tc.table_name = IFNULL(lv_table_name, tc.table_name) AND tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.table_name;   /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;   /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name;   /* Open a local cursor. */ OPEN foreign_key_table; cursor_foreign_key_table: LOOP   /* Fetch a row into the local variables. */ FETCH foreign_key_table INTO lv_schema_name , lv_table_name , lv_constraint_name;   /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF;   /* The nested calls to the columnList function returns the list of columns in the foreign key. Surrogate primary to foreign keys can be resolved with a simply query but natural primary to foreign key relationships require the list of columns involved in the primary and foreign key. The columnList function returns the list of foreign key columns in the dependent table and the list of referenced columns (or the primary key columns) in the independent table. */ SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' ' ,'ADD CONSTRAINT ',lv_constraint_name,' ' ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name));   /* Record the SQL statements. */ INSERT INTO command_list ( sql_command , sql_object , sql_constraint , sql_statement ) VALUES ('ALTER' ,'TABLE' ,'FOREIGN KEY' , sql_stmt );   END LOOP cursor_foreign_key_table; CLOSE foreign_key_table;   END; $$ This is the columnList function: 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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 CREATE FUNCTION columnList ( pv_schema_name VARCHAR(64) , pv_table_name VARCHAR(64) , pv_constraint_name VARCHAR(64)) RETURNS VARCHAR(512) BEGIN /* Declare local variables. */ DECLARE lv_schema_name VARCHAR(64); DECLARE lv_table_name VARCHAR(64); DECLARE lv_constraint_name VARCHAR(64); DECLARE lv_column_count INT UNSIGNED; DECLARE lv_column_name VARCHAR(64); DECLARE lv_column_list VARCHAR(512); DECLARE lv_column_ref_list VARCHAR(64); DECLARE lv_referenced_table_schema VARCHAR(64); DECLARE lv_referenced_table_name VARCHAR(64); DECLARE lv_referenced_column_name VARCHAR(64); DECLARE lv_return_string VARCHAR(768); /* Declare control variable for handler. */ DECLARE fetched INT DEFAULT 0; /* Declare local cursor for foreign key column. */ DECLARE foreign_key_column CURSOR FOR SELECT kcu.column_name , kcu.referenced_table_schema , kcu.referenced_table_name , kcu.referenced_column_name FROM information_schema.key_column_usage kcu WHERE kcu.referenced_table_schema = lv_schema_name AND kcu.table_name = lv_table_name AND kcu.constraint_name = lv_constraint_name ORDER BY kcu.column_name; /* Declare a not found record handler to close a cursor loop. */ DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1; /* Assign parameter values to local variables. */ SET lv_schema_name := pv_schema_name; SET lv_table_name := pv_table_name; SET lv_constraint_name := pv_constraint_name; /* Set the first column value. */ SET lv_column_count := 1; /* Open the nested cursor. */ OPEN foreign_key_column; cursor_foreign_key_column: LOOP /* Fetch a row into the local variables. */ FETCH foreign_key_column INTO lv_column_name , lv_referenced_table_schema , lv_referenced_table_name , lv_referenced_column_name; /* Catch handler for no more rows found from the fetch operation. */ IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF; /* Initialize the column list or add to it. */ IF lv_column_count = 1 THEN SET lv_column_list := lv_column_name; SET lv_column_ref_list := lv_referenced_column_name; /* Increment the counter value. */ SET lv_column_count := lv_column_count + 1; ELSE SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name); SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name); END IF; END LOOP cursor_foreign_key_column; CLOSE foreign_key_column; /* Set the return string to a list of columns. */ SET lv_return_string := CONCAT(lv_column_list,')',' ' ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' ,'(',lv_column_ref_list,');'); RETURN lv_return_string; END;$$

You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):

CREATE TABLE parent ( first_name VARCHAR(20) NOT NULL DEFAULT '' , last_name VARCHAR(20) NOT NULL DEFAULT '' , PRIMARY KEY (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   CREATE TABLE child ( child_name VARCHAR(20) NOT NULL , first_name VARCHAR(20) DEFAULT NULL , last_name VARCHAR(20) DEFAULT NULL , PRIMARY KEY (child_name) , KEY fk_parent(first_name, last_name) , CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name) REFERENCES parent (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You call the storeForeignKeys procedure for the child table with this syntax:

CALL storeForeignKeys('studentdb', 'child');

You call the storeForeignKeys procedure for all tables in a schema with this syntax:

CALL storeForeignKeys('studentdb', null);

While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:

CALL storeForeignKeys(null, null);

You can export the command sequence with the following command to a script file:

SELECT sql_statement INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM command_list;

While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.

Written by maclochlainn

March 17th, 2014 at 11:27 pm

## Hobbyist at Heart

Fifteen years ago when I dove into JavaScript friends called me a hobbyist and said I was wasting my time. They had some basis in what they said because most of my work is server side with the database or operating system. However, they haven’t said that I wasted my time with the advent of NoSQL solutions, like Cassandra and MongoDB; or frameworks like node.js. Now writing JavaScript is mainstream and an invaluable skill set, and they’re trying to catch up. While David Flanagan’s JavaScript: The Definitive Guide is comprehensive, I recommend my students start with David McFarland’s JavaScript & jQuery: The Missing Manual. I also strongly suggest they use jQuery first and write JavaScript when jQuery isn’t suitable.

I really liked David McFarland’s CSS: The Missing Manual when it came out in 2009, and I’ve recommended it for several years. However, he’s got a new version – CSS3: The Missing Manual that came out in late January 2014. It’s an improvement over his first volume and I’d recommend you upgrade if you’re writing, modifying, or maintaining Cascading Style Sheet or if you just want to learn more about CSS.

Fortunately for me, CSS3: The Missing Manual is available through iTunes for Apple users, Naturally, it’s also available on Safari and Kindle formats. As an Apple user, I opted for the iBook format for my iPad Air. Unfortunately, it’s $27.99 as an iBook compared to$15.49 on Kindle, and that almost makes me opt to use the Kindle App. 😉

Written by maclochlainn

March 16th, 2014 at 4:20 pm

Posted in CSS,CSS3,JavaScript,jQuery,JSON

Tagged with , , , ,

## Speaking at UTOUG 2014

with one comment

I’m off shortly to attend and speak at the Utah Oracle User Group’s Spring Training Days 2014, which is held at Salt Lake Community College – Larry H. Miller Campus. My presentation is on writing PL/SQL for Oracle Database 12c. We’ll be covering how you can best anchor PL/SQL cursors to include or exclude hidden columns, and how to work with identifying columns in the scope of multiple table inserts.

I have copy of Oracle Database 12c PL/SQL Programming to give away in my session and John Harper’s session. While the book is also available on Kindle, Mac OS X users can get it as a natively as an iBook on the Apple Store. I’ve got a copy on my iPad and it looks great. What I especially like is the search feature.

Written by maclochlainn

March 11th, 2014 at 1:03 pm

Posted in Oracle,Oracle 12c,UTOUG

Tagged with ,

## Word 2010 – Add Template

You can add a Word Template without a problem until you get to Word 2010 because they’re hidden under the Developer ribbon, which is disabled by default. It’s funny something so widely used would be placed on a ribbon that is disabled by default.

It didn’t come up in the first few Google search pages. I figured that I’d blog it because it took me more than a couple minutes.

You can enable the ribbon with the following steps:

1. Click the File menu choice as shown in the image.

1. Click the Options menu item as shown in the image.

1. Click the Customize Ribbon menu item from the left column.

1. After clicking the Customize Ribbon menu item, the dialog on the right changes as shown below.

1. Click the Developer checkbox to enable the Developer Ribbon that lets you assign a macro template.

1. AFter enabling the Developer Ribbon, it now appears whenever you open or launch Word 2010.

Written by maclochlainn

March 9th, 2014 at 3:35 pm

## Find Type Dependents

Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:

DROP TYPE item_object * ERROR at line 1: ORA-02303: cannot DROP OR REPLACE a TYPE WITH TYPE OR TABLE dependents

They had a great point, so here I’m providing and object table function (object table functions are covered in Chapter 8 of Oracle Database 12c PL/SQL Programming book) that displays an object dependency tree.

Unlike table functions in Microsoft SQL Server or PostgreSQL, Oracle requires that you create the object type before you can return a result set table from a parameter-driven function. Here’s the object type for this object table function:

1 2 3 4 5 6 7 8 9 10 11 -- Create an object type that mimics a record structure. CREATE OR REPLACE TYPE type_tree IS object ( level_id NUMBER , type_name VARCHAR2(30) , object VARCHAR2(10) , parent_type VARCHAR2(30)); /   -- Create a UDT Collection. CREATE OR REPLACE TYPE type_tree_table IS TABLE OF type_tree; /

Now, you can create the object table function that returns an object type dependent tree. This function uses branching recursion because you need to walk the dependency tree. The cursor against the three types of metadata in the database catalog lets us walk the tree. Each object type, and their dependents may have:

• A dependent object type
• A dependent collection, which uses the object type as a base object type.
• A dependent schema function, which uses the object type as a parameter data type or return data type.
• A dependent schema procedure, which uses the object type as a parameter data type.
• A dependent package, which contains a function or procedure using the object type as a parameter data type, or a function using the object type as a return data type.

Here’s the type_dependent function that walks the tree and displays the hierarchical results:

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 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 CREATE OR REPLACE FUNCTION type_dependents ( pv_base_object_type VARCHAR2 , pv_level_id NUMBER DEFAULT 1 , pv_collection TYPE_TREE_TABLE DEFAULT NULL ) RETURN TYPE_TREE_TABLE IS   /* Declare a return data type. */ lv_level_id NUMBER;   /* Declare item type. */ lv_type_name VARCHAR2(30); lv_object_type VARCHAR2(30); lv_parent_type VARCHAR2(30);   /* Declare a collection variable. */ lv_collection TYPE_TREE_TABLE := type_tree_table(); lv_incoming TYPE_TREE_TABLE := type_tree_table();   /* The first part of the cursor finds the dependent type names of complex object types, and the second part of the cursor finds the dependent collection types. Effectively the set operator finds two distinct branches because you may use any base type as an element of a complex object or of a collection. */ CURSOR base_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT (cv_level_id) AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || ut.type_name AS type_name , ut.typecode AS object_type , NULL AS parent_type FROM user_types ut WHERE ut.type_name = cv_base_type;   CURSOR dependent_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uta.type_name AS type_name , ut.typecode AS object_type , ut.type_name AS parent_type FROM user_type_attrs uta INNER JOIN user_types ut ON uta.attr_type_name = ut.type_name WHERE ut.type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uct.type_name AS type_name , CASE WHEN uct.coll_type = 'TABLE' THEN uct.coll_type ELSE 'VARRAY' END AS object_type , ut.type_name AS parent_type FROM user_types ut INNER JOIN user_coll_types uct ON ut.type_name = uct.elem_type_name WHERE uct.elem_type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , CASE WHEN package_name IS NULL THEN LPAD(' ', 2*(cv_level_id - 1)) || ua.object_name ELSE LPAD(' ', 2*(cv_level_id - 1)) || ua.package_name END AS type_name , CASE WHEN package_name IS NULL THEN uo.object_type ELSE 'PACKAGE' END AS object_type , ua.type_name AS parent_type FROM user_arguments ua LEFT JOIN user_objects uo ON ua.package_name = uo.object_name OR ua.object_name = uo.object_name WHERE type_name = cv_base_type ORDER BY object_type;   BEGIN   /* Set the call level indicator. */ lv_level_id := pv_level_id;   /* Check if the collection holds values, which should occur in recursive calls. */ IF pv_collection IS NOT NULL THEN lv_collection := pv_collection; ELSE /* Open the base type and record level 1. */ OPEN base_type (lv_level_id, pv_base_object_type); FETCH base_type INTO lv_level_id , lv_type_name , lv_object_type , lv_parent_type; CLOSE base_type;   /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( lv_level_id , lv_type_name , lv_object_type , lv_parent_type );   /* Increment the type dependency level. */ lv_level_id := lv_level_id + 1;   END IF;   /* Loop through and return records. */ FOR i IN dependent_type(lv_level_id, pv_base_object_type) LOOP   /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( i.level_id , i.type_name , i.object_type , i.parent_type );   /* Recursively call down to dependent types. */ IF i.type_name <> i.parent_type THEN lv_collection := type_dependents(TRIM(i.type_name), (lv_level_id + 1), lv_collection); END IF;   END LOOP;   /* Return 0 for false. */ RETURN lv_collection; END; /

You would query the function with the TABLE function, like this:

COLUMN level_id FORMAT 99999 HEADING "Level|ID #" COLUMN type_name FORMAT A30 HEADING "Type Name" COLUMN object FORMAT A10 HEADING "Object Type" COLUMN parent_type FORMAT A30 HEADING "Parent Type"   SELECT * FROM TABLE(type_dependents('ITEM_OBJECT',1));

It displays the following results based on the sample types created by the original blog post:

 Level ID # Type Name Object Typ Parent Type ------ ------------------------------ ---------- ------------------------ 1 ITEM_OBJECT OBJECT 2 GET_ITEM_OBJECT FUNCTION ITEM_OBJECT 2 IDENTIFIED_OBJECT OBJECT ITEM_OBJECT 2 ITEM_PACKAGE PACKAGE ITEM_OBJECT 2 ITEM_TABLE TABLE ITEM_OBJECT 3 ITEM_ASYNC_TABLE COLLECTION ITEM_TABLE 4 ITEM_LIST TABLE ITEM_ASYNC_TABLE 2 ITEM_VARRAY VARRAY ITEM_OBJECT 3 ITEM_ASYNC_VARRAY COLLECTION ITEM_VARRAY 4 ITEM_ARRAY VARRAY ITEM_ASYNC_VARRAY   10 rows selected.

I hope this helps those working with Oracle Database 10g (de-supported), 11g, or 12c. As a note, you don’t need to drop type dependents in Oracle 12c because it supports type evolution. I cover type evolution in Appendix B of Oracle Database 12c PL/SQL Programming. As always, I hope this helps those trying to determine type dependents in an Oracle database.

Written by maclochlainn

March 8th, 2014 at 10:33 am

## PostgreSQL Install-Windows

A number of folks have asked me to create a post of a PostgreSQL installation on Windows. So, here is my step-by-step PostgreSQL 9.3 installation, and you can go further and learn how to setup your own PostgreSQL database schema.

Install PostgreSQL 9.3 on Windows

These steps walk you through installing PostgreSQL and the the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) packages.

1. The first thing you need to do is launch the PostgreSQL file from download folder.

1. After launching the downloaded file, you see an Open File – Security Warning dialog message. Click the Run button to run the PostgreSQL executable file.

1. The first dialog screen is the Setup – PostgreSQL dialog, which welcomes you and prompts you to start the installtion of PostgreSQL. Click the Next button to continue.

1. The Installation Directory dialog sets the location for the PostgreSQL server. Click the Next button to continue.

1. The Data Directory dialog sets the location for the PostgreSQL server’s data repository. Click the Next button to continue.

1. The first Setup dialog sets the database’s superuser password. Click the Next button to continue.

1. The second Setup dialog sets the database’s listener port, which is Port 5432 by default. Click the Next button to continue.

1. The third Setup dialog sets the database’s default Locale. Click the Next button to continue.

1. The Ready to Install dialog lets you start the installation. Click the Next button to continue.

1. The Installing dialog lets you montor the unpacking of programs for the installation. You need not click any buttons to continue.

1. The Completing the PostgreSQL Setup Wizard dialog advises you that you’ve completed the setup. Checking the Stack Builder check box lets you download and install additional tools, drivers, and applications. Click the Next button to continue.

1. This is Welcome dialog to the Stack Builder. Click the Next button to continue.

1. This is dialog lets you select applications that you would like to install. Click the plus symbol for the Database Drivers option to continue.

1. In the modified dialog, click the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) check boxes. Then, click the Next button to continue.

1. This dialog tells you the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) installed packages. Click the Next button to continue.

1. The Setup pgJDBC dialog welcomes you to the setup wizard. Click the Next button to continue.

1. The Installation Directory dialog lets you set the pgJDBC installation directory. Click the Next button to continue.

1. The Ready to Install dialog lets you install the pgJDBC package. Click the Next button to install it.

1. The Setup dialog confirms you’ve installed the add-on packages. Click the Finish button to complete the installation.

Configure psqlODBC on Windows

These steps walk you through configuring the psqlODBC packages.

1. The Setup dialog helps you configure the psqlODBC package. Click the Next button to install it.

1. The Installation Directory dialog lets you set the psqlODBC installation directory. Click the Next button to continue.

1. The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.

1. The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.

1. The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.

1. The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.

1. The Stack Builder dialog displays a message that everything is installed. Click the Finish button when complete.

Connect to the Default PostgreSQL database

You have two options for connecting to the PostgreSQL database. One uses the GUI pgAdmin III console and the other uses the command line.

Connect through pgAdmin III to the PostgreSQL Database

1. In Windows, navigate to through the Start to the pgAdmin III menu option.

1. After launching pgAdmin III, you’ll see the pgAdmin II console, displayed at left.

1. Right click on the PostgreSQL 9.3 (localhost:5432) item in the Object browser and click the Connect menu choice from the floating menu.

1. Enter the password you chose when installing the PostgreSQL Server, and click the OK button.

Connect through the Command-line to the PostgreSQL Database

These steps show you how to connect through the PostgreSQL Command Line Interface (CLI) – psql. psql is like Oracle’s sqlplus, MySQL’s mysql, and Microsoft SQL Server’s sqlcmd CLI interfaces. By default, the PostgreSQL binary directory is not in your default %PATH% environment variable, so you need to add it. I recommend an environment file because in a test situation you may not want to use it all the time.

Create the following directory for the environment file:

C:\Data\PostgreSQL\env

You can create an environment file with any text editor or do it at the command line, like this:

COPY CON postgresqlenv.bat SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.3\bin;. ^Z

Then, you can source the environment file like this:

C:\> postgresql.bat

Now, you can connect to the default database provided you understand that PostgreSQL Stack Builder set some values that differ from the defaults if you installed it manually. Specifically, they set the default value of the database to postgres and default value of the user to postgres.

That means you can connect with the following, provided you remember the password you used during installation:

C:\> psql -d postgres -U postgres

You should see the following when you connect:

C:\> psql -d postgres -U postgres Password for user postgres: psql (9.3.3) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help.

You can now submit interactive SQL statements or run batch files, like:

postgres=# SELECT 'Hello World!';

It displays:

 ?COLUMN? -------------- Hello World! (1 ROW)

Adding a column alias to the string literal gives a more readable formatted output:

postgres=# SELECT 'Hello World!' AS "String Literal";

It displays:

 String Literal ---------------- Hello World! (1 ROW)

PostgreSQL’s CLI Help

You have the ability to do a lot with the PostgreSQL psql CLI. The general command to discover help is help, and it displays the following:

postgres=# help You are USING psql, the command-line interface TO PostgreSQL. TYPE: \copyright FOR distribution terms \h FOR help WITH SQL commands \? FOR help WITH psql commands \g OR terminate WITH semicolon TO EXECUTE query \q TO quit

You quit PostgreSQL with a \q command:

postgres=# \q

Have fun exploring PostgreSQL. You can click here to learn how to setup your own tablespace, database, and user. As always, I hope this helps those looking to discover how to install and use PostgreSQL.

Written by maclochlainn

March 2nd, 2014 at 7:59 pm