OCP Developer PL

part i language syntax

1 overview of pl/sql


an introduction to the pl/sql language

pl/sql syntax fundamentals

statements

identifiers

comments

literals

declaration section

variables

datatypes

constants

other declared elements

processing section

expressions

assignment statements

conditional stateents

loops

.cursors

implicit cursors

explicit cursors

cursor attributes

loops revisited:the cursor for loop

advanced datatype declaration

%type

%rowtype

exception-handling section

system-defined exceptions

user-defined exceptions

working with blocks

nested blocks

scope:declared elements

exception scope and exception propagation

an introduction to program units

anonymous blocks

named program units

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


2 procedures


uses of procedures

what can you do with procedures?

where can you store procedures?

creating,altering,and dropping procedures

creating procedures

altering procedures

dropping procedures

invoking procedures

executing a procedure from a pl/sql block

executing a procedure from the sql*plus command line

parameters

parameter declaration syntax

parameter datatypes

default values

parameter types

positional notation versus named notation

data dictionary resources for procedures

user_objects

user_object_size

user_source

user_errors

show errors

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


3 functions


uses of functions

functions versus procedures

creating,altering,and dropping functions

creating functions

altering functions

dropping functions

invoking functions

functions called from pl/sql expressions

functions called from sql statements

client-side functions

parameters

passing parameters by reference

return

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


4 packages


uses of packages

creating,altering,and dropping packages

creating a package specification

creating a package body

public versus private constructs

global constructs

altering a package

dropping a package

changes to a package specification that require a change to the body

invoking packaged constructs

referencing packaged constructs

using packaged constructs

data dictionary resources for packages

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


5 triggers


uses of triggers

creating,altering,and dropping triggers

creating triggers

the:old and:new qualifiers

conditional predicates

firing rules

restrictions

altering triggers

dropping triggers

enabling and disabling triggers

disabling and enabling a named trigger

enabling and disabling a named tables’s triggers

instead of triggers

non-dml triggers

ddl triggers

data dictionary resources for triggers

user_triggers

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


6 working with program units


client-side versus server-side program units

invoking server-side program units

invoking client-side program units

the trade-of—where to put them?

local subprograms

forward declarations

overloading
creating overloaded modules

namespaces for program units

initializing variables with a one-time-only procedure

functions and purity levels

wnds and rnds

wnps and rnps

trust

purity levels for the package initialization section

default

the benefits of pragma

persistent states

persistent variables and constants

persistent tables,records,and types

persistent cursors

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


7 working with oracle's pl/sql packages


dbms_output

set serveroutput on

enable and disable

put_line,put,and new_line

get_line and get_lines

dbms_job

job_queue_processes and job_queue_interval

the submit procedure

the remove procedure

the run procedure

the change,next_date,what,kinterval,and instance procedures

other procedures:user_export,broken,and isubmit

monitoring batch jobs with user_jobs

dbms_ddl

the alter_compile procedure

the analyze_object procedure

monitoring the results of alter_compile and analyze_object

dbms_pipe

the pack_message procedure

the send_message procedure

the receive_message procedure

the next_item_type procedure

the unpack_message procedure

other procedures

dbms_sql

the open_cursor function and the close_cursor procedure

the parse procedure

the define_conlumn procedure

the execute function

the fetch_rows function

the column_value procedure

the bind_variable procedure

native dynamic sql

compilation errors

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


patr ii development tools


8 using oracle pl/sql tools


sql*plus

the sql*plus buffer editor

using text editors

executing text files

pl/sql blocks and the buffer

procedure builder

object navigator

creating a program unit with procedure builder

executing a program unit

moving program units

working with libraries

creating database triggers

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


9 debugging pl/sql


debugging concepts

debugging techniques

code formatting:an example

debugging tools

debugging with sql*plus

debugging compilation errors with sql*plus

show error

debugging execution error in sql*plus

debugging with procedure builder

starting debugging:setting breakpoints

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


part iii privileges and interdependence


10 managing privileges


system privileges

roles

system and object privilege requirements for program units

system privileges for program units

object privileges for program units

owner and invoker rights

owner rights

invoker rights

granting and revoking privileges

data dictionary resources

session_privs

session_roles

user_sys_privs

user_role_privs

dba_sys_privs

user_tab_privs,user_tab_privs_made,and user_tab_privs_recd

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


11 managing interdependencies


tracking dependencies

user_dependencies

deptree and ideptree

dependency issues within a single database

dependency issues across multiple databases

the timestamp mode

the signature mode

avoiding recompilation errors

chapter summary

two-minute drill

chapter questions

answers to chapter quetions


part iv practice exams


12 practice exams


practice exam #1

answers to practice exam #1

practice exam #2

answers to practice exam #2

practice exam #3

answers to practice exam #3