Advanced Oracle 11g PL/SQL
Advanced Oracle 11g PL/SQL
This Oracle 11g PL/SQL training teaches attendees advanced Oracle PL/SQL database programming skills.
BJECTIVES
- Invoke external procedures and integrate these into PL/SQL applications.
- Use dynamic SQL to extend the functionality and flexibility of database programs.
- Identify SQL injection attack vulnerabilities and security risks to protect against hacking.
- Incorporate collections and other advanced types into application logic to increase efficiency and execution speed.
- Work with LOBs, including piece-wise data manipulation and dynamic modification of SecureFile storage options.
- Expand functionality with system-supplied database utility packages.
- Tune with the DBMS_PROFILER() system-supplied package and debugging with the DBMS_TRACE() system-supplied package.
- Write efficient PL/SQL code and avoiding common coding mistakes.
- Enable native compilation and execution of all database-resident program units.
- Control and managing PL/SQL compilation for high-efficiency execution.
- Analyze PL/SQL code structure by means of the PL/Scope facility.
- Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical Profiler.
- Implement fine-grained security mechanisms as part of an advanced security model.
- Use dynamic partitioning and DML parallelization using the system-supplied package DBMS_PARALLEL_EXECUTE().
ADVANCED ORACLE 11G PL/SQL TRAINING PREREQUISITES
All attendees must have a prior understanding of writing SQL queries, including joins.
ADVANCED ORACLE 11G PL/SQL TRAINING MATERIALS
All students receive comprehensive courseware and a related textbook.
SOFTWARE NEEDED FOR EACH PC:
- A complete installation of Oracle Enterprise Edition 11g or 11g R2
- Oracle SQL Developer
- For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor’s screen simultaneously with working on their own.
ADVANCED ORACLE 11G PL/SQL TRAINING OUTLINE
- Dynamic SQL
- Advantages & Disadvantages
- Native Dynamic SQL
- Dynamic Update…Returning
- Dynamic SQL Using DBMS_SQL()
- Using Collections
- About Collections
- Bulk Bind Using Collections
- Collection Methods
- More About The Returning Clause
- Advanced Collection Features
- Collection Multiset Operations
- In Indices Of Clause
- In Values Of Clause
- System-Supplied Packages: DBMS_METADATA() – Part I
- Why Retrieve Object Definitions?
- Retrieving Default Metadata
- Retrieving Customized Metadata
- Using OPEN() & CLOSE()
- Using SET_FILTER()
- Using SET_COUNT()
- Using ADD_TRANSFORM()
- Using Fetch DDL()
- About Sys.Ku$_Ddl
- About Sys.Ku$_Ddls
- Calling FETCH_DDL()
- System-Supplied Packages: DBMS_METADATA() – Part II
- SET_TRANSFORM_PARAM()
- GET_QUERY()
- System-Supplied Packages: DBMS_METADATA() – Part III
- Fetch CLOB()
- SET_FILTER() for Dependent Objects
- SET_PARSE_ITEM()
- Primary & Dependent Object Ddl
- System-Supplied Packages: DBMS_REDEFINITION()
- About Table Redefinition
- Using DBMS_REDEFINITION()
- DBA_REDEFINITION_ERRORS()
- CAN_REDEF_TABLE()
- START_REDEF_TABLE()
- FINISH_REDEF_TABLE()
- ABORT_REDEF_TABLE()
- COPY_TABLE_DEPENDENTS()
- SYNC_INTERM_TABLE()
- System-Supplied Packages: DBMS_LOB()
- Working with External Bfiles
- Working with Internal Lobs
- LOADBLOBFROMFILE(), LOADCLOBFROMFILE()
- COMPARE()
- GETLENGTH()
- APPEND()
- COPY()
- ERASE()
- TRIM()
- READ()
- SUBSTR()
- INSTR()
- WRITE()
- Dynamic Securefile Options
- GETOPTIONS()
- SETOPTIONS() System-Supplied Packages: Others
- Compression With UTL_COMPRESS()
- LZ_COMPRESS()
- LZ_UN_COMPRESS()
- DBMS_DESCRIBE()
- UTL_MAIL()
- Set SMTP_OUT_SERVER
- Calling the SEND() Procedure
- Calling the SEND_ATTACH_XXX() Procedures
- DBMS_UTILITY()
- COMPLIE_SCHEMA()
- DB_VERSION()
- WAIT_ON_PENDING_DML()
- Advanced Interface Methods
- About External Procedures
- Calling Java Classes
- Calling C Programs
- PL/SQL Advanced Programming & Coding Techniques
- Autonomous Transactions
- Using Nocopy for Parameters
- Choosing the Optimum Data Type
- Avoiding Implicit Data Type Conversion
- Choosing Between Number and Pls_Integer
- About Pls_Integer
- Using Simple_Integer
- Char Variables of Different Lengths
- Varchar2 Variables of Different Lengths
- Char Vs. Varchar2
- Char Vs. Varchar2 with an Equality
- Comparison
- Useful PL/SQL Coding Techniques
- Handling String Literals
- Influencing Oracle PL/SQL Compilation
- PL/SQL Compiler Optimization
- PLSQL_Optimize_Level
- Controlling Compilation Messages
- PL/SQL Native Execution
- Dynamic Partitioning & Parallelization
- About Dynamic Partitioning (Chunks)
- Creating & Processing Chunks
- CREATE_TASK()
- CREATE_CHUNKS_BY_ROWID()
- CREATE_CHUNKS_BY_NUMBER_COL()
- EXECUTE_RUN_TASK()
- TASK_STATUS()
- DROP_TASK()
- Monitoring Chunk Processing
- Application Tuning with the PL/SQL Hierarchical Profiler
- What Is The Hierarchical Profiler?
- Configuring The Profiler
- Managing Profiler Runs
- Analyzing Profiler Data
- Interpreting The Results
- Dbmshp_Runs
- Dbmshp_Function_Info
- Dbmshp_Parent_Child_Info
- PL/SQL Debugging with DBMS_TRACE()
- Using The Trace Facility
- DBMS_TRACE() To Manage Runs
- Examining The Trace Data
- Event_Kind Values
- Protecting Against SQL Injection Attacks
- Understanding The Threat
- Applying Countermeasures
- Implementing Virtual Private Databases
- Understanding VPDs
- Preparing for a VPD
- Configuring a VPD
- Managing Application Contexts
- Using SYS_CONTEXT()
- Managing Policies & Security Rules
- Conclusion
CONTACT US
+91 9376007676INQUIRY NOW
Advanced Oracle 11g PL/SQL






- Course No : ORC-203
- Theory : 40%
- Lab : 60%
- Duration : 30 hours
All attendees must have a prior understanding of writing SQL queries, including joins.