1 / 21

ADVANCED FEATURES

Explore advanced features like procedural and object-oriented programming, exception handling, and increased security in PL/SQL. Learn about debugging, reusability, and inbuilt packages for efficient coding. Discover Object-Oriented concepts like encapsulation and function overloading. Incorporate Java functionality and secure remote procedure calls in your applications. Improve code maintenance with history tracking and exception handling. Enhance user experience with search keys, screen printing, and system command execution.

raymondclay
Download Presentation

ADVANCED FEATURES

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADVANCED FEATURES Of PL/SQL

  2. * PROCEDURAL LANGUAGEFEATURE *OBJECT ORIENTED FEATURES *EXCEPTION HANDLING *INCREASED SECUTIRY *PACKAGES

  3. PROGRAMMING FEATURES * PROCEDURES and FUNCTIONS * BLOCK FEATURES * DEBUGGING IS FRUSTRATION * REUSABILITY * INBUILT PACKAGES

  4. PROCEDURES AND FUNCTIONS

  5. BLOCK FEATURES

  6. 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

  7. REUSABILITY BEST EXAMPLES ARE INBUILT PACKAGES

  8. 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

  9. 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;

  10. OBJECT ORIENTED * Data Centric * Encapsulation * Function Overloading * DOES NOT SUPPORT INHERITENCE

  11. WE CAN PROTECT OUR SOURCE CODE • wrap iname=myscript.sql • oname=xxxx.plb • CALL REMOTE PROCEDURES

  12. 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';

  13. ? KEEP HISTORY e.g code CREATE TABLE SOURCE_HIST AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.* FROM USER_SOURCE WHERE 1=2;

  14. 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;

  15. EXCEPTION HANDLING TYPICAL BODY OF AN PL/SQL PROGRAM DECLARE ….. BEGIN ….. EXCEPTION ….. END;

  16. OTHERS • * INTERPORATBILITY and • INTERPRETABILITY • JAVA INSIDE PL/SQL • MAILS from DATABASE

  17. JAVA

  18. Step 1 Identify the Java functionality you want to use inyour application. See if the existing Java class libraries haveany predefined Java classes containing methods that havethat functionality. Step 2 Create a specific Java class that includes a methodbased on this functionality. Step 3 Compile the Java class, test it, and load it into thedatabase.

  19. 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.

  20. Build a Custom Java Class Whycan't you just call File.delete directly inside thePL/SQL wrapper? There are two reasons: A Java method is, in almost every case (except for staticand class methods), executed for a specific objectinstantiated from the class. You cannot instantiate aJava object from within PL/SQL and then invoke themethod on that object.

  21. Datatypes in Java and PL/SQL do not map directly toeach other. For example, you can't pass a Java Booleandatatype directly to a PL/SQL Boolean datatype. • Therefore, you need to build your own class that will :- • - Instantiate an object from the File class • Invoke the delete method on that object • – Return a value that PL/SQL can interpret

More Related