Package persistent stage - Example

Package persistent stage - Example
--------------------------------------------------------------------------------------- Every time ( in the same session ) that a procedure EMP is executed it returns the next 30 rows from table employees until the cursor returns all rows.

CREATE OR REPLACE PACKAGE EMP AS

/* TODO enter package declarations (types, exceptions, methods etc) here */
cursor c is select * from employees;
return_rows number:=30;
procedure PRINTEMPLOYEES ;
END EMP;
----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY EMP AS

procedure PRINTEMPLOYEES
as
cr c%ROWTYPE;
fetched number:=0;
BEGIN

IF NOT c%ISOPEN THEN
OPEN c;
END IF;
loop
fetch c into cr;
EXIT WHEN c%ROWCOUNT > return_rows OR c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cr.last_name);
end loop;
return_rows:=return_rows+30;
END PRINTEMPLOYEES;

END EMP;
/
----------------------------------------------------------------------------------------

Check Alert.log for ORA- errors using PLSQL

create or replace procedure write_file

( DIR IN VARCHAR2,filename IN VARCHAR2,text IN varchar2)

is

file utl_file.file_type;

begin

file:=utl_file.fopen(DIR, filename,'a');

utl_file.put_line(file,text);

utl_file.new_line(file);

utl_file.fclose(file);

end;

/

create or replace

procedure read_file ( DIR IN VARCHAR2,filename IN VARCHAR2, report IN VARCHAR2)

is

file utl_file.file_type; --file handler

buffer char(1000);

--EOF boolean:=false;

begin

if not utl_file.is_open(file) then

file := utl_file.fopen(DIR,filename,'R');

end if;

loop

begin

utl_file.get_Line(file,buffer,32767);

--dbms_output.put_line(buffer);

if instr(buffer,'ORA-')=1 then

---dbms_output.put_line(buffer);

write_file(DIR, report,buffer);

end if;

exception

when no_data_found then

exit;

end;

end loop;

-- To run everynight at midnight starting tonight

exec dbms_job.submit

(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

How to call c shared library (.so) from PLSQL

The example uses Oracle Linux 5 32bits , Oracle 11gR2 EE

Call C

csum

int csum(int a, int b)
{
return (a+b);
}

gcc -c -fPIC csum.c -o csum.o
gcc -shared -Wl,-soname,csum.so.1 -o csum.so.1.0.1 csum.o

mv csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so.1

create or replace library calc_sum as '/oracle/product/11.2.0/dbhome_1/lib/csum.so';
/

create or replace function getCSUM( a binary_integer,b binary_integer) return binary_integer
as language c
library calc_sum
name "csum";
/

select getCSUM(10,2) from dual;

How to Call C and Java from PLSQL

Call C

csum

int csum(int a, int b)
{
return (a+b);
}

gcc -c -fPIC csum.c -o csum.o
gcc -shared -Wl,-soname,csum.so.1 -o csum.so.1.0.1 csum.o

mv csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so
ln -sf /oracle/product/11.2.0/dbhome_1/lib/csum.so.1.0.1 /oracle/product/11.2.0/dbhome_1/lib/csum.so.1

create or replace library calc_sum as '/oracle/product/11.2.0/dbhome_1/lib/csum.so';
/

create or replace function getCSUM( a binary_integer,b binary_integer) return binary_integer
as language c
library calc_sum
name "csum";
/

select getCSUM(10,2) from dual;

Call Java

java

public class JSUM

{

public static int sum ( int a , int b){return a+b;}

}

[oracle@db11g myC]$ /oracle/product/11.2.0/dbhome_1/jdk/bin/javac JSUM.java

[oracle@db11g myC]$ loadjava -user hr/hr JSUM.class

 ALTER JAVA CLASS "JSUM"
    RESOLVER (("C:\app\Administrator\product\11.2.0\dbhome_1\jdk\bin\*" pm)(* public))
   RESOLVE;

create or replace function javaSUM( a number,b number) return number

as

language java

name 'JSUM.sum(int,int) return int';

/

select javaSUM(1,2) from dual;

PLSQL Large objects example

Large objects exercise

As sys create the oracle directory EMP_DATA. Grant the appropriate privileges to user HR to read and write on this directory.

Modify your employees table (_emp) add columns emp_resume clob and emp_photo bfile. For each employee that exist in your table update the new columns as follows: (a) for emp_resume add the String 32000 bytes String emp_name ||' resume is: '||plus dots(....) till 32000 characters (b) add the anonymous photo for all employee.

Write a procedure to print your table's date. For the resume print its length,for photo just print the photo size.

Solution

As sys:

create or replace directory IMG as 'C:\Libraries\Pictures';

grant read,write on directory IMG to hr;

As hr:

drop table michael_emp purge;

create table michael_emp

(

emp_id number primary key,

emp_name varchar2(30) not null

);

insert into michael_emp values ( 1,'mmmmm');

insert into michael_emp values ( 2,'ggggg');

commit;

alter table michael_emp add emp_resume clob;

alter table michael_emp add emp_photo bfile;

create or replace procedure updateEMP

as

cursor c is select emp_id,emp_name from michael_emp FOR UPDATE;

cr c%ROWTYPE;

begin

for cr in c loop

update michael_emp set emp_resume=RPAD(cr.emp_name||' resume is: ',32000,'.') ,

emp_photo=bfilename('EMP_DATA','anonymous.jpg')

where current of c;

end loop;

commit;

end;

CREATE OR REPLACE PROCEDURE PRINTEMP AS

cursor c is select * from michael_emp ;

cr c%ROWTYPE;

BEGIN

for cr in c loop

dbms_lob.fileopen(cr.emp_photo);

dbms_output.put_line('Employee ID:'||cr.emp_id||' Name:'||cr.emp_name||' Resume size: '||dbms_lob.getlength(cr.emp_resume) ||' Photo size: '||dbms_lob.getlength(cr.emp_photo));

dbms_lob.fileclose(cr.emp_photo);

end loop;

END PRINTEMP;

Create a small plsql parser

CREATE OR REPLACE FUNCTION pl_parser ( SQL_ IN VARCHAR2) RETURN varchar2
AS
cursor_name INTEGER;
ecode NUMBER;
emesg VARCHAR2(200);
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name,SQL_,DBMS_SQL.NATIVE);
return 0;
EXCEPTION WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
return SQLERRM;
END;
/

Using PLSQL Collections -- Simple Example with Nested Table

Scenario:

============

Three intelligence sensors are used to keep the temperature and humidity of an Area XYZ .

Create a table that will be host sensors results.

Create a procedure to add the temperature (Celsius) and humidity per sensor.

Create a procedure to display temperature (Fahrenheit or Celsius) and humidity per sensor.

Use the TABLE expression to display the table's results

Note : You ust use a NESTED TABLE for your COLLECTION.

create or replace type sensor_measure is object

(

temperature number,

humidity number

);

/

create or replace type sensor_measure_tab as table of sensor_measure;

/

create table sensors

(

sensor_id number primary key,

sensor_name char(1) unique,

location char(3)

);

create table weather

(

sensor_id number,

m_date number(8) not null,

time number(4) not null,

s_measures sensor_measure_tab

)

NESTED TABLE s_measures STORE AS s_measures_storage;

alter table weather add constraint weather_fk foreign key(sensor_id) references sensors ( sensor_id);

insert into sensors values ( 1,'A','XYZ');

insert into sensors values ( 2,'B','XYZ');

insert into sensors values ( 3,'C','XYZ');

commit;

-------------------------------------------------------

CREATE OR REPLACE PROCEDURE ADD_SENSOR_MEASURES

(

SENSOR_ID_ IN NUMBER

, TEMPERATURE_ IN NUMBER

, HUMIDITY_ IN NUMBER

)

AS

sm sensor_measure;

s sensor_measure_tab;

i integer;

m_date_ number;

r_date_ number;

BEGIN

sm:=sensor_measure(TEMPERATURE_,HUMIDITY_);

s:=sensor_measure_tab(sm);

m_date_ := to_number(to_char(sysdate,'YYYYMMDD'));

r_date_ := to_number(to_char(sysdate,'HH24MI'));

insert into weather values (

SENSOR_ID_,

m_date_,

r_date_,

s);

commit;

END ADD_SENSOR_MEASURES;

--------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PRINT_WEATHER_PER_SENSOR

(

SENSOR_ID_ IN NUMBER

)

AS

cursor data_(sensorid number) is

select w.sensor_id, w.m_date,w.time,w.s_measures

from weather w

where w.sensor_id=SENSORID;

r data_%ROWTYPE;

s sensor_measure_tab;

sm sensor_measure;

BEGIN

dbms_output.put_line('Data for sensor:'||SENSOR_ID_);

For r_data IN data_(sensor_id_) loop

for i in r_data.s_measures.first..r_data.s_measures.last loop

sm:=r_data.s_measures(i);

dbms_output.put_line('Date:'||r_data.m_date||' '||'Time:'||r_data.time||' Temperature:'||sm.temperature||' Humidity:'||sm.humidity);

end loop;

end loop;

END PRINT_WEATHER_PER_SENSOR;

------------------------------------------------------------------------------------------------------------

select w.sensor_id, w.m_date,time,ws.temperature,ws.humidity from weather w,table(w.s_measures) ws

PLSQL Fine Grained Control Example

-- sys --

CREATE OR REPLACE

PACKAGE PROTECT_HR_SALARY_PKG AS

procedure set_app_context(emp_id_value IN number);

function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2;

END PROTECT_HR_SALARY_PKG;

CREATE OR REPLACE

PACKAGE BODY PROTECT_HR_SALARY_PKG

AS

procedure set_app_context(emp_id_value IN number) AS

BEGIN

dbms_session.set_context ('protect_hr_salaries','emp_id_value',''||emp_id_value);

NULL;

END set_app_context;

function the_predicate (schema_in IN varchar2,name_in IN VARCHAR2) return varchar2

AS

v_r varchar2(2000);

employee_id_ number;

BEGIN

employee_id_:=nvl(sys_context('protect_hr_salaries','emp_id_value'),0);

v_r := 'department_id=( select department_id from hr.departments where manager_id='||employee_id_||')';

return v_r;

END the_predicate;

END PROTECT_HR_SALARY_PKG;

create or replace context protect_hr_salaries using PROTECT_HR_SALARY_PKG;

-- Apply the policy function to the table.

BEGIN

DBMS_RLS.ADD_POLICY (object_schema => 'HR',

object_name => 'EMPLOYEES',

policy_name => 'PROTECT_EMPLOYEES',

function_schema => 'sys',

policy_function => 'PROTECT_HR_SALARY_PKG.the_predicate',

statement_types => 'SELECT,INSERT,UPDATE,DELETE',

enable => TRUE);

END;

/

grant execute on PROTECT_HR_SALARY_PKG to hr

hr

begin

sys.PROTECT_HR_SALARY_PKG. set_app_context(103);

end;

/

SQL> select first_name,salary from employees;

FIRST_NAME SALARY

-------------------- ----------

Alexander 9000

Bruce 6000

David 4800

Valli 4800

Diana 4200

begin

sys.PROTECT_HR_SALARY_PKG. set_app_context(102);

end;

/

SQL> select first_name,salary from employees;

no rows selected

Using PLSQL Collections -- Simple Example with VARRAY

Using Collections

===================

Simple Example with VARRAY

create or replace type colors as varray (10) of varchar2(10);

set serveroutput on size 1000000;

DECLARE

rainbow colors;

BEGIN

rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet');

for i in rainbow.first..rainbow.last loop

DBMS_OUTPUT.put_line(rainbow(i));

end loop;

END;

/

create table my_rainbows_per_day

(

r_date number(8),

rainbow colors

);

---INSERT DATA---

insert into my_rainbows_per_day values ( '20111024',Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet'));

insert into my_rainbows_per_day values ( '20111025',Colors('Red','Orange','Yellow','Green'));

insert into my_rainbows_per_day values ( '20111026',Colors('Red','Orange','Yellow','Green'));

--- Read data using first and last methods

set serveroutput on size 1000000

DECLARE

cursor c is

select r_date ,rainbow

from MY_RAINBOWS_PER_DAY;

c_r c%rowtype;

BEGIN

FOR c_r IN c LOOP

DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);

for i in c_r.rainbow.first..c_r.rainbow.last loop

DBMS_OUTPUT.put_line(c_r.rainbow(i));

end loop;

END LOOP;

END;

/

--- Read data using first and next methods

set serveroutput on size 1000000

DECLARE

cursor c is

select r_date ,rainbow

from MY_RAINBOWS_PER_DAY;

c_r c%rowtype;

i integer;

BEGIN

FOR c_r IN c LOOP

DBMS_OUTPUT.put_line('DAY:'||c_r.r_date);

i:=c_r.rainbow.first;

while i is not null loop

DBMS_OUTPUT.put_line(c_r.rainbow(i));

i:=c_r.rainbow.next(i);

end loop;

END LOOP;

END;

/

---- Read data using the TABLE expression

select * from TABLE( select r.rainbow from my_rainbows_per_day r where r.r_date=20111024);

---------------------------------------------------------------------------------------------