begin
for i in 1..30000
loop
dbms_output.put_line(lpad(i,7,0));
end loop;
end;
-----------------
begin
for i in 1..30000
loop
dbms_output.enable(1000000); --max 1,000,000
dbms_output.put_line(lpad(i,7,0));
end loop;
end;
-----------
--messages not sent untill the PLSQL completed
begin
dbms_output.put_line('test');
dbms_output.disable;
dbms_output.put_line('test2');
dbms_output.enable;
dbms_output.put_line('test3');
end;
-----------------
begin
dbms_output.put_line('welcome');
dbms_output.put('my ');
dbms_output.put('name ');
dbms_output.put('is ');
dbms_output.put('khaled ');
dbms_output.new_line; --you should use this after put
dbms_output.put_line('my name is khaled');
end;
--------------------
--get_line
--we need 2 variables
DECLARE
buffer VARCHAR2(100);
status INTEGER;
begin
dbms_output.put_line('line 1');
dbms_output.put_line('line 2');
dbms_output.put_line('line 3');
--This procedure retrieves a single line of buffered information.
dbms_output.get_line(buffer, status); --both are out parameters
dbms_output.put_line('Buffer: ' || buffer);
dbms_output.put_line('Status: ' || status);
/*If the call completes successfully,
then the status returns as 0. If there are no more
lines in the buffer, then the status is 1.
*/
end;
----------------
DECLARE
buffer VARCHAR2(100);
status INTEGER;
v VARCHAR2(3000);
begin
dbms_output.put_line('line 1');
dbms_output.put_line('line 2');
dbms_output.put_line('line 3');
for i in 1..3
loop
dbms_output.get_line(buffer, status);
v:=v||buffer||chr(10);
end loop;
dbms_output.put_line(v);
end;
---------------------
DECLARE
buffer DBMS_OUTPUT.CHARARR;
-- type chararr is table of varchar2(32767) index by binary_integer
v_line INTEGER;
begin
v_line:=10;
dbms_output.put_line('line 1');
dbms_output.put_line('line 2');
dbms_output.put_line('line 3');
dbms_output.put_line('line 4');
dbms_output.get_lines(buffer, v_line); --the first par is out, the second is in out
dbms_output.put_line( buffer(3) );
dbms_output.put_line( buffer(2) );
dbms_output.put_line( buffer(1 ));
dbms_output.put_line( buffer(4 ));
end;
/*
to read/write file , we need to create directory
create directory only for sys and system user
1- open sqlplus
2-con as sysdba
3- alter session set container=orclpdb ( the plug db name )
4- create directory mydir as 'F:\TEST';
5- grant READ, WRITE on DIRECTORY MYDIR to public;
6- CREATE THE DIRECTORY ON your computer ( the server )
7-put empty file sample.txt
*/
--take the output for this select and put it in sample.txt
select * from employees;
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MYDIR'; --CAPITAL
l_filename VARCHAR2(100) := 'sample.txt';
l_text VARCHAR2(32767);
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
--Read and output first line.
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line('First Line:' || l_text);
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line('second Line:' || l_text );
--close the file
UTL_FILE.fclose(l_file);
end;
-------------------
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MYDIR'; --CAPITAL
l_filename VARCHAR2(100) := 'sample.txt';
l_text VARCHAR2(32767);
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r');
begin
loop
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line( l_text);
end loop;
exception when no_data_found then
DBMS_OUTPUT.put_line('end file');
end;
--close the file
UTL_FILE.fclose(l_file);
end;
----------------------------------------
--UTL_FILE.put_line
declare
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MYDIR'; --CAPITAL
l_filename VARCHAR2(100) := 'sample2.txt';
begin
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'w');
for i in (select * from DEPARTMENTS)
loop
UTL_FILE.put_line(l_file, i.DEPARTMENT_name);
end loop;
--close the file
UTL_FILE.fclose(l_file);
l_file := UTL_FILE.fopen(l_location, l_filename, 'A');
UTL_FILE.put_line(l_file, 'ADDITIONAL LINES');
UTL_FILE.fclose(l_file);
end;
select * from all_directories;
create or replace procedure read_any_file
(P_dir in varchar2, p_file_name in varchar2)
is
l_file UTL_FILE.file_type;
l_text VARCHAR2(32767);
begin
l_file := UTL_FILE.fopen(P_dir, p_file_name, 'r');
begin
loop
UTL_FILE.get_line(l_file, l_text);
DBMS_OUTPUT.put_line( l_text);
end loop;
exception
when no_data_found then
DBMS_OUTPUT.put_line('end file');
UTL_FILE.fclose(l_file);
end;
exception
when UTL_FILE.invalid_operation then
DBMS_OUTPUT.put_line('can not open the file,invalid file name');
when UTL_FILE.read_error then
DBMS_OUTPUT.put_line('can not be read');
end read_any_file;
execute read_any_file('MYDIR','sample2.txt');
execute read_any_file('MYDIR','sampdddle.txt');
---to see all directories
select * from all_directories;
--1 in order to send email, install UTL_MAIL package ( DBA task/ Pluggable Database)
--connect sys as sysdba and you should execute this on the pluggable db
--@E:\app\kh\product\12.2.0\dbhome_1\rdbms\admin\utlmail.sql
--@E:\app\kh\product\12.2.0\dbhome_1\rdbms\admin\prvtmail.plb
--2 GRANT execute ON utl_mail TO public
--3 add record to ACL T
--the DBMS_NETWORK_ACL_ADMIN package provides the interface to administer
--the network Access Control List (ACL).
--alter system set smtp_out_server = 'mail.xxx.ac.ae:25'
BEGIN
UTL_MAIL.send(sender => 'khaled.khudari@ankabut.ae',
recipients => 'khaled_alkhudari@hotmail.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;
--------------------------------------------------------------
--create directory KHTEST as '/u01';
declare
function get_image (P_DIR VARCHAR2,P_FILE_NAME VARCHAR2)
RETURN RAW
IS
IMAGE raw(32767);
f bfile :=bfilename(P_DIR,P_FILE_NAME);
--BFILENAME returns a BFILE locator that is associated
--with a physical LOB binary file on the server file system
BEGIN
--This procedure opens a BFILE for read-only access.
dbms_lob.fileopen(f,dbms_lob.file_readonly);
IMAGE:= dbms_lob.substr(f);
dbms_lob.close(f);
return IMAGE;
END;
BEGIN
utl_mail.send_attach_raw(sender => 'khaled.khudari@ankabut.ae',
recipients => 'khaled_alkhudari@hotmail.com',
subject => 'Test Mail with Attachment',
message => 'kindly find the attachment',
mime_type => 'text; charset=us-ascii',
attachment=> get_image('KHTEST','jordan.jpg'),
att_inline=>true,
att_mime_type=>'image/jpg',
att_filename=>'jordan.jpg'
);
END;
----------------------------------
declare
function get_file (P_DIR VARCHAR2,P_FILE_NAME VARCHAR2)
RETURN varchar2
IS
v_cont varchar2(32767);
f bfile :=bfilename(P_DIR,P_FILE_NAME);
--BFILENAME returns a BFILE locator that is associated
--with a physical LOB binary file on the server file system
BEGIN
--This procedure opens a BFILE for read-only access.
dbms_lob.fileopen(f,dbms_lob.file_readonly);
v_cont:= utl_raw.cast_to_varchar2 (dbms_lob.substr(f) );
dbms_lob.close(f);
return v_cont;
END;
BEGIN
utl_mail.send_attach_varchar2(sender => 'khaled.khudari@ankabut.ae',
recipients => 'khaled_alkhudari@hotmail.com',
subject => 'Test Mail with text Attachment',
message => 'kindly find the attachment',
mime_type => 'text; charset=us-ascii',
attachment=> get_file('KHTEST','notes.txt'),
att_inline=>false,
att_mime_type=>'text/Plain',
att_filename=>'notes.txt'
);
END;
select * from all_directories;
No comments:
Post a Comment