Pragmas

PRAGMAs are processed at compile time, not at run time.

source for objects

user_views for views

user_source for
- procedure , function , package , package body, type

Packages

When a PL/SQL-packaged construct is referenced for the first time, the whole package is loaded
into memory.
You can code and compile a specification without its body.
The package specification may also include PRAGMAs, which are directives to the compiler.
If a package specification does not contain subprogram declarations, then there is no
requirement for a package body.
Variables declared in the package specification are initialized to NULL by default.

definition of package level data: variable,cursor etc declared either in spec or body
but outside the procedure/function.

Package level data is visible only during the user session but across transactions. Eg an open
cursor either defined in package body or spec will remain open for the whole
session and different programs (proc/functions) can access the open cursor. You could open in one program , fetch from second and close in third.

use DBMS_PIPE or Oracle Advanced Queueing to share data across sessions.


Place items in the declaration part of the package body
when you must maintain them throughout
a session or across transactions.
The fine-grain dependency management reduces the
need to recompile referencing subprograms when a
package specification changes.

package variables good example http://www.dba-oracle.com/plsql/t_plsql_global_data.htm

Visibility of Package Components
The visibility of a component describes whether that component can be seen, that is, referenced
and used by other components or objects. The visibility of components depends on whether they
are locally or globally declared.
Local components are visible within the structure in which they are declared, such as:
• Variables defined in a subprogram can be referenced within that subprogram, and are not
visible to external components—for example, local_var can be used in procedure A.
• Private package variables, which are declared in a package body, can be referenced by other
components in the same package body. They are not visible to any subprograms or objects
that are outside the package. For example, private_var can be used by procedures A
and B within the package body, but not outside the package.
Globally declared components are visible internally and externally to the package, such as:
• A public variable, which is declared in a package specification, can be referenced and
changed outside the package (for example, public_var can be referenced externally).
• A package subprogram in the specification can be called from external code sources (for
example, procedure A can be called from an environment external to the package).


Overloading Subprograms
Restrictions
You cannot overload:
• Two subprograms if their formal parameters differ only in data type and the different data
types are in the same family (NUMBER and DECIMAL belong to the same family.)
• Two subprograms if their formal parameters differ only in subtype and the different
subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL
subtypes of VARCHAR2.)
• Two functions that differ only in return type, even if the types are in different families

What are possible side effects of using a stored function in a sql query (related to purity level)
It is important to control side effects because they can prevent the proper parallelization of a
query, produce order-dependent and therefore indeterminate results, or require impermissible
actions such as the maintenance of package state across user sessions.

Add PRAGMA SERIALLY_RESUABLE to the package
specification, then the database stores package variables in the System Global Area (SGA)
shared across user sessions. In this case, the package state is maintained for the life of a
subprogram call or a single reference to a package construct. The SERIALLY_REUSABLE
directive is useful if you want to conserve memory and if the package state does not need to
persist for each user session.

This PRAGMA is appropriate for packages that declare large temporary work areas that are used
once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you
must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA),
not allocated to individual users in the User Global Area (UGA). That way, the package work
area can be reused. When the call to the server ends, the memory is returned to the pool. Each
time the package is reused, its public variables are initialized to their default values or to NULL.
Note: Serially reusable packages cannot be accessed from database triggers or other PL/SQL
subprograms that are called from SQL statements. If you try, the Oracle server generates an
error.

Initializing Packages
1) The block at the end of the package body executes once
and is used to initialize public and private package
variables.
2) Also any package level data (such as number variable or string constant).
package level data are of 2 types , public ie declared in the package spec outside of any subprogram
and private ie declared in the package body outside any stored proc/function.
3) Also default values to variable and constants as specified in their declarations

The first time the package is used/referenced the package data (above 3) is initialized.

Functions

functions
- deterministic
- parallel_enable
- authid
- pipelined
- result_cache
- dml inside functions

good examples here http://blogs.oracle.com/sysdba/2009/07/workaround_the_dmls_plsql_func.html

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
NAME VARCHAR2(10)


SQL>
1 create or replace function test_fn(a number)
2 return number
3 is
4 begin
5 insert into test(a) values (a);
6 return 100;
7* end;
SQL> /

Function created.

SQL> select test_fn(20) from dual;
select test_fn(20) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "NEETU.TEST_FN", line 5


SQL> insert into test(a) values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> update test set a = test_fn(2000)
2 where a = 100;

0 rows updated.

SQL> select * from test;

A NAME
---------- ----------
20

SQL> update test set a = test_fn(2000)
2 where a = 20;
update test set a = test_fn(2000)
*
ERROR at line 1:
ORA-04091: table NEETU.TEST is mutating, trigger/function may not see it
ORA-06512: at "NEETU.TEST_FN", line 5


Also cannot perform a commit / rollback inside a function.




- function can be used in
pl/sql expression, sql query in where,having, connect by , start with , order by
values of insert and set of update

Restrictions When Calling Functions
from SQL Expressions
• User-defined functions that are callable from SQL
expressions must:
– Be stored in the database
– Accept only IN parameters with valid SQL data types, not
PL/SQL-specific types
– Return valid SQL data types, not PL/SQL-specific types
• When calling functions in SQL statements:
– Parameters must be specified with positional notation
– You must own the function or have the EXECUTE privilege

The following restrictions apply when calling a function in a SQL statement:

• Parameters must use positional notation. Named notation is not supported. (This is not true in 11g. In 11g can use mixed notation)
• You must own or have the EXECUTE privilege on the function.

Other restrictions on a user-defined function include the following:
• It cannot be called from the CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement.
• It cannot be used to specify a default value for a column.


Controlling Side Effects When
Calling Functions from SQL Expressions
Functions called from:
• A SELECT statement cannot contain DML statements
• An UPDATE or DELETE statement on a table T cannot
query or contain DML on the same table T
• SQL statements cannot end transactions (that is,
cannot execute COMMIT or ROLLBACK operations)
- A session control statement (such as SET ROLE)
- A system control statement (such as ALTER SYSTEM)

PL/SQL Notes

The PL/SQL engine resides in:
r
• The Oracle database for executing stored subprograms

• The Oracle Forms client when running client/server applications, or in the Oracle

Application Server when using Oracle Forms Services to run Forms on the Web

I
The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL
m-code instructions. When the PL/SQL engine encounters a SQL statement, a context switch is
made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the
SQL statement to complete and for the results to be returned before it continues to process
subsequent statements in the PL/SQL block.

The Oracle Forms PL/SQL engine runs in the client for the client/server implementation, and in
the application server for the Forms Services implementation. In either case, SQL statements are
typically sent over a network to an Oracle server for processing.


A procedure is compiled and stored in the database as a schema object. If you are using the
procedures with Oracle Forms and Reports, then they can be compiled within the Oracle Forms
or Oracle Reports executables.

compiled procedure is stored as m-code in the database;
if errors exist then m-code is not stored until all errors are fixed.


The OUT parameter in a Procedure must be actually passed as a variable (actual parameter).
It cannot be assigned a default value. It is an uninitialized variable.
IN OUT parameter also cannot be assigned a default value. I must also be a variable being passed
so that it can take the value back to the caller. It is an initialized variable.

The IN parameter is passed by reference and IN OUT or OUT are passed by value.
passed by value means that a copy is made of the actual parameter to the formal parameter
and the procedure only modifies the local copy until the procedure exits successfully
then the local modified copy is copied to the actual parameter.
If a large collection is passed as IN OUT then it requires double the memory resources
because it copies the collection and makes a local copy in the procedure. To avoid this
declare IN OUT as NOCOPY
eg procedure test (abc IN OUT NOCOPY integer)
source : http://www.dba-oracle.com/t_pl_sql_nocopy_data_structures.htm

EXCEPTION
If not handled DML is rolled back in that block/procedure and if the caller block/procedure also does not handle the exception its DML is also rolled back.


Functions
You cannot reference host or bind variables in the PL/SQL block of a stored function.

Note: Although the OUT and IN OUT parameter modes can be used with functions, it is not good

programming practice to use them with functions. However, if you need to return more than one

value from a function, consider returning the values in a composite data structure such as a

PL/SQL record or a PL/SQL table.
If IN OUT parameters are used in a function then that function cannot be part of an sql statement
it can be executed as
execute :x := fn(:param);
but not select fn(:param) from dual;

Procedures Functions
Execute as a PL/SQL statement Invoke as part of an expression
Do not contain RETURN clause in Must contain a RETURN clause in the
the header header
Can pass values (if any) using Must return a single value
output parameters
Can contain a RETURN statement Must contain at least one RETURN
without a value statement




CURSOR

When you open a cursor, PL/SQL executes the query for that cursor. It also identifies
the active set of data—that is, the rows from all involved tables that meet the criteria
in the WHERE clause and join conditions. The OPEN does not actually retrieve any of
these rows; that action is performed by the FETCH statement.

Regardless of when you perform the first fetch, however, the read consistency model
in the Oracle database guarantees that all fetches will reflect the data as it existed when
the cursor was opened. In other words, from the moment you open your cursor until
the moment that cursor is closed, all data fetched through the cursor will ignore any
inserts, updates, and deletes performed by any active sessions after the cursor was
opened.

TRIGGERS

Trigger types
- Before
- After
- Row level
- Statement level

Triggers can be for
-DML statements
-DDL statements
-Database events
-INSTEAD of
-Suspended Statements


Row level triggers will give mutating table error even if u read from the tigger table, updating is of course not allowed. This is not the case with Statement level trigger. Statement level trigger can both read and modify the triggering table.

Triggers can have commit within them if used as a part of AUTONOMOUS TRANSACTION.


11g has now compound triggers ie mix both row level and statement level triggers in the same trigger. The keyword is COMPOUND TRIGGER
To distinguish between statement level code and row level code
use
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
AFTER STATEMENT

Triggers can have a max size of 32K but can call stored procedures or functions.

If a trigger raises an error then that part of transaction is rolled back.

can combine update/insert/delete in the same trigger like
AFTER INSERT or UPDATE or DELETE or MERGE -- statement level trigger
on ORDERS
[FOR EACH ROW] -- row level trigger


- REFERENCING old as prior

- INSERTING
- UPDATING
- DELETING
eg IF INSERTING THEN

- WHEN clause of a trigger is part of trigger header and controls if the trigger should be executed at all based on the condition specified.
- WHEN Vs IF -- IF is used within the trigger body when the trigger is being executed. IF is used to code logic within the trigger. eg IF INSERTING

- FOLLOWS another_trigger_name -- used to specify the trigger order

Perl recursion , decimal to binary

#!/usr/bin/perl

print &num2bin(10),"\n";


sub num2bin {
my $n = shift;
return $n if $n == 0 || $n == 1;

my $div = int($n / 2);
my $rem = $n % 2;
my $E = num2bin($div);

return $E . $rem;
}


the order of $E . $rem is very important.


Look at the code below. It prints in reverse. Not an elegant solution , incorrect solution rather. ohh its an infinite loop.

The above code has exit condition (base case) in the beginning and the one below has in the end.
The above code prints the bit in the code itself without using a return value.
order of $rem and $div also needs to be reversed.
The wrong code below highlights the importance of breaking down the problem into simpler sub units, and thats called recursion.

sub num2bin_reverse {
my $n = shift;

my $rem = $n % 2;
my $div = int($n / 2);
print $rem;
num2bin_reverse($div) if ($div > 0);
}

books / material to read in order

Oracle Student guides

1. PL/SQL Vol 1
2. PL/SQL Vol 2
3. PL/SQL Additional Practices
4. Advanced PL/SQL
5. SQL Fundamentals I
6. SQL Fundamentals II
   
7. SQL Tuning Guide
8. SQL Tuning Workshop
9. SQL Tuning Workshop - Practices and Solutions (Appendix A)