Archive for March, 2014
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.
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.
Deprecated mysqli Functions
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_parammysqli_bind_resultmysqli_client_encodingmysqli_fetchmysqli_param_countmysqli_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.
Capture MySQL Foreign Keys
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:
1 2 3 4 5 6 7 8 9 10 11 | 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; |
In MySQL 8 forward, you must make a case sensitive comparison, like:
9 | WHERE tc.constraint_type = 'FOREIGN KEY' |
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.
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. 😉
Speaking at UTOUG 2014
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.
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:
- Click the File menu choice as shown in the image.
- Click the Options menu item as shown in the image.
- Click the Customize Ribbon menu item from the left column.
- After clicking the Customize Ribbon menu item, the dialog on the right changes as shown below.
- Click the Developer checkbox to enable the Developer Ribbon that lets you assign a macro template.
- AFter enabling the Developer Ribbon, it now appears whenever you open or launch Word 2010.
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.
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.
Download PostgreSQL Database
You can start the download of PostgreSQL from this site. It leads you to the EnterpriseDB site, where you can download the prepackaged software.
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.
- The first thing you need to do is launch the PostgreSQL file from download folder.
- After launching the downloaded file, you see an Open File – Security Warning dialog message. Click the Run button to run the PostgreSQL executable file.
- 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.
- The Installation Directory dialog sets the location for the PostgreSQL server. Click the Next button to continue.
- The Data Directory dialog sets the location for the PostgreSQL server’s data repository. Click the Next button to continue.
- The first Setup dialog sets the database’s superuser password. Click the Next button to continue.
- The second Setup dialog sets the database’s listener port, which is Port
5432by default. Click the Next button to continue.
- The third Setup dialog sets the database’s default Locale. Click the Next button to continue.
- The Ready to Install dialog lets you start the installation. Click the Next button to continue.
- The Installing dialog lets you montor the unpacking of programs for the installation. You need not click any buttons to continue.
- 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.
- This is Welcome dialog to the Stack Builder. Click the Next button to continue.
- This is dialog lets you select applications that you would like to install. Click the plus symbol for the Database Drivers option to continue.
- 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.
- This dialog tells you the pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit) installed packages. Click the Next button to continue.
- This dialog lays a downloading progress bar while loading the additional packages. You don’t need to do anything to continue.
- This dialog lays a downloading progress bar while loading the additional packages. Click the Next button to continue.
- The Setup pgJDBC dialog welcomes you to the setup wizard. Click the Next button to continue.
- The Installation Directory dialog lets you set the pgJDBC installation directory. Click the Next button to continue.
- The Ready to Install dialog lets you install the pgJDBC package. Click the Next button to install it.
- 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.
- The Setup dialog helps you configure the psqlODBC package. Click the Next button to install it.
- The Installation Directory dialog lets you set the psqlODBC installation directory. Click the Next button to continue.
- The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.
- The Ready to Install dialog lets you install the psqlODBC package. Click the Next button to install it.
- The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.
- The Installing psqlODBC dialog displays a dialog while installing the psqlODBC package(s). You need not click anything, the dialog dismisses itself when complete.
- 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
- In Windows, navigate to through the Start to the pgAdmin III menu option.
- After launching pgAdmin III, you’ll see the pgAdmin II console, displayed at left.
- Right click on the PostgreSQL 9.3 (localhost:5432) item in the Object browser and click the Connect menu choice from the floating menu.
- 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 |
You can find more help with the \HELP or the \? commands:
\HELP |
It displays the following:
Available help: ABORT CLUSTER DEALLOCATE END ALTER AGGREGATE COMMENT DECLARE EXECUTE ALTER COLLATION COMMIT DELETE EXPLAIN ALTER CONVERSION COMMIT PREPARED DISCARD FETCH ALTER DATABASE COPY DO GRANT ALTER DEFAULT PRIVILEGES CREATE AGGREGATE DROP AGGREGATE INSERT ALTER DOMAIN CREATE CAST DROP CAST LISTEN ALTER EVENT TRIGGER CREATE COLLATION DROP COLLATION LOAD ALTER EXTENSION CREATE CONVERSION DROP CONVERSION LOCK ALTER FOREIGN DATA WRAPPER CREATE DATABASE DROP DATABASE MOVE ALTER FOREIGN TABLE CREATE DOMAIN DROP DOMAIN NOTIFY ALTER FUNCTION CREATE EVENT TRIGGER DROP EVENT TRIGGER PREPARE ALTER GROUP CREATE EXTENSION DROP EXTENSION PREPARE TRANSACTION ALTER INDEX CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER REASSIGN OWNED ALTER LANGUAGE CREATE FOREIGN TABLE DROP FOREIGN TABLE REFRESH MATERIALIZED VIEW ALTER LARGE OBJECT CREATE FUNCTION DROP FUNCTION REINDEX ALTER MATERIALIZED VIEW CREATE GROUP DROP GROUP RELEASE SAVEPOINT ALTER OPERATOR CREATE INDEX DROP INDEX RESET ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE ALTER OPERATOR FAMILY CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW ROLLBACK ALTER ROLE CREATE OPERATOR DROP OPERATOR ROLLBACK PREPARED ALTER RULE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK TO SAVEPOINT ALTER OPERATOR CREATE INDEX DROP INDEX RESET ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE ALTER OPERATOR FAMILY CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW ROLLBACK ALTER ROLE CREATE OPERATOR DROP OPERATOR ROLLBACK PREPARED ALTER RULE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK TO SAVEPOINT ALTER SCHEMA CREATE OPERATOR FAMILY DROP OPERATOR FAMILY SAVEPOINT ALTER SEQUENCE CREATE ROLE DROP OWNED SECURITY LABEL ALTER SERVER CREATE RULE DROP ROLE SELECT ALTER TABLE CREATE SCHEMA DROP RULE SELECT INTO ALTER TABLESPACE CREATE SEQUENCE DROP SCHEMA SET ALTER TEXT SEARCH CONFIGURATION CREATE SERVER DROP SEQUENCE SET CONSTRAINTS ALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP SERVER SET ROLE ALTER TEXT SEARCH PARSER CREATE TABLE AS DROP TABLE SET SESSION AUTHORIZATION ALTER TEXT SEARCH TEMPLATE CREATE TABLESPACE DROP TABLESPACE SET TRANSACTION ALTER TRIGGER CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH CONFIGURATION SHOW ALTER TYPE CREATE TEXT SEARCH DICTIONARY DROP TEXT SEARCH DICTIONARY START TRANSACTION ALTER USER CREATE TEXT SEARCH PARSER DROP TEXT SEARCH PARSER TABLE ALTER USER MAPPING CREATE TEXT SEARCH TEMPLATE DROP TEXT SEARCH TEMPLATE TRUNCATE ALTER VIEW CREATE TRIGGER DROP TRIGGER UNLISTEN ANALYZE CREATE TYPE DROP TYPE UPDATE BEGIN CREATE USER DROP USER VACUUM CHECKPOINT CREATE USER MAPPING DROP USER MAPPING VALUES CLOSE CREATE VIEW DROP VIEW WITH |
You can find more help with the \HELP or the \? commands:
\? |
It displays the following:
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\gset [PREFIX] execute query and store results in psql variables
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\watch [SEC] execute query every SEC seconds
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "studentdb")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations |
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.
Subscribe via RSS