45
Using PL/SQL With OCI8
11 end;
12 /
Salary is 2500
Other tools have different ways to indicate the end of the statements and how to switch
server output on.
If a PHP application performs several SQL statements at one time, it can be efficient to
bundle the statements together in a single PL/SQL procedure. Instead of executing multiple
SQL statements, PHP only needs to execute one PL/SQL call. This reduces the number of
round trips between PHP and the database, and can improve overall performance.
There are a number of pre-supplied PL/SQL packages to make application development
easier. Packages exist for full text indexing, queuing, change notification, sending emails, job
scheduling and TCP access, just to name a few. When deciding whether to write PHP on the
mid-tier or PL/SQL in the server, consider your skill level in the languages, the cost of data
transfer across the network and the re-usability of the code. If you write in PL/SQL, all your
Oracle applications in any tool or client language can reuse the functionality.
Blocks, Procedures, Packages and Triggers
PL/SQL code can be categorized as one of the following:
●
Anonymous blocks
●
Stored procedures or functions
●
Packages
●
Triggers
Anonymous Blocks
An anonymous block is a PL/SQL block included in your application that is not named or
stored in the database. The previous example is an anonymous block. Because these blocks
are not stored in the database, they are generally for one-time use in a SQL script, or for
simple code dynamically submitted to the Oracle server.
Stored Procedures and Functions
A stored procedure is a PL/SQL block that Oracle stores in the database. They can be called by
name from an application. Functions are similar but also return a value when executed.
Procedures and functions can be used from other procedures or functions. They can be
enabled and disabled to prevent them being used. They may also have an invalid state, if
anything they reference is not available. They can be created individually, or be part of a
package.
When you create a stored procedure or function, Oracle stores its parsed representation in
the database for efficient reuse. Procedures can be created in SQL*Plus like:
SQL> create table mytab (mydata varchar2(40), myid number);
SQL> create or replace procedure
2 myproc(d_p in varchar2, i_p in number) as
3 begin
188
46
Blocks, Procedures, Packages and Triggers
4 insert into mytab (mydata, myid) values (d_p, i_p);
5 end;
6 /
The procedure is only created, not run. Programs like PHP can run it later.
PL/SQL functions are created in a similar way using the CREATE OR REPLACE FUNCTION
command.
If you have creation errors, use the SQL*Plus SHOW ERRORS command to display any
messages. For example, creating a procedure that references an invalid table causes an error:
SQL> create or replace procedure
2 myproc(d_p in varchar2, i_p in number) as
3 begin
4 insert into yourtab (mydata, myid) values (d_p, i_p);
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE MYPROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3
PL/SQL: SQL Statement ignored
4/15
PL/SQL: ORA-00942: table or view does not exist
If you are running SQL script files in SQL*Plus, it is helpful to turn SET ECHO ON to see the line
numbers.
See later below for handling PL/SQL errors in PHP.
Packages
Typically, stored procedures and functions are encapsulated into packages. This helps
minimizes recompilation of dependent objects. The package specification defines the
signatures of the functions and procedures. If that definition is unchanged, code that invokes
it will not need to be recompiled even if the implementation of the package body changes.
Script 44: toyshop.sql
create table toys (id number, name varchar2(40));
insert into toys (id, name) values (1, 'bicycle');
commit;
create or replace package toyshop as
function find_toy(id_p in number) return varchar2;
procedure add_toy(id_p in number, name_p in varchar2);
procedure find_toy_proc(id_p in number, name_p out varchar2);
end toyshop;
/
create or replace package body toyshop as
function find_toy(id_p in number) return varchar2 as
name_l varchar2(20);
189
VB Imaging - Postnet Barcode Creation Tutorial creator control add-on will be your best choice. including PNG, BMP, GIF, JPEG, TIFF, PDF, Excel, PowerPoint RasterEdge VB.NET Barcode Creator Add-on can be
adding a text field to a pdf; add attachment to pdf form
42
Using PL/SQL With OCI8
begin
select name into name_l from toys where id = id_p;
return name_l;
end;
procedure add_toy(id_p in number, name_p in varchar2) as
begin
insert into toys (id, name) values(id_p, name_p);
end;
procedure find_toy_proc(id_p in number, name_p out varchar2) as
begin
select name into name_p from toys where id = id_p;
end;
end toyshop;
/
Triggers
A database trigger is a stored procedure associated with a database table, view, or event. The
trigger can be called after the event, to record it, or take some follow-up action. A trigger can
also be called before an event, to prevent erroneous operations or fix new data so that it
conforms to business rules. Triggers were shown earlier as a way to optimize setting date
formats (see Do Not Set the Date or Numeric Format Unnecessarily in the chapter on
connecting) and as a way of creating auto-increment columns (see Auto-Increment Columns
in the previous chapter).
Creating PL/SQL Stored Procedures in PHP
Procedures, functions and triggers can be created using PHP. For example, to create a
procedure BIKE_CREATE the code is:
Script 45: bikecreate.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$plsql = <<<'EOS'
create or replace procedure bike_create(type_p in varchar2) as
begin
insert into bicycles (style) values (type_p);
end;
EOS; // this must be at the start of the line without leading whitespace
$s = oci_parse($c, $plsql);
$r = oci_execute($s);
if ($r) {
echo 'Procedure created';
}
?>
190
42
Creating PL/SQL Stored Procedures in PHP
Note the last character of the PL/SQL statement is a semi-colon (after the PL/SQL keyword
end), which is different to the way SQL statements are terminated in Oracle.
The example shows a PHP NOWDOC containing the statement. If you are on Windows,
make sure you avoid the end-of-line terminator issue mentioned below.
Similar to the earlier performance advice on creating tables, avoid creating packages and
procedures at runtime in an application. Pre-create them as part of application installation.
End of Line Terminators in PL/SQL With Windows PHP
With older versions of Oracle on Windows, multi-line PL/SQL blocks won't run if the line
terminators are incorrect. The problem happens when the end of line characters in a multi-line
PL/SQL string are Windows carriage-return line-feeds:
$plsql = "create or replace procedure
myproc(d_p in varchar2, i_p in number) as
begin
insert into mytab(mydata, myid) values (d_p, i_p);
end;";
The typical error is ORA-24344: success with compilation error.
If the showcompilationerrors() function, shown later, is used, additional Oracle messages
will show the error PLS-00103: Encountered the symbol "" when expecting one of the
following. This error, which may have the symbol ";" or a seemingly empty token representing
the unexpected end-of-line syntax, is followed by a list of keywords or tokens the PL/SQL
parser was expecting.
Use one of these solutions to fix the problem:
●
Write the PL/SQL code on a single line:
$plsql = "create or replace procedure myproc . . . end;";
●
Use PHP string concatenation with appropriate white space padding between string
tokens:
$plsql = "create or replace procedure "
. "myproc(d_p in varchar2, i_p in number) as "
. "begin "
. "insert into mytab(mydata, myid) values (d_p, i_p); "
. "end;";
●
Convert the file to use UNIX-style line-feeds with a conversion utility or editor.
Calling PL/SQL Code
Calling PL/SQL Procedures in PHP
To invoke a PL/SQL procedure from PHP, use BEGIN and END to create an anonymous block:
Script 46: anonplsql.php
<?php
191
VB Imaging - EAN-8 Generating Tutorial VB.NET Barcode Creator Add-on from RasterEdge DocImage SDK to create EAN-8 barcode image with best quality. creating are JPEG, PNG, BMP, GIF, TIFF, PDF and MS
create a pdf form from excel; best program to create pdf forms
37
Using PL/SQL With OCI8
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$s = oci_parse($c, "begin toyshop.add_toy(2, 'ball'); end;");
oci_execute($s);
?>
The block contains a single procedure call, but you could include any number of other PL/SQL
statements.
You can also use the SQL CALL statement like:
Script 47: callplsql.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$s = oci_parse($c, "call toyshop.add_toy(3, 'paddling pool')");
oci_execute($s);
?>
The call command is actually a SQL command and does not have the trailing semi-colon
needed for PL/SQL blocks.
Binding Parameters in PL/SQL Procedure Calls
PL/SQL procedure and function arguments can be marked IN, OUT or IN OUT depending on
whether data is being passed into or out of PL/SQL. Single value parameters can be bound in
PHP with oci_bind_by_name(). In the toyshop.sql example, the find_toy_proc() parameters
were IN and OUT. The code could be:
$s = oci_parse($c, "begin toyshop.find_toy_proc(:id, :name); end;");
$id = 1;
oci_bind_by_name($s, ":id", $id);
oci_bind_by_name($s, ":name", $name, 40);
oci_execute($s);
echo "Name is: ".$name;
The bind call specifies that 40 bytes should be allocated to hold the retrieved toy name. For
OUT and IN OUT parameters, make sure the length is specified in the bind call. As mentioned
in the previous chapter, specifying the length for IN binds is often a good idea too, if the one
statement is executed multiple times in a loop.
Calling PL/SQL Functions in PHP
Calling a PL/SQL function requires a bind variable for the return value. Using the function
find_toy() created previously in toyshop.sql:
Script 48: plsqlfunc.php
<?php
192
VB Imaging - VB Code 93 Generator Tutorial a test now to write and draw the best Code 93 write Code 93 linear barcode pictures on PDF documents, multi a Windows application or ASP.NET web form and copy
chrome save pdf with fields; change font in pdf form
39
Calling PL/SQL Code
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$s = oci_parse($c, "begin :name := toyshop.find_toy(1); end;");
oci_bind_by_name($s, ':name', $name, 40);
oci_execute($s);
echo "Name is: " . $name;
?>
The := token is the assignment operator in PL/SQL. Here it assigns the return value of the
function to the bind variable. The bind call specifies that 40 bytes should be allocated to hold
the result. The script output is:
Name is: bicycle
Binding Unsupported PL/SQL Types
Some PL/SQL data types are internal to PL/SQL and cannot be returned through the C layer
used by the OCI8 extension. In these cases some extra PL/SQL code that maps the type to a
form usable in PHP is needed. For example, the PL/SQL user function is_valid() returns the
internal Oracle type BOOLEAN:
Script 49: isvalid.sql
create or replace function is_valid(p_uid in number) return boolean as
begin
if (p_uid < 10) then
return true;
else
return false;
end if;
end;
/
The anti-example PHP code to call this is:
Script 50: isvalid.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$sql = "begin :r := is_valid(:userid); end;"; // will fail
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':r', $r, 40);
$userid = 4;
oci_bind_by_name($s, ':userid', $userid);
oci_execute($s);
echo "Result is " . ($r ? "true" : "false") . "\n";
?>
193
44
Using PL/SQL With OCI8
The expectation is that user id 4 is less than 10 and so the result will display as true.
However, because of the use of the internal PL/SQL type, this script actually gives the error
PLS-00382: expression is of wrong type. The solution is to change the anonymous PL/SQL
statement to make it evaluate the is_valid() return value and propagate a type that can be
bound in OCI8:
$sql = "begin
if (is_valid(:userid) = true) then
:r := 1;
else
:r := 0;
end if;
end;";
The output of the script is now:
Result is true
Array Binding and PL/SQL Bulk Processing
OCI8 1.2 (PHP 5.1.2) introduced a function, oci_bind_array_by_name(). Used with a PL/SQL
procedure, this can be very efficient for insertion or retrieval, requiring just a single
oci_execute() to transfer multiple values. The following example, arraybind.sql, creates a
PL/SQL package with two procedures. The first, myinsproc(), will be passed a PHP array to
insert. It uses Oracle’s “bulk” FORALL statement for fast insertion. The second procedure,
myselproc(), selects back from the table using the BULK COLLECT clause and returns the array
as the OUT parameter p_arr. The p_count parameter is used to make sure PL/SQL does not
try to return more values than the PHP array can handle.
Script 51: arraybind.sql
drop table mytab;
create table mytab(name varchar2(20));
create or replace package mypkg as
type arrtype is table of varchar2(20) index by pls_integer;
procedure myinsproc(p_arr in arrtype);
procedure myselproc(p_arr out arrtype, p_count in number);
end mypkg;
/
show errors
create or replace package body mypkg as
procedure myinsproc(p_arr in arrtype) is
begin
forall i in indices of p_arr
insert into mytab values (p_arr(i));
end myinsproc;
procedure myselproc(p_arr out arrtype, p_count in number) is
begin
select name bulk collect into p_arr from mytab where rownum <= p_count;
194
40
Array Binding and PL/SQL Bulk Processing
end myselproc;
end mypkg;
/
show errors
To insert a PHP array $a into MYTAB, use:
Script 52: arrayinsert.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$a = array('abc', 'def', 'ghi', 'jkl');
$s = oci_parse($c, "begin mypkg.myinsproc(:a); end;");
oci_bind_array_by_name($s, ":a", $a, count($a), -1, SQLT_CHR);
oci_execute($s);
?>
The oci_bind_array_by_name() function is similar to oci_bind_by_name(). As well as the
upper data length, it has an extra parameter giving the number of elements in the array. In
this example, the number of elements inserted is count($a). The data length –1 tells PHP to
use the actual length of the character data, which is known to PHP.
To query the table in PHP, the myselproc() procedure can be called. The number of
elements $numelems to be fetched is passed into myselproc() by being bound to :n. This
limits the query to return four rows. The value is also used in the oci_bind_array_by_name()
call so the output array $r is correctly sized to hold the four rows returned. The value 20 is the
width of the database column. Any lower value could result in shorter strings being returned
to PHP.
Script 53: arrayfetch.php
<?php
$c = oci_connect("hr", "welcome", "localhost/XE");
$numelems = 4;
$s = oci_parse($c, "begin mypkg.myselproc(:p1, :n); end;");
oci_bind_array_by_name($s, ":p1", $r, $numelems, 20, SQLT_CHR);
oci_bind_by_name($s, ":n", $numelems);
oci_execute($s);
var_dump($r); // print the array
?>
The output is:
array(4) {
[0]=>
string(3) "abc"
[1]=>
195
44
Using PL/SQL With OCI8
string(3) "def"
[2]=>
string(3) "ghi"
[3]=>
string(3) "jkl"
}
A number of other Oracle types can be bound with oci_array_bind_by_name(), for example
SQLT_FLT for floating point numbers.
There are more examples of oci_bind_array_by_name() in the automated OCI8 tests
bundled with the PHP source code, see ext/oci8/tests.
PL/SQL Success With Information Warnings
A common PL/SQL error when creating packages, procedures or triggers is Warning:
oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344: success with compilation error. This
message is most likely to be seen during development of PL/SQL which is commonly done in
SQL*Plus or SQL Developer. It can also be seen during application installation if PL/SQL
packages, procedures or functions have an unresolved dependency.
PHP code to check for informational errors and warnings is shown in the example
plsqlerr.php. It creates a procedure referencing a non-existent table and then queries the
USER_ERRORS table after the ORA-24344 error occurs:
Script 54: plsqlerr.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
ini_set('display_errors', false); // do not automatically show PHP errors
// PL/SQL statement with deliberate error: not_mytab does not exist
$plsql = "create or replace procedure
myproc(d_p in varchar2, i_p in number) as
begin
insert into not_mytab (mydata, myid) values (d_p, i_p);
end;";
$s = oci_parse($c, $plsql);
$r = @oci_execute($s);
if (!$r) {
$m = oci_error($s);
if ($m['code'] == 24344) { // A PL/SQL "success with compilation error"
echo "Warning is " . $m['message'] . "\n";
showcompilationerrors($c);
} else { // A normal SQL-style error
echo "Error is " . $m['message'] . "\n";
}
}
// Display PL/SQL errors
function showcompilationerrors($c)
196
Documents you may be interested
Documents you may be interested
