Transcript advanced features

ADVANCED FEATURES
Of
PL/SQL
*
*
*
*
*
PROCEDURAL LANGUAGE FEATURE
OBJECT ORIENTED FEATURES
EXCEPTION HANDLING
INCREASED SECUTIRY
PACKAGES
PROGRAMMING FEATURES
* PROCEDURES and FUNCTIONS
* BLOCK FEATURES
* DEBUGGING IS FRUSTRATION
* REUSABILITY
* INBUILT PACKAGES
PROCEDURES AND FUNCTIONS
BLOCK FEATURES
DEBUGGING
- Suspending Execution
breakpoint: unconditional and conditional
- Stepping through the code
STEP IN, STEP OVER, STEP OUT,
RUN TO CURSOR
- Monitoring values
WATCH window
- Monitoring Execution Flow
CALL STACK window
This is done using Sql Program Debugger Window, one
of the modules of PL/SQL and needs installation
REUSABILITY
BEST EXAMPLES ARE INBUILT PACKAGES
USER FRIENDLY
- Search keys in PL/SQL codes
- Print to the screen
- Read or Write from PL/SQL
- Use SQL from PL/SQL
- Execute Operating System Commands
INBUILT PACKAGES
e.g Banking Package
package banking is
procedure new_acct(name IN VARCHAR);
procedure acct_dep(acno IN NUMBER, amount IN NUMBER);
procedure acc_wdr(acno IN NUMBER, amount IN NUMBER);
procedure acc_bal(acno IN NUMBER, bal OUT NUMBER);
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN;
end banking;
OBJECT ORIENTED
* Data Centric
* Encapsulation
* Function Overloading
* DOES NOT SUPPORT INHERITENCE
WE CAN PROTECT OUR SOURCE CODE
wrap iname=myscript.sql
oname=xxxx.plb
CALL REMOTE PROCEDURES
SECURITY
? LOGS OF FILE and CODE
e.g. We can keep log of last modification of any code
with the following code.
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI')
CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI')
MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
?
KEEP HISTORY
e.g code
CREATE TABLE SOURCE_HIST
AS
SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE
WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE')
then INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN raise_application_error(-20000, SQLERRM);
END;
EXCEPTION HANDLING
TYPICAL BODY OF AN PL/SQL PROGRAM
DECLARE
…..
BEGIN
…..
EXCEPTION
…..
END;
OTHERS
* INTERPORATBILITY and
INTERPRETABILITY
•JAVA INSIDE PL/SQL
• MAILS from DATABASE
JAVA
Step 1 Identify the Java functionality
you want to use in your application.
See if the existing Java class
libraries have any predefined Java
classes containing methods that have
that functionality.
Step 2 Create a specific Java class
that includes a method based on this
functionality.
Step 3 Compile the Java class, test
it, and load it into the database.
Step 4 Build a PL/SQL wrapper
program that will call the
Java stored procedure you've
loaded.
Step 5 Grant the privileges
required for the PL/SQL wrapper
program and the Java stored
procedure it references.
Step 6 Call the PL/SQL program.
Build a Custom Java Class
Why can't you just call File.delete
directly inside the PL/SQL wrapper?
There are two reasons:
A Java method is, in almost every
case (except for static and class
methods), executed for a specific
object instantiated from the class.
You cannot instantiate a Java
object from within PL/SQL and then
invoke the method on that object.
Datatypes in Java and P L/SQ L do not map
directly to each other. For example, you
can't pass a Java Boolean datatype directly
to a P L/ SQ L Boolean datatype.
Therefore, you need to build your own class
that will :- Instantiate an ob ject from the File class
- Invoke the delete method on that ob ject
– Return a value that P L/SQ L can interpret