Posts Tagged ‘pl/sql’
Synchronizing file deletion with transaction control
I finally got back to synchronizing file deletion with transaction control. You’ll need some code from an earlier blog page. The reference to that page is the new page referenced below.
Probably the largest disappointment was discovering the details of global temporary tables. A commit on a DML statement against another table doesn’t trigger a DELETE event on the temporary table. This means a trigger built on a global temporary table doesn’t fire unless you delete from the temporary table. If they did, you could commit a change in one table and trigger another event through the temporary table. That would be a great feature.
Code for my Oracle Database 11g PL/SQL Programming
Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.
Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.
Reversing direction in a PL/SQL range for-loop
Have you ever wondered how to decrement index values in a PL/SQL range for-loop. You’ve probably heard that you can’t. That’s part true and part false.
The true part is two fold. First, range for-loops move forward from a starting low value to high value. Second, the loop exits immediately when your starting value is high and ending value low. If you use the REVERSE keyword, the loop moves backward from an ending point to the starting point. This happens because a PL/SQL range for-loop is a guard on entry loop. The starting value is compared against the ending value and exits when the starting value is greater than the ending value.
The false part happens when you want to traverse a collection backward. Oracle collections are varrays, tables, or associative arrays. Associative arrays are also known as PL/SQL tables. You overcome the ascending only limitation by using the REVERSE keyword or leveraging the Oracle Collection API library functions.
This code works when the index is numeric:
DECLARE
TYPE collection IS TABLE OF VARCHAR2(20);
fellowship COLLECTION := collection('Frodo','Gandalf','Aragorn');
BEGIN
-- Increasing iterator values.
FOR i IN REVERSE 1..fellowship.COUNT LOOP
dbms_output.put('Iterator ['||i||'] ');
dbms_output.put('Value ['||fellowship(i)||']');
dbms_output.new_line();
END LOOP;
END;
/
This code works when the index is a string in an associative array:
DECLARE
current VARCHAR2(8);
element VARCHAR2(20);
TYPE collection IS TABLE OF VARCHAR2(20)
INDEX BY VARCHAR2(8);
fellowship COLLECTION;
BEGIN
-- Assign character indexed values.
fellowship('Hobbit') := 'Frodo';
fellowship('Wizard') := 'Gandalf';
fellowship('Human') := 'Aragorn';
-- Increasing iterator values.
FOR i IN 1..fellowship.COUNT LOOP
IF i = 1 THEN -- Set starting point and assign value.
current := fellowship.LAST;
element := fellowship(current);
ELSE -- Change value until you read the first element.
IF fellowship.PRIOR(current) IS NOT NULL THEN
current := fellowship.PRIOR(current);
element := fellowship(current);
ELSE -- Exit when all elements read.
EXIT;
END IF;
END IF;
dbms_output.put('Iterator ['||i||'] ');
dbms_output.put('Index ['||current||'] ');
dbms_output.put('Value ['||element||']');
dbms_output.new_line();
END LOOP;
END;
/
Chris Neumüller suggested another example with a WHILE loop:
DECLARE
TYPE collection IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(8);
fellowship COLLECTION;
current VARCHAR2(20);
BEGIN
-- Assign character indexed values.
fellowship('Hobbit') := 'Frodo';
fellowship('Wizard') := 'Gandalf';
fellowship('Human') := 'Aragorn';
-- Increasing iterator values.
IF fellowship.COUNT > 0 THEN
current := fellowship.last;
WHILE current IS NOT NULL LOOP
dbms_output.put('Iterator ['||current||'] ');
dbms_output.put('Value ['||fellowship(current)||'] ');
dbms_output.new_line();
current := fellowship.prior(current);
END LOOP;
END IF;
END;
/
While the former works, you should consider changing the cursor that populates the collection by adding a descending ORDER BY clause when indexing with a string. The algorithm is a bit more work but it also supports sparsely populated indexes and string-indexed associative arrays. All you need do is swap the LAST for FIRST and PRIOR for NEXT to go forward through a sparsely populated index.
UTOUG web site updates links
There were two broken links in earlier blogs because UTOUG moved their presentation download repository. I’ve updated those links for my Utah Oracle User Group Training Days 2008 presentations. You can also uses these new links: PL/SQL 11g New Features and an Oracle LOBs. You can learn more about UTOUG and prior presentations at http://www.utoug.org, including Tom Kyte’s Materialized Views presentation from the same 2008 spring conference.
PHP code to read a PL/SQL reference cursor
The code is updated and copiable on the new blog.
The following demonstrates how to read a PL/SQL reference cursor in a PHP program. The reference cursor function is defined in the Pipelined Functions & PL/SQL Tables blog page. I’ve commented it to the hilt for those new to PHP.
The reference cursor maintains a separate connection to the database to access the reference cursor. You also use the oci_fetch_assoc() function to get the data. That strip_special_characters() function lets you format your call to the PL/SQL program and remove non-parsing line returns and tabs before running the oci_parse() function.
<?php
// Return successful attempt to connect to the database.
if ($c = @oci_connect("plsql","plsql","orcl"))
{
// Declare input variables.
(isset($_GET['table'])) ? $table = (int) $_GET['table'] : $table = 'ITEM';
(isset($_GET['column'])) ? $column = (int) $_GET['column'] : $column = 'ITEM_TYPE';
// Declare a PL/SQL execution command.
$stmt = "BEGIN
:return_cursor := get_common_cursor(:table,:column);
END;";
// Strip special characters to avoid ORA-06550 and PLS-00103 errors.
$stmt = strip_special_characters($stmt);
// Parse a query through the connection.
$s = oci_parse($c,$stmt);
// Declare a return cursor for the connection.
$rc = oci_new_cursor($c);
// Bind PHP variables to the OCI input or in mode variables.
oci_bind_by_name($s,':table',$table);
oci_bind_by_name($s,':column',$column);
// Bind PHP variables to the OCI output or in/out mode variable.
oci_bind_by_name($s,':return_cursor',$rc,-1,OCI_B_CURSOR);
// Execute the PL/SQL statement & reference cursor.
oci_execute($s);
oci_execute($rc);
// Print the table header with known labels.
print '<table border="1" cellpadding="3" cellspacing="0">';
// Set dynamic labels control variable true.
$label = true;
// Read the contents of the reference cursor.
while($row = oci_fetch_assoc($rc))
{
// Declare header and data variables.
$header = "";
$data = "";
// Read the reference cursor into a table.
foreach ($row as $name => $column)
{
// Capture labels for the first row.
if ($label)
{
$header .= '<td class="e">'.$name.'</td>';
$data .= '<td class="v">'.$column.'</td>';
}
else
$data .= '<td class=v>'.$column.'</td>';
}
// Print the header row once.
if ($label)
{
print '<tr>'.$header.'</tr>';
$label = !$label;
}
// Print the data rows.
print '<tr>'.$data.'</tr>';
}
// Print the HTML table close.
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 />";
}
// Strip special characters, like carriage or line returns and tabs.
function strip_special_characters($str)
{
$out = "";
for ($i = 0;$i < strlen($str);$i++)
if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
(ord($str[$i]) != 13))
$out .= $str[$i];
// Return character only strings.
return $out;
}
?>
Entering a colon in an NDS statement
An updated version of this blog is on my newer site here.
Somebody asked me how you include a colon in a Native Dynamic SQL (NDS) statement when it’s not related to a placeholder. A colon without a placeholder raises an ORA-00947 error when parsing the statement. The error means you’ve failed to submit enough bind variables. You can substitute a CHR(58) where you need to insert the standalone colon. The NDS or DBMS_SQL parsing phase ignores a CHR(58), which translates during actual SQL statement parsing as a colon.
Let’s say you want to insert a column value with an ASIN (Amazon Standard Identification Number) code in the format: ASIN: B000VBJEEG
Using NDS, you have two choices. You can let the entry person type the full string and pass that string as a bind variable, or you can substitute CHR(58) for the colon and enter only the ASIN code. The example (Oracle Database 11g PL/SQL Programming, pp. 386-387) implements the latter:
CREATE OR REPLACE PROCEDURE insert_item
( asin VARCHAR2
, item_type VARCHAR2
, item_title VARCHAR2
, item_subtitle VARCHAR2 := ''
, rating VARCHAR2
, agency VARCHAR2
, release_date DATE ) IS
stmt VARCHAR2(2000);
BEGIN
stmt := 'INSERT INTO item VALUES '
|| '( item_s1.nextval '
|| ',''ASIN''||CHR(58)||:asin '
|| ',(SELECT common_lookup_id '
|| ' FROM common_lookup '
|| ' WHERE common_lookup_type = :item_type)'
|| ', :item_title '
|| ', :item_subtitle '
|| ', empty_clob() '
|| ', NULL, NULL '
|| ', :rating '
|| ', :agency '
|| ', :release_date '
|| ', 3, SYSDATE, 3, SYSDATE)';
dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt
USING asin, item_type, item_title, item_subtitle, rating, agency, release_date;
END insert_item;
/
