create or replace package name is
f_name varchar2(30):='Anonymous '; --global varibles
l_name varchar2(30):='Anonymous '; --global varibles
initial char := '_'; --global varibles
procedure name1;
procedure name1(f_name varchar2,last_name varchar2 default 'Amonymous',initial_v char:=initial);
procedure name1(f_name varchar2,l_name varchar2,initial char);
end;
/
create or replace package body name is
procedure name1 is
begin
dbms_output.put_line('1-Name:'||f_name||' '||initial||' '||l_name);
end name1;
procedure name1 (f_name varchar2,last_name varchar2 default 'Amonymous',initial_v char:=initial)is
begin
dbms_output.put_line('2-Name:'||f_name||' '||initial_v||' '||l_name);
end name1;
procedure name1(f_name varchar2,l_name varchar2,initial char)is
begin
dbms_output.put_line('3-Name:'||f_name||' '||initial||' '||l_name);
end name1;
end;
/
EKTORAS> exec name.name1;
1-Name:Anonymous _ Anonymous
PL/SQL procedure successfully completed.
EKTORAS> exec name.name1(f_name=>'MIKE',last_name=>'Georgiou',initial_v=>'A');
2-Name:MIKE A Anonymous
PL/SQL procedure successfully completed.
EKTORAS> exec name.name1(f_name=>'MIKE',initial_v=>'A');
2-Name:MIKE A Anonymous
PL/SQL procedure successfully completed.
EKTORAS> exec name.name1('MIKE',initial_v=>'A');
2-Name:MIKE A Anonymous
PL/SQL procedure successfully completed.
EKTORAS> exec name.name1('MIKE',initial=>'A',f_name=>'GEORGIOU);
ERROR:
ORA-01756: quoted string not properly terminated
EKTORAS> exec name.name1('MICHAEL');
2-Name:MICHAEL _ Anonymous
PL/SQL procedure successfully completed.
EKTORAS> exec name.name1('MICHAEL','GEORGIOU','A');
BEGIN name.name1('MICHAEL','GEORGIOU','A'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00307: too many declarations of 'NAME1' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EKTORAS> exec name.name1(f_name=>'MICHAEL',l_name=>'GEORGIOU',initial=>'A');
3-Name:MICHAEL A GEORGIOU
PL/SQL procedure successfully completed.
EKTORAS>
create or replace package forwDecl is
procedure t2( id number );
end;
/
create or replace package body forwDecl is
procedure t1( id number ); --forward declaration below
procedure t2( id number ) is
begin
t1(10);
end;
procedure t1( id number ) is
begin
dbms_output.put_line(id);
end;
end;
/
create or replace package hr_standard is
function to_char(id number) return number;
end;
/
create or replace package body hr_standard is
function to_char(id number) return number is
begin
return null;
end;
end;
/
create or replace package vat
is
t1 number :=1;
t2 number :=1;
end;
/
create or replace package body vat
is
t3 number ;
begin
t1 :=10;
t2 :=20;
t3 :=30;
end;
/
create or replace package funct_test is
function em_max_id return number;
end;
/
create or replace package body funct_test is
function em_max_id return number IS
id number;
begin
select max(employee_id) into id from employees;
return id+1;
end;
end;
/
insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
values
(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
from employees;
create or replace package funct_test is
function em_max_id return number;
end;
/
create or replace package body funct_test is
function em_max_id return number IS
id number;
begin
select max(employee_id) into id from employees;
commit;
return id+1;
end;
end;
/
insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
values
(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID
from employees;
EKTORAS> insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)
2 values
3 (funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR');
(funct_test.em_max_id,'Michael','Georgiou','michaelg@netu.com.cy',sysdate,'AC_MGR')
*
ERROR at line 3:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "HR.FUNCT_TEST", line 6
create or replace package funct_test is
function em_max_id return number;
end;
/
create or replace package body funct_test is
function em_max_id return number IS
id number;
begin
select max(employee_id) into id from employees;
return id+1;
end;
end;
/
update employees
set employee_id=funct_test.em_max_id
where employee_id=207;
commit;
EKTORAS> update employees
2 set employee_id=funct_test.em_max_id
3 where employee_id=207;
set employee_id=funct_test.em_max_id
*
ERROR at line 2:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.FUNCT_TEST", line 5
update employees
set employee_id=funct_test.em_max_id
where employee_id=207;
create or replace package pkg_state
is
PRAGMA SERIALLY_REUSABLE;--must be declared first
--tha dosei tin time sta variables tha ginei to job
-- kai amesos meta ta variable tha ginoun set opos itan
a number;
b number;
procedure print;
procedure setA(a1 number);
procedure setB(b1 number);
end;
/
create or replace package body pkg_state
is
PRAGMA SERIALLY_REUSABLE;--must be declared first
procedure print is
begin
dbms_output.put_line('a:'||a);
dbms_output.put_line('b:'||b);
end;
procedure setA(a1 number) is
begin
a :=a1;
print;
end;
procedure setB(b1 number) is
begin
b :=b1;
print;
end;
end;
/
EKTORAS> exec pkg_state.setA(10); --First call
a:10
b:
PL/SQL procedure successfully completed.
EKTORAS> exec pkg_state.print; --first call
a:
b:
PL/SQL procedure successfully completed.
create or replace package pkg_state
is
a number;
b number;
procedure print;
procedure setA(a1 number);
procedure setB(b1 number);
end;
/
create or replace package body pkg_state
is
procedure print is
begin
dbms_output.put_line('a:'||a);
dbms_output.put_line('b:'||b);
end;
procedure setA(a1 number) is
begin
a :=a1;
print;
end;
procedure setB(b1 number) is
begin
b :=b1;
print;
end;
begin
a:=0;
b:=0;
end;
/
create table mike_sequences ( sequence_name varchar2(30)unique,increment_by number default 1,currval number);
create or replace package seq_pkg
is
a number:=0;
session_id :=0;
function nextval(name mike_sequences.sequence_name%type)return number;
function currval(name mike_sequences.sequence_name%type)return number;
procedure setSequence(sequence_name varchar2,increment_by number default 1);
end;
/
create or replace package body seq_pkg
is
function currval(name mike_sequences.sequence_name%type);
function nextval(name mike_sequences.sequence_name%type);
function nextval(name mike_sequences.sequence_name%type)return number is
begin
if session_id = 0 then
a:=currval(name)+1;
return a
else
return a := a +1 ;
end if;
end;
function currval(name mike_sequences.sequence_name%type)return number is
current_value number ;
begin
if session_id = 0 then
select currval into current_value from mike_sequences where sequence_name=Upper(name);
session_id := SYS_CONTEXT('USERENV','SESSIONID');
return current_value;
else
a := a +1 ;
e mike_sequences set currval=a where sequence_name=Upper(name);
commit;
return a;
end if;
end;
procedure setSequence(sequence_name varchar2,increment_by number default 1)is
begin
null;
end;
end;
/
CONNECT Scott/Tiger
DROP PACKAGE Sr_pkg;
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
Num NUMBER := 10;
Str VARCHAR2(200) := 'default-init-str';
Str_tab STR_TABLE_TYPE;
PROCEDURE Print_pkg;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);
END Sr_pkg;
CREATE OR REPLACE PACKAGE BODY Sr_pkg IS
-- the body is required to have the pragma because the
-- specification of this package has the pragma
PRAGMA SERIALLY_REUSABLE;
PROCEDURE Print_pkg IS
BEGIN
DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);
DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);
DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);
FOR i IN 1..Sr_pkg.Str_tab.Count LOOP
DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i));
END LOOP;
END;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
BEGIN
-- init the package globals
Sr_pkg.Num := N;
Sr_pkg.Str := V;
FOR i IN 1..n LOOP
Sr_pkg.Str_tab(i) := V || ' ' || i;
END LOOP;
-- print the package
Print_pkg;
END;
END Sr_pkg;
SET SERVEROUTPUT ON;
Rem SR package access in a CALL:
BEGIN
-- initialize and print the package
DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');
Sr_pkg.Init_and_print_pkg(4, 'abracadabra');
-- print it in the same call to the server.
-- we should see the initialized values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');
Sr_pkg.Print_pkg;
END;
Initing and printing pkg state..
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL...
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
REM SR package access in subsequent CALL:
BEGIN
-- print the package in the next call to the server.
-- We should that the package state is reset to the initial (default) values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');
Sr_pkg.Print_pkg;
END;
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0
drop table customer purge;
create table customer
(
customerid number,
email varchar2(100)
)
tablespace users;
create unique index customer_customerid_pk on customer(customerid) tablespace tools;
alter table customer
add constraint customer_customerid_pk primary key(customerid);
alter table customer
add constraint customer_email_nn check (email <> null);
comment on table customer is 'The customer table contains information for demo customers';
create sequence key_customerid;
insert into customer values(key_customerid.nextval,'michaelg@netu.com.cy');
insert into customer values(key_customerid.nextval,'andreas@netu.com.cy');
insert into customer values(key_customerid.nextval,'katerina@netu.com.cy');
set serveroutput on size 1000000
create or replace procedure selectCustomer
is
cust_data_rec customer%ROWTYPE;
customerid customer.customerid%type;
email customer.email%type;
n number:=0;
cursor cust_data is
select customerid,email from customer;
begin
if not cust_data%isopen then
open cust_data;
end if;
Loop
n:=n+1;
fetch cust_data into customerid,email;
exit when cust_data%notfound;
dbms_output.put_line('['||n||'] '||customerid||' '||email);
end loop;
if cust_data%isopen then
close cust_data;
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error:');
end;
/
create or replace package customer_pkg
is
type all_customer is ref cursor;
procedure findAllCustomers(customers OUT all_customer);
procedure printCustomers;
--procedure findByPrimaryKey(customerid IN number ,customers OUT all_customer);
end;
/
create or replace package body customer_pkg
is
procedure findAllCustomers(customers OUT all_customer)
is
begin
open customers for select * from customer;
end;
procedure printCustomers
is
tmp_customer all_customer;
all_customers customer%rowtype;
n number;
begin
findAllCustomers(tmp_customer);
dbms_output.put_line('Printing customers '||tmp_customer%rowcount);
loop
fetch tmp_customer into all_customers;
exit when tmp_customer%notfound;
dbms_output.put_line(all_customers.customerid||':'||all_customers.email);
end loop;
end;
end;
/
declare
type a is table of char(3200) index by binary_integer;
data a;
begin
for i IN 1..10 loop
data(i):= 'abcd';
end loop;
for i IN 1..10 loop
dbms_output.put_line(data(i));
end loop;
end;
/
begin
FOR i IN (SElect * from customer) LOOP
dbms_output.put_line(i.email);
END LOOP;
end;
/
--wrap pl/sql
create or replace package customer_pkg2
is
type all_customer is ref cursor;
procedure findAllCustomers(customers OUT all_customer);
procedure printCustomers;
--procedure findByPrimaryKey(customerid IN number ,customers OUT all_customer);
end;
/
create or replace package body customer_pkg2
is
procedure findAllCustomers(customers OUT all_customer)
is
begin
open customers for select * from customer;
end;
procedure printCustomers
is
tmp_customer all_customer;
all_customers customer%rowtype;
n number;
begin
findAllCustomers(tmp_customer);
dbms_output.put_line('Printing customers '||tmp_customer%rowcount);
loop
fetch tmp_customer into all_customers;
exit when tmp_customer%notfound;
dbms_output.put_line(all_customers.customerid||':'||all_customers.email);
end loop;
end;
end;
/
--dbms_output
set serveroutput on size 1000000
declare
buffer varchar(100);
status number;
begin
dbms_output.put_line('Write a line');
dbms_output.get_line(buffer,status);
dbms_output.put_line('Your line is :'||buffer);
end;
/
undefine username
set verify off
define username
ACCEPT username PROMPT "Username:"
begin
dbms_output.put_line('Username from check is:'||'&username');
end;
define username
/
--------
/*
UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2,
linesize IN NUMBER,
len IN PLS_INTEGER DEFAULT NULL);
*/
--UTL_FILE
create or replace procedure write_file
is
file utl_file.file_type;
begin
file:=utl_file.fopen('ORA_DIR2','test.txt','w');
utl_file.put_line(file,'first line');
utl_file.new_line(file);
utl_file.put_line(file,'Second line');
utl_file.new_line(file);
utl_file.fclose(file);
file:=utl_file.fopen('ORA_DIR2','test.txt','a');
utl_file.put_line(file,'Thrird line');
utl_file.new_line(file);
utl_file.fclose(file);
end;
/
create or replace procedure read_file
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('ORA_DIR2','test.txt','R',32767);
end if;
loop
begin
utl_file.get_Line(file,buffer,32767);
dbms_output.put_line(buffer);
--exit when EOF=true;
exception
when no_data_found then
--EOF:=true;
exit;
end;
end loop;
if utl_file.is_open(file) then
utl_file.fclose(file);
end if;
exception
when utl_file.INVALID_FILEHANDLE then
dbms_output.put_line('INVALID_FILEHANDLE');
when utl_file.INVALID_OPERATION then
dbms_output.put_line('INVALID_OPERATION');
when utl_file.READ_ERROR then
dbms_output.put_line('READ_ERROR');
when others then
dbms_output.put_line('Unknow error');
end;
/
SET SERVEROUTPUT ON SIZE 100000
BEGIN -- Generates:
htp.htmlOpen; --------->
htp.headOpen; --------->
htp.title('Welcome'); -->
htp.headClose; --------->
htp.bodyOpen; --------->
htp.print('My home page');
htp.bodyClose; --------->
htp.htmlClose; --------->
END;
EXECUTE OWA_UTIL.SHOWPAGE
BEGIN
UTL_MAIL.SEND('oracle@tom.domain.local','oracle@tom.domain.local',
message => 'For latest downloads visit OTN',
subject => 'OTN Newsletter');
END;
/
DECLARE
file utl_file.file_type;
CURSOR RES IS SELECT ID,ID2 FROM A;
BEGIN
file:=utl_file.fopen('ORA_DIR','test.txt','w');
FOR I IN RES LOOP
--utl_file.put_line(file,I.ID);
NULL;
END LOOP;
--utl_file.fclose(file);
END;
/
