Oracle database 11 g advanced plsql 5 17 copyright

Oracle Database 11 g : Advanced PL/SQL 5 - 17
Copyright © 2008, Oracle. All rights reserved. 5 - 17
Executing the External Procedure
1. Create and compile the external procedure in 3GL.
2. Link the external procedure with the shared library at the
operating system level.
3. Create an alias library schema object to map to the
operating system’s shared library.
4. Grant execute privileges on the library.
5. Publish the external C procedure by creating the PL/SQL
subprogram unit specification, which references the alias
library.
6. Execute the PL/SQL subprogram that invokes the external
procedure.
Executing the External Procedure: Example
Here is a simple example of invoking the external routine:
BEGIN
DBMS_OUTPUT.PUT_LINE( tax_amt (100));
END;
You can call the function in a cursor FOR loop or in any location where a PL/SQL function call
is allowed:
DECLARE
CURSOR cur_orders IS
SELECT order_id, order_total
FROM orders;
v_tax NUMBER(8,2);
BEGIN
FOR order_record IN cur_orders
LOOP
v_tax := tax_amt (order_record.order_total);
DBMS_OUTPUT.PUT_LINE('Total tax: ' || v_tax);
END LOOP;
END;

Oracle Database 11 g : Advanced PL/SQL 5 - 18
Copyright © 2008, Oracle. All rights reserved. 5 - 18
Java: Overview
The Oracle database can store Java classes and Java source,
which:
• Are stored in the database as procedures, functions, or
triggers
• Run inside the database
• Manipulate data
Java: Overview
The Oracle database can store Java classes ( .class files) and Java source code ( .java files),
which are stored in the database as procedures, functions, or triggers. These classes can
manipulate data but cannot display graphical user interface (GUI) elements such as Abstract
Window Toolkit (AWT) or Swing components. Running Java inside the database helps these
Java classes to be called many times and manipulate large amounts of data without the
processing and network overhead that comes with running on the client machine.
You must write these named blocks, and then define them by using the loadjava command or
the SQL CREATE FUNCTION , CREATE PROCEDURE , CREATE TRIGGER , or CREATE
PACKAGE statements.

Oracle Database 11 g : Advanced PL/SQL 5 - 19
Copyright © 2008, Oracle. All rights reserved. 5 - 19
Calling a Java Class Method by Using PL/SQL
libunits
Java class
/home/java/bin/Agent.class
1
3
Java
Virtual
Machine
2
CREATE
JAVA
4
Calling a Java Class Method by Using PL/SQL
The loadjava command-line utility uploads the Java binaries and resources into a system-
generated database table. It then uses the CREATE JAVA statement to load the Java files into
the RDBMS libunits. You can upload the Java files from file systems, Java IDEs, intranets, or
the Internet.
When the CREATE JAVA statement is invoked, the Java Virtual Machine library manager on
the server loads the Java binaries and resources from the local BFILE s or LOB columns into the
RDBMS libunits. Libunits can be considered analogous to the DLLs written in C, although they
map one-to-one with Java classes, whereas DLLs can contain multiple routines.