Archive for the ‘pl/sql’ Category
Oracle Stored Procedure
Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.
The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.
Here are the detailed steps, even though there are other blog entries with information on related subjects.
1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.
SQL> CREATE USER student IDENTIFIED BY student; SQL> GRANT CONNECT, resource, CREATE any VIEW TO student; |
2. Create a create_oracle_procedure.sql file with the following contents:
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 | -- Conditionally drop the objects to make this rerunnable. BEGIN FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('A','B')) 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 ('A_SEQ','B_SEQ')) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create the tables and sequences. CREATE TABLE a ( a_id NUMBER CONSTRAINT a_pk PRIMARY KEY , a_text VARCHAR2(12)); CREATE SEQUENCE a_seq; CREATE TABLE b ( b_id NUMBER CONSTRAINT b_pk PRIMARY KEY , a_id NUMBER , b_text CHAR(12) , CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id)); CREATE SEQUENCE b_seq; -- Create a stored procedure with IN-only (default) mode parameters. CREATE OR REPLACE PROCEDURE double_insert ( input_a VARCHAR2, input_b VARCHAR2) IS BEGIN SAVEPOINT starting_point; INSERT INTO a VALUES (a_seq.NEXTVAL, input_a); INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / -- Define a couple local session variables. VARIABLE text1 VARCHAR2(12) VARIABLE text2 VARCHAR2(12) -- Assign values to session variables. BEGIN :text1 := 'This is one.'; :text2 := 'This is two.'; END; / -- Call the local procedure. EXECUTE double_insert(:text1,:text2); -- Select the data set. SELECT * FROM a; SELECT * FROM b; |
3. Quit the session as the system user. You can simply reconnect to the new schema like this:
SQL> CONNECT student/student@orcl |
4. Run the file from the relative directory where you started the sqlplus executable.
SQL> @create_oracle_procedure.sql |
You see the following query results at the end of the script:
A_ID A_TEXT ---------- ------------ 1 This IS one. B_ID A_ID B_TEXT ---------- ---------- ------------ 1 1 This IS two. |
5. Write the following into a call_oracle_procedure.php:
<?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "orcl"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { // Declare two variables for the test procedure call. $val1 = "Hello Hal!"; $val2 = "Hello Dave!"; // Set the call statement, like a SQL statement. $sql = "BEGIN double_insert(:a,:b); END;"; // Prepare the statement and bind the two strings. $stmt = oci_parse($c,$sql); // Bind local variables into PHP statement. oci_bind_by_name($stmt, ":a", $val1); oci_bind_by_name($stmt, ":b", $val2); // Execute it and print success or failure message. if (oci_execute($stmt)) { print "Congrats! You've executed a Oracle stored procedure from PHP!"; } else { print "Sorry, I can't do that Dave..."; } // Free resources. oci_free_statement($stmt); oci_close($c); } ?> |
6. Run the call_oracle_procedure.php from the command line, like this:
php call_oracle_procedure.php |
7. Now you can requery the database to see the inserts made by the PHP program.
SQL> SELECT * FROM a; A_ID A_TEXT ---------- ------------ 1 This IS one. 2 Hello Hal! SQL> SELECT * FROM b; B_ID A_ID B_TEXT ---------- ---------- ------------ 1 1 This IS two. 2 2 Hello Dave! |
There’s now a sample file for both approaches. As always, I hope this helps some folks.
Not quite an invalid function
An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …
Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:
SQL> VARIABLE verified NUMBER SQL> CALL update_contact INTO :verified; |
It failed with the following message:
CALL update_contact INTO :verified * ERROR at line 1: ORA-06576: NOT a valid FUNCTION OR PROCEDURE name |
They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:
SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> BEGIN 2 IF update_contact = 1 THEN 3 DBMS_OUTPUT.put_line('Success!'); 4 ELSE 5 DBMS_OUTPUT.put_line('Failure!'); 6 END IF; 7 END; 8 / |
It returns
Success! |
I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.
Here’s the correct way call the program:
SQL> CALL update_contact() INTO :verified; SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual; |
It prints this to console:
ANSWER
--------
Success! |
The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.
SQL> SELECT update_contact() FROM dual; |
this query raises the following exception:
SELECT update_contact() FROM dual * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4 |
Setup Code Supplement
You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:
-- Add a null allowed column for derived data. ALTER TABLE contact ADD (full_name VARCHAR2(44)); -- Define a function concatenate strings. CREATE OR REPLACE FUNCTION MERGE ( last_name VARCHAR2 , first_name VARCHAR2 , middle_initial VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE IS BEGIN RETURN last_name ||', '||first_name||' '||middle_initial; END; / -- Define a wrapper function around the merge function. CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS ret_val NUMBER := 0; -- The default return value to false or zero. BEGIN UPDATE contact c1 SET c1.full_name = (SELECT MERGE(c2.last_name ,c2.first_name ,c2.middle_initial) FROM contact c2 WHERE c1.rowid = c2.rowid); IF SQL%ROWCOUNT > 0 THEN ret_val := 1; -- This is only reached when 1 or more rows are updated. END IF; RETURN ret_val; END update_contact; / |
Oracle Interval Data Types
I saw an interesting post on INTERVAL YEAR TO MONTH while checking things out today. It struck me as odd, so I thought I’d share a similar sample along with my opinion about how it should be done in a PL/SQL block.
The example is a modification of what I found in a forum. You should see immediately that it’s a bit complex and doesn’t really describe what you should do with any months. Naturally, the example only dealt with years.
DECLARE lv_interval INTERVAL YEAR TO MONTH; lv_end_day DATE := '30-APR-2009'; lv_start_day DATE := '30-APR-1975'; BEGIN lv_interval := TO_CHAR(FLOOR((lv_end_day - lv_start_day)/365.25))||'-00'; DBMS_OUTPUT.put_line(lv_interval); END; / |
I suggest that the better way is the following because it allows for months, which are a bit irregular when it comes to divisors.
DECLARE lv_interval INTERVAL YEAR TO MONTH; lv_end_day DATE := '30-APR-2009'; lv_start_day DATE := '30-JAN-1976'; BEGIN lv_interval := TO_CHAR(EXTRACT(YEAR FROM lv_end_day) - EXTRACT(YEAR FROM lv_start_day)) ||'-'|| TO_CHAR(EXTRACT(MONTH FROM lv_end_day) - EXTRACT(MONTH FROM lv_start_day)); DBMS_OUTPUT.put_line(lv_interval); END; / |
Let me know if you’ve another alternative that you prefer.
Oracle XE Install
Some folks asked me to post Oracle Database 10g Express Edition step-by-step installation and how to setup an initial user/schema. There’s not much difference between the Windows and Linux installation but this one is the Windows XP installation.
Here are the eight steps required to run the MSI program:
1. Launch the MSI icon from your Windows Explorer:
2. Click the run button to extracing the zip file:
3. Click the Next button to run the Oracle Database 10g Expression Edition install wizard:
4. Accept the license agreement and click the Next button:
5. You can change the default installation base folder by clicking the Browse button, but why bother. Click the Next button to proceed.
6. This is the screen where you enter the privileged user account for the Oracle database. Make sure you’ll remember the password you enter. When you’ve entered it twice, click the Next button to continue the installation.
7. The Summary dialog shows you the ports for the installation. If you want to access this machine externally from the local host, you should open these ports for TCP transactions in your firewall. Click the Install button to proceed.
8. The last screen tells you that the InstallShield Wizard is complete. Make sure the Launch the Database homepage check box checked and click the Finish button to complete the installation.
After you’ve installed the database, you should immediately create a schema where you’ll work. The next steps show you how to create your own user/schema in the Oracle Database 10g XE instance.
1. The Database homepage provides you with a web login to the database. The only configured user at this point is the privileged user, which is known as the system user. The password to this account is the same as the one you entered during the product installation.
2. After you sign on to the database, you’ll be on the following console page. Click the Administration button on the far left.
3. The middle Database Users button in the web page allows you to create new database users. Click the Database Users image to proceed.
4. The Manage Database Users console lets you create or manage existing users. Click the Create button to create a new user.
5. The Create Database User console lets you enter a user name and password plus lets you grant necessary permissions to the new user. The entries on this form create a STUDENT user/schema with superuser responsibilities. The DBA role grants those superuser privileges. You click the Create button to setup a new user.
6. After you create the user, you’ll see the following screen. You can now click the Logout link to exit the application. Then, you can logon again as the STUDENT user.
I hope this meets the needs of new users.
T-SQL Hierarchical Query
Playing around with Microsoft SQL Server 2008 Express edition, I’ve sorted through a bunch of tidbits. One that I thought was interesting, is how to perform a recursive or hierarchical query. This describes how you can perform the magic.
The official name of the WITH clause in Oracle’s lexicon (otherwise known as Oraclese) is a subquery factoring clause. You can find more on that in this earlier blog post. Microsoft has a different name for the WITH clause. They call it a Common Table Expression or CTE.
You perform recursive queries in Microsoft SQL Server 2008 by leveraging CTEs. I’ve modified the setup code from that earlier blog post to run in SQL Server 2008. You’ll find it at the bottom of this blog post.
Unless you want to write your own C# (.NET is the politically correct lingo) equivalent to Oracle’s SQL*Plus, you’ll need to run this script in the SQL Server Management Studio. Actually, you can use Microsoft SQL Server 2008’s command-line utility, which is called sqlcmd.exe but it is much less robust than SQL*Plus. In the Management Studio, you click File, then Open, and File… to load the file for execution, and then click the Execute button. You need to be careful you don’t click the Debug button, which is the green arrow to the right of the Execute button.
This is the magic query in the illustration. You can also find it in the source code. At the end of the day, I’m hard pressed to understand why they’d use a UNION ALL to support recursion.
The top-most CTE, or subquery factoring clause, simply joins the ORGANIZATION_NAME to the ORG_PARENT_ID and ORG_CHILD_ID columns to provide a single working source. The second CTE performs the recursion. The top-query sets the starting row, and the second query recursively navigates the tree. After all children are found, the first query moves to the next element in the table and recursively searches for its children.
You should note that the CTE self-references itself from inside the second query. Then, the external query (the non-CTE query) returns the results by querying the same CTE.
This logic behaves more like a nested loop, and actually fails to move down branches of the tree like a recursive program. Otherwise line 19 would be line 14 in the output. You could write another CTE to fix this shortfall, thereby mirroring a true recursive behavior, or you can write a stored procedure.
The illustrated query outputs the following hierarchical relationship, which navigates down the hierarchical tree:
You can also go up any branch of the tree by changing some of the logic. You’ll find the query to navigate up the tree as the second query in the setup script at the end of the blog. It renders the following output:
The blog will be updated if I discover the equivalent to the LEVEL in Oracle’s self-referencing semantics. If you know it, please share it with everybody.
Setup Script
Microsoft SQL Server 2008 Join Script
USE student; BEGIN TRAN; -- Conditionally drop tables when they exist. IF OBJECT_ID('dbo.ORGANIZATION','U') IS NOT NULL DROP TABLE dbo.ORGANIZATION; IF OBJECT_ID('dbo.ORG_STRUCTURE','U') IS NOT NULL DROP TABLE dbo.ORG_STRUCTURE; -- Create the organization table. CREATE TABLE ORGANIZATION ( organization_id INT , organization_name VARCHAR(10)); -- Seed the organizations. INSERT INTO dbo.ORGANIZATION VALUES (1,'One'), (2,'Two'), (3,'Three'), (4,'Four'), (5,'Five') ,(6,'Six'), (7,'Seven'), (8,'Eight'), (9,'Nine'), (10,'Ten') ,(11,'Eleven'), (12,'Twelve'), (13,'Thirteen'), (14,'Fourteen'), (15,'Fifteen') ,(16,'Sixteen'), (17,'Seventeen'), (18,'Eighteen'), (19,'Nineteen'), (20,'Twenty'); -- Create the organization structure table that holds the recursive key. CREATE TABLE org_structure ( org_structure_id INT , org_parent_id INT , org_child_id INT ); -- Seed the organization structures. INSERT INTO org_structure VALUES ( 1, 0, 1),( 1, 1, 2),( 1, 1, 3),( 1, 1, 4),( 1, 2, 5) ,( 1, 2, 6),( 1, 3, 7),( 1, 3, 8),( 1, 4, 9),( 1, 4,10) ,( 1, 5,11),( 1, 5,12),( 1, 6,13),( 1, 6,14),( 1, 7,15) ,( 1, 8,16),( 1, 8,17),( 1, 9,18),( 1, 9,19),( 1,14,20); COMMIT TRAN; -- Navigating down the tree from the root node. WITH org_name AS (SELECT os.org_parent_id AS org_parent_id , o1.organization_name AS org_parent_name , os.org_child_id AS org_child_id , o2.organization_name AS org_child_name FROM dbo.organization o1 RIGHT JOIN dbo.org_structure os ON o1.organization_id = os.org_parent_id RIGHT JOIN dbo.organization o2 ON o2.organization_id = os.org_child_id) , jn AS (SELECT org_parent_id, org_parent_name , org_child_id, org_child_name FROM org_name WHERE org_parent_id = 1 UNION ALL SELECT c.org_parent_id, c.org_parent_name , c.org_child_id, c.org_child_name FROM jn AS p JOIN org_name AS c ON c.org_parent_id = p.org_child_id) SELECT jn.org_parent_id, jn.org_parent_name , jn.org_child_id, jn.org_child_name FROM jn ORDER BY 1; -- Navigating up the tree from the 20th leaf-node child. WITH org_name AS (SELECT os.org_parent_id AS org_parent_id , o1.organization_name AS org_parent_name , os.org_child_id AS org_child_id , o2.organization_name AS org_child_name FROM dbo.organization o1 RIGHT JOIN dbo.org_structure os ON o1.organization_id = os.org_parent_id RIGHT JOIN dbo.organization o2 ON o2.organization_id = os.org_child_id) , jn AS (SELECT org_parent_id, org_parent_name , org_child_id, org_child_name FROM org_name WHERE org_child_id = 20 UNION ALL SELECT c.org_parent_id, c.org_parent_name , c.org_child_id, c.org_child_name FROM jn AS p JOIN org_name AS c ON c.org_child_id = p.org_parent_id) SELECT jn.org_parent_id, jn.org_parent_name , jn.org_child_id, jn.org_child_name FROM jn ORDER BY 1 DESC; |
Object Record Collections
It must have been disgust with learning that a Result Cache function couldn’t use an object type that made me zone on showing how to use an object type as the return type of a PL/SQL table function. The nice thing about this approach, as pointed out by Gary Myer’s comment on another blog post, is that it doesn’t require a pipelined function to translate it from PL/SQL to SQL scope.
The first step is to create an object type without a return type of SELF, which Oracle elected as its equivalent to this for some unknown reason. A user-defined type (UDT) defined without a return type, returns the record structure of the object, but as mentioned it is disallowed in result cache functions. After you define the base type, you create a collection of the base UDT. Then, you can use the UDT as a SQL return type in your code, like this:
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 | CREATE OR REPLACE FUNCTION get_common_lookup_object_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN common_lookup_object_table IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Declare a package collection data type as a SQL scope table return type. list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table(); BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.extend; list(counter) := common_lookup_object(i.common_lookup_id ,i.common_lookup_type ,i.common_lookup_meaning); counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_object_table; / |
You can then query it in SQL like this:
COLUMN common_lookup_id FORMAT 9999 HEADING "ID" COLUMN common_lookup_type FORMAT A16 HEADING "Lookup Type" COLUMN common_lookup_meaning FORMAT A30 HEADING "Lookup Meaning" SELECT * FROM TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE')); |
This depends on the same sample code that I use elsewhere on the blog. You can download it from McGraw-Hill’s web site. You can also find a complete and re-runnable script by clicking on the down arrow below.
Code Script ↓
BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name = 'GET_COMMON_LOOKUP_OBJECT_TABLE') LOOP EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END LOOP; FOR i IN (SELECT type_name FROM user_types WHERE type_name = 'COMMON_LOOKUP_OBJECT_TABLE') LOOP EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name; END LOOP; FOR i IN (SELECT type_name FROM user_types WHERE type_name = 'COMMON_LOOKUP_OBJECT') LOOP EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name; END LOOP; END; / CREATE OR REPLACE TYPE common_lookup_object IS OBJECT ( common_lookup_id NUMBER , common_lookup_type VARCHAR2(30) , common_lookup_meaning VARCHAR2(255)); / CREATE OR REPLACE TYPE common_lookup_object_table IS TABLE OF common_lookup_object; / CREATE OR REPLACE FUNCTION get_common_lookup_object_table ( table_name VARCHAR2 , column_name VARCHAR2 ) RETURN common_lookup_object_table IS -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = UPPER(table_name_in) AND common_lookup_column = UPPER(table_column_name_in); -- Declare a counter variable. counter INTEGER := 1; -- Declare a package collection data type as a SQL scope table return type. list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table(); BEGIN -- Assign the cursor return values to a record collection. FOR i IN c(table_name, column_name) LOOP list.extend; list(counter) := common_lookup_object(i.common_lookup_id ,i.common_lookup_type ,i.common_lookup_meaning); counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_object_table; / TTITLE OFF COLUMN common_lookup_id FORMAT 9999 HEADING "ID" COLUMN common_lookup_type FORMAT A16 HEADING "Lookup Type" COLUMN common_lookup_meaning FORMAT A30 HEADING "Lookup Meaning" SELECT * FROM TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE')); |
Describe User Record Types
Gary Myers made a comment on the blog that got me thinking about how to look up user defined types (UDTs). Like those UDTs that you define to leverage pipelined functions and procedures. It became more interesting while considering how Oracle Object Types act as SQL record types. At least, that’s their default behavior when you don’t qualify a return type of self (that’s this in Oracle objects for those who write in any other object-oriented programming language).
The following query creates a view of data types in your user schema. It is fairly straightforward and written to let you deploy the view in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.
CREATE OR REPLACE VIEW schema_types AS SELECT ut.type_name AS type_name , uta.attr_no AS position_id , uta.attr_name AS attribute_name , DECODE(uta.attr_type_name , 'BFILE' ,'BINARY FILE LOB' , 'BINARY_FLOAT' ,uta.attr_type_name , 'BINARY_DOUBLE',uta.attr_type_name , 'BLOB' ,uta.attr_type_name , 'CLOB' ,uta.attr_type_name , 'CHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'DATE' ,uta.attr_type_name , 'FLOAT' ,uta.attr_type_name , 'LONG RAW' ,uta.attr_type_name , 'NCHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'NVARCHAR2' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'NUMBER' ,DECODE(NVL(uta.precision||uta.scale,0) , 0,uta.attr_type_name , DECODE(NVL(uta.scale,0),0 , uta.attr_type_name||'('||uta.precision||')' , uta.attr_type_name||'('||uta.precision||','|| uta.scale||')')) , 'RAW' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'VARCHAR' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'VARCHAR2' ,DECODE(NVL(uta.length,0),0,uta.attr_type_name , uta.attr_type_name||'('||uta.length||')') , 'TIMESTAMP' , uta.attr_type_name,uta.attr_type_name) AS attr_type_name FROM user_types ut, user_type_attrs uta WHERE ut.type_name = uta.type_name ORDER BY ut.type_name, uta.attr_no; |
You can query and format the view as follows:
CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES TTITLE OFF SET ECHO ON SET FEEDBACK OFF SET NULL '' SET PAGESIZE 999 SET PAUSE OFF SET TERM ON SET TIME OFF SET TIMING OFF SET VERIFY OFF ACCEPT INPUT PROMPT "Enter type name: " SET HEADING ON TTITLE LEFT o1 SKIP 1 - '--------------------------------------------------------' SKIP 1 CLEAR COLUMNS CLEAR BREAKS BREAK ON REPORT BREAK ON c1 SKIP PAGE COL c1 NEW_VALUE o1 NOPRINT COL c2 FORMAT 999 HEADING "ID" COL c3 FORMAT A32 HEADING "Attribute Name" COL c4 FORMAT A33 HEADING "Attribute Type" SELECT st.type_name c1 , st.position_id c2 , st.attribute_name c3 , st.attr_type_name c4 FROM schema_types st WHERE st.type_name LIKE UPPER('&input')||'%' ORDER BY st.type_name , st.position_id; |
Here’s a sample output for an object type named common_lookup_object:
COMMON_LOOKUP_OBJECT -------------------------------------------------------- ID Attribute Name Attribute TYPE ---- -------------------------------- ------------------ 1 COMMON_LOOKUP_ID NUMBER 2 COMMON_LOOKUP_TYPE VARCHAR2(30) 3 COMMON_LOOKUP_MEANING VARCHAR2(255) |
It certainly makes the point that a named data type is most convenient. I’m still working through the metadata to find how to link those meaningless type names back to meaningful package specifications. If you know, let me know in a comment. Hope this helps somebody.
Beats a reference cursor
You can’t beat play’n around with the technology. It seems that each time I experiment with something to answer a question, I discover new stuff. So, I really appreciate that Cindy Conlin asked me to net out why a PL/SQL Pipelined Table function existed at UTOUG Training Days 2009.
I found that Java and PHP have a great friend in Pipelined Table functions because when you wrap them, you can simplify your code. While a reference cursor lets you return the product of a bulk operation, it requires two hooks into the database. One for the session connection and another for the connection to the system reference cursor work area. While this was a marvelous feature of the OCI8 library, which I duly noted in my Oracle Database 10g Express Edition PHP Web Programming book, there’s a better way.
The better way is a Pipelined Table function because you can query it like you would a normal table or view. Well, not exactly but the difference involves the TABLE function, and it is really trivial.
When you call a Pipelined Table function, you only need to manage a single hook into the database. That hook is for the session connection. You can find a full (really quite detailed) treatment of Table and Pipelined Table functions in this blog page. Building on that blog page, here’s a simple PHP program that demonstrates the power of leveraging the SQL context provided by a Pipelined Table function.
<?php // Connect to the database. if ($c = @oci_connect("plsql","plsql","orcl")) { // Parse a query to a resource statement. $s = oci_parse($c,"SELECT * FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE'))"); // Execute query without an implicit commit. oci_execute($s,OCI_DEFAULT); // Open the HTML table. print '<table border="1" cellspacing="0" cellpadding="3">'; // Read fetched headers. print '<tr>'; for ($i = 1;$i <= oci_num_fields($s);$i++) print '<td class="e">'.oci_field_name($s,$i).'</td>'; print '</tr>'; // Read fetched data. while (oci_fetch($s)) { // Print open and close HTML row tags and columns data. print '<tr>'; for ($i = 1;$i <= oci_num_fields($s);$i++) print '<td class="v">'.oci_result($s,$i).'</td>'; print '</tr>'; } // Close the HTML table. print '</table>'; // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } ?> |
You’ll notice that all the information that is expected from a query against a table or view is also available from the result of Pipelined Table function. That’s because the Pipeline Table function actually places the internal record structure of a PL/SQL collection into the SQL context along with the data.
This sample PHP program produces the following XHTML output:
| COMMON_LOOKUP_ID | COMMON_LOOKUP_TYPE | COMMON_LOOKUP_MEANING |
| 1013 | DVD_FULL_SCREEN | DVD: Full Screen |
| 1014 | DVD_WIDE_SCREEN | DVD: Wide Screen |
| 1015 | GAMECUBE | Nintendo GameCube |
| 1016 | PLAYSTATION2 | PlayStation2 |
| 1019 | VHS_DOUBLE_TAPE | VHS: Double Tape |
| 1018 | VHS_SINGLE_TAPE | VHS: Single Tape |
| 1017 | XBOX | XBOX |
Naturally, you can parameterize your PHP program and add bind variables to make this more dynamic. An example of parameterizing the call to a Pipelined Function is provided in the next program example.
You would use the following URL to call the dynamic PHP program:
http://mclaughlin11g/GetCommonLookup.php?table=ITEM&column=ITEM_TYPE |
The working PHP program code is:
<?php // Declare input variables. (isset($_GET['table'])) ? $table = $_GET['table'] : $table = "ITEM"; (isset($_GET['column'])) ? $column = $_GET['column'] : $column = 'ITEM_TYPE'; // Connect to the database. if ($c = @oci_connect("plsql","plsql","orcl")) { // Parse a query to a resource statement. // Don't use table and column because they're undocumented reserved words in the OCI8. $s = oci_parse($c,"SELECT * FROM TABLE(get_common_lookup_plsql_table(:itable,:icolumn))"); // Bind a variable into the resource statement. oci_bind_by_name($s,":itable",$table,-1,SQLT_CHR); oci_bind_by_name($s,":icolumn",$column,-1,SQLT_CHR); // Execute query without an implicit commit. oci_execute($s,OCI_DEFAULT); // Open the HTML table. print '<table border="1" cellspacing="0" cellpadding="3">'; // Read fetched headers. print '<tr>'; for ($i = 1;$i <= oci_num_fields($s);$i++) print '<td class="e">'.oci_field_name($s,$i).'</td>'; print '</tr>'; // Read fetched data. while (oci_fetch($s)) { // Print open and close HTML row tags and columns data. print '<tr>'; for ($i = 1;$i <= oci_num_fields($s);$i++) print '<td class="v">'.oci_result($s,$i).'</td>'; print '</tr>'; } // Close the HTML table. print '</table>'; // Disconnect from database. oci_close($c); } else { // Assign the OCI error and format double and single quotes. $errorMessage = oci_error(); print htmlentities($errorMessage['message'])."<br />"; } ?> |
You may note that the parameter values (placeholders or bind variables inside the SQL statement) are prefaced with an i. That’s because TABLE and COLUMN are restricted key words in the context of OCI8, and their use triggers an ORA-01036 exception.
This makes PHP more independent of the OCI8 library and easy to cross port to other databases if that’s a requirement. Hope this helps some folks.
Pipelined function update
When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. The question asked was: “What’s the primary purpose for pipelined table functions?”
My answer is: The primary purpose of a pipelined table function lets you retrieve PL/SQL record collection structures in a SQL context.
If there’s another reason that I missed, please let me know. It took a couple days to expand the older post to be more complete.
Kindle on the iPhone
I played around with a friend’s Kindle and really had to wonder why would people buy one of them. Since my books sell an electronic edition on Kindle, I hoped for a better solution.
Great news today, a better physical technology has arrived. You can now download a Kindle application for your iPhone! As a big iPhone fan, this is awesome. Naturally, I was curious how my last book looked.
I downloaded the sample chapter and it looked great on the iPhone. The images are well rendered and clear on the screen. I’m probably going to have to buy an e-copy (ouch) because they don’t provide authors with electronic copies.
Thanks a bunch to the team who ported it to the iPhone. Great job!
Subscribe via RSS