This is for example to switch port to 8181
begin
dbms_xdb.sethttpport('8181');
end;
How to kill session
--- First you must sure what sid and serial the session
SELECT SID, SERIAL#
FROM V$SESSION
WHERE USERNAME =
--- Kill the session
ALTER SYSTEM KILL SESSION '7,15';
Block TOAD and other tools
CONNECT / AS SYSDBA;
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/
SHOW ERRORS
PL/SQL extract rows to a file
CREATE OR REPLACE PROCEDURE Extraction_Table
(
PC$Table in Varchar2, -- Table name
PC$File in Varchar2, -- Output file name
PC$Directory in Varchar2, -- Outpur Directory name
PC$Delimiter in Varchar2 Default ',', -- delimiter character
PC$Header in Varchar2 Default 'O', -- Output column header
PC$DateFMT in Varchar2 Default 'DD/MM/YYYY', -- Date format
PC$Where in Varchar2 Default Null, -- Where clause
PC$Order in Varchar2 Default Null -- Order by
) IS
LF$Fichier UTL_FILE.FILE_TYPE ;
LC$Ligne Varchar2(32767) ;
LI$I Integer ;
LC$DateFMT Varchar2(40) := '''' || PC$DateFMT || '''' ;
TYPE REFCUR1 IS REF CURSOR ;
cur REFCUR1;
-- Columns --
CURSOR C_COLTAB ( PC$Tab IN VARCHAR2 ) IS
SELECT
COLUMN_NAME,
DATA_TYPE
FROM
USER_TAB_COLUMNS
WHERE
TABLE_NAME = PC$Tab
AND
DATA_TYPE IN ('CHAR','VARCHAR2','NUMBER','DATE','FLOAT')
;
LC$Separateur Varchar2(2) := PC$Delimiter ;
LC$Requete Varchar2(10000) ;
LC$Desc Varchar2(10000) ;
LC$SQLW VARCHAR2(10000):= 'SELECT ';
LC$Col VARCHAR2(256);
-----------------------------
-- Open the output file --
-----------------------------
FUNCTION Ouvrir_fichier
(
PC$Dir in Varchar2,
PC$Nom_Fichier in Varchar2
) RETURN UTL_FILE.FILE_TYPE
IS
Fichier UTL_FILE.FILE_TYPE ;
LC$Msg Varchar2(256);
Begin
Fichier := UTL_FILE.FOPEN( PC$Dir, PC$Nom_Fichier, 'W', 32764 ) ;
If not UTL_FILE.IS_OPEN( Fichier ) Then
LC$Msg := 'Erreur ouverture du fichier (' || PC$Dir || ') ' || PC$Nom_Fichier ;
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
End if ;
Return( Fichier ) ;
Exception
When UTL_FILE.INVALID_PATH Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File location is invalid.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MODE Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The open_mode parameter in FOPEN is invalid.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILEHANDLE Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File handle is invalid.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OPERATION Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File could not be opened or operated on as requested.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.READ_ERROR Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the read operation.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.WRITE_ERROR Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the write operation.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INTERNAL_ERROR then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Unspecified PL/SQL error';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.CHARSETMISMATCH Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'A file is opened using FOPEN_NCHAR,'
|| ' but later I/O operations use nonchar functions such as PUTF or GET_LINE.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.FILE_OPEN Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested operation failed because the file is open.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MAXLINESIZE Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The MAX_LINESIZE value for FOPEN() is invalid;'
|| ' it should be within the range 1 to 32767.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILENAME Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The filename parameter is invalid.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.ACCESS_DENIED Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Permission to access to the file location is denied.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OFFSET Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;'
||' it should be greater than 0 and less than the total number of bytes in the file.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.DELETE_FAILED Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file delete operation failed.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.RENAME_FAILED Then
LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file rename operation failed.';
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When others Then
LC$Msg := 'Erreur : ' || To_char( SQLCODE ) || ' sur ouverture du fichier ('
|| PC$Dir || ') ' || PC$Nom_Fichier ;
RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
End Ouvrir_fichier ;
Begin
-- Open file --
LF$Fichier := Ouvrir_fichier( PC$Directory, PC$File ) ;
-- Output column header ? --
If Upper(PC$Header) = 'O' Then
LI$I := 1 ;
For COLS IN C_COLTAB( PC$Table ) Loop
If LI$I = 1 Then
LC$Ligne := LC$Ligne || COLS.COLUMN_NAME ;
Else
LC$Ligne := LC$Ligne || LC$Separateur || COLS.COLUMN_NAME ;
End if ;
LI$I := LI$I + 1 ;
End loop ;
-- Output column header --
UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
ElsIf Upper(PC$Header) = 'I' Then
LC$Separateur := ',' ;
LC$Desc := 'INSERT INTO ' || PC$Table || ' (' ;
LI$I := 1 ;
For COLS IN C_COLTAB( PC$Table ) Loop
If LI$I = 1 Then
LC$Desc := LC$Desc || COLS.COLUMN_NAME ;
Else
LC$Desc := LC$Desc || LC$Separateur || COLS.COLUMN_NAME ;
End if ;
LI$I := LI$I + 1 ;
End loop ;
LC$Desc := LC$Desc || ' ) VALUES (' ;
End if ;
-- Building of the query --
LI$I := 1 ;
FOR COLS IN C_COLTAB( PC$Table ) LOOP
IF LI$I > 1 THEN
LC$SQLW := LC$SQLW || '||' ;
END IF ;
If COLS.DATA_TYPE IN ('NUMBER','FLOAT') Then
LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',To_char("'
|| COLS.COLUMN_NAME || '"))' ;
ElsIf COLS.DATA_TYPE = 'DATE' Then
If Upper(PC$Header) = 'I' Then
LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL,''NULL'',''to_date(''''''||'
|| 'To_char("' || COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' || '||'''''','''|| LC$DateFMT||''')'')' ;
Else
LC$Col := 'To_char("'|| COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' ;
End if ;
Else
If Upper(PC$Header) = 'I' Then
LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',' || ''''''''''
|| '||"'|| COLS.COLUMN_NAME || '"' || '||' || ''''''''')' ;
Else
LC$Col := '"'|| COLS.COLUMN_NAME || '"' ;
End if ;
End if ;
IF LI$I = 1 THEN
LC$SQLW := LC$SQLW || LC$Col ;
ELSE
LC$SQLW := LC$SQLW || '''' || LC$Separateur || '''' || '||' || LC$Col ;
END IF ;
LI$I := LI$I + 1 ;
END LOOP ;
LC$Requete := LC$SQLW || ' FROM ' || PC$Table ;
If PC$Where is not null Then
-- add the WHERE clause --
LC$Requete := LC$Requete || ' WHERE ' || PC$Where ;
End if ;
If PC$Order is not null Then
-- add the ORDER BY clause --
LC$Requete := LC$Requete || ' ORDER BY ' || PC$Order ;
End if ;
-- Extrac the raws --
Open cur For LC$Requete ;
Loop
Fetch cur Into LC$Ligne ;
Exit when cur%NOTFOUND ;
-- Write to the output file --
If Upper(PC$Header) = 'I' Then
UTL_FILE.PUT_LINE( LF$Fichier, LC$Desc || LC$Ligne || ' );' ) ;
Else
UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
End if ;
End loop ;
Close cur ;
-- Close file --
UTL_FILE.FCLOSE( LF$Fichier ) ;
End Extraction_Table ;
/
Dynamic cursor
Because I like anything which is "dynamic", I have played with the DBMS_SQL package.
My goal was to send any Select order to a procedure and get a collection of records and a datastructure to manipulate these records.
So, this is the trick:
CREATE OR REPLACE PACKAGE PKG_DYNAMIC IS
TYPE T1 IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER ;
TYPE T2 IS TABLE OF T1 INDEX BY BINARY_INTEGER ;
PROCEDURE dynamic_cursor
(
PC$Query IN VARCHAR2,
PT$Cols OUT T2,
PT$Raws OUT T2
) ;
END PKG_DYNAMIC ;
/
CREATE OR REPLACE PACKAGE BODY PKG_DYNAMIC IS
PROCEDURE dynamic_cursor
(
PC$Query IN VARCHAR2,
PT$Cols OUT T2,
PT$Raws OUT T2
)
IS
c NUMBER;
d NUMBER;
col_cnt PLS_INTEGER;
rec_tab dbms_sql.desc_tab;
col_num NUMBER;
LN$Lig PLS_INTEGER := 0 ;
LN$MaxCol PLS_INTEGER := 0 ;
v VARCHAR2(4000) ;
t T1 ;
source_cursor INTEGER;
result INTEGER;
BEGIN
-- retrieve the columns of the query --
c := dbms_sql.open_cursor;
dbms_sql.parse(c, PC$Query , dbms_sql.NATIVE);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);
LN$MaxCol := rec_tab.last ;
For i in rec_tab.first .. rec_tab.last Loop
PT$Cols(i)(1) := rec_tab(i).col_name ; -- name
PT$Cols(i)(2) := rec_tab(i).col_type ; -- type
PT$Cols(i)(3) := rec_tab(i).col_precision ; -- precision
PT$Cols(i)(4) := rec_tab(i).col_scale ; -- scale
PT$Cols(i)(5) := rec_tab(i).col_max_len ; -- length
End loop ;
dbms_sql.close_cursor(c);
-- Prepare a cursor to select from the source table: --
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor, PC$Query, dbms_sql.NATIVE);
-- Define the columns --
For i in 1 .. LN$MaxCol Loop
dbms_sql.DEFINE_COLUMN(source_cursor, i, v,4000);
End loop ;
result := dbms_sql.execute(source_cursor);
-- Fetch the rows from the source query --
LOOP
IF dbms_sql.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row --
LN$Lig := LN$Lig + 1 ;
For i in 1.. LN$MaxCol Loop
dbms_sql.COLUMN_VALUE(source_cursor, i, t(i));
PT$Raws(LN$Lig)(i) := t(i) ;
End loop ;
ELSE
-- No more rows --
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(source_cursor);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(source_cursor) THEN
dbms_sql.close_cursor(source_cursor);
END IF;
RAISE;
END;
END PKG_DYNAMIC ;
/
I created a table to store the Select orders:
SQL> desc fd.queries
Name Null? Type
----------------------------------------- -------- -----------------
NUM NOT NULL NUMBER(3)
QUERY VARCHAR2(512)
SQL> select * from fd.queries ;
NUM QUERY
---------- ----------------------------------------------------------
1 select empno, ename from emp where rownum <= 1
2 select * from emp where rownum <= 2
Then i used this table to test my package
SQL> set serveroutput on
SQL>
SQL> Declare
2 TabCols PKG_DYNAMIC.T2 ;
3 TabRaws PKG_DYNAMIC.T2 ;
4 cursor C is select query from queries where num = 1;
5 Begin
6 For CC in C Loop
7 PKG_DYNAMIC.dynamic_cursor( CC.query, TabCols, TabRaws ) ;
8 dbms_output.put_line( '*** Columns description ***' ) ;
9 For i in TabCols.first .. TabCols.last Loop
10 dbms_output.put_line
11 (
12 ' Name = ' || TabCols(i)(1) ||
13 ' Type = ' || TabCols(i)(2) ||
14 ' Precision = ' || TabCols(i)(3) ||
15 ' Scale = ' || TabCols(i)(4) ||
16 ' Size = ' || TabCols(i)(5)
17 ) ;
18 End loop ;
19 dbms_output.put_line( '*** Datas ***' ) ;
20 For i in TabRaws.first .. TabRaws.last Loop
21 For j in 1 .. TabRaws(i).Last loop
22 dbms_output.put ( TabRaws(i)(j) || ' - ' ) ;
23 End loop ;
24 dbms_output.put_line('') ;
25 End loop ;
26 End loop ;
27 End ;
28
29 /
*** Columns description ***
Name = EMPNO Type = 2 Precision = 4 Scale = 0 Size = 22
Name = ENAME Type = 1 Precision = 0 Scale = 0 Size = 10
*** Datas ***
7369 - SMITH -
PL/SQL procedure successfully completed.
SQL>
DBMS_PROFILER Usage
The DBMS_PROFILER package allows to determine the time taken by each instruction of your code.
At the same time, you could also see the internal processing time for the basic instructions.
Of course, these times depend on the power of the test machine.
On 9i database, you have to run some scripts to install the DBMS_PROFILER package
Connect to SYS
connect / as sysdba
@
@
@
Then run the test with the following instructions:
SQL>
1 DECLARE
2 Pgm NUMBER;
3 BEGIN
4 DBMS_PROFILER.START_PROFILER('test','test1',Pgm);
5 FD.F_TRACE('Hello Oracle world','T');
6 DBMS_PROFILER.STOP_PROFILER;
7 DBMS_PROFILER.ROLLUP_RUN(Pgm);
8 PROF_REPORT_UTILITIES.PRINT_RUN(Pgm);
9* END;
SQL> /
On 10g database, it seems that there is nothing to install
This is a very simple procedure to test the DBMS_PROFILER package :
CREATE OR REPLACE PROCEDURE TEST_PROFILER
IS
TYPE TYP_TV1 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER ;
TYPE TYP_TV2 IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER ;
i1 PLS_INTEGER ;
n1 NUMBER ;
v1 VARCHAR2(2000) ;
t1 TYP_TV1 ;
t2 TYP_TV2 ;
d1 DATE ;
BEGIN
For i IN 1 .. 1000 Loop
i1 := i ;
n1 := i ;
v1 := i ;
v1 := To_char( i ) ;
v1 := Ltrim( To_Char( i ) ) ;
v1 := Replace( To_Char( i ), ' ', '' ) ;
i1 := Instr( v1, '0' ) ;
v1 := Substr( v1, 1, 1 ) ;
t1(i) := i ;
t2(i) := i ;
n1 := Round( i * 1.37 ) ;
n1 := Trunc( i * 1.37 ) ;
n1 := Round( i * 1.37999999999999 ) ;
n1 := Trunc( i * 1.37999999999999 ) ;
d1 := SYSDATE + i ;
End Loop ;
END TEST_PROFILER ;
/
Run the test:
SQL> Begin
2
3 dbms_profiler.start_profiler ('start') ;
4 test_profiler ;
5 dbms_profiler.stop_profiler ;
6
7 End ;
8 /
PL/SQL procedure successfully completed.
Then show the result:
SQL> set linesize 500
SQL>
SQL> column unit_name format a15
SQL> column occured format 999999
SQL> column line# format 99999
SQL> column tot_time format 999.999999
SQL> set linesize 500
SQL>
SQL> select p.unit_name, p.occured, p.tot_time, p.line# line,
2 substr(s.text, 1,150) text
3 from
4 (select u.unit_name, d.TOTAL_OCCUR occured,
5 (to_char(d.TOTAL_TIME/1000000000,'999.999999')) tot_time, d.line#
6 from plsql_profiler_units u, plsql_profiler_data d
7 where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
8 and d.TOTAL_OCCUR >0
9 and u.runid= (select max(runid) from plsql_profiler_units)) p,
10 user_source s
11 where p.unit_name = s.name(+) and p.line# = s.line (+)
12 order by p.unit_name, p.line#;
UNIT_NAME OCCURED TOT_TIME LINE TEXT
--------------- ------- ----------- ---------- ---------------------------------------------------
TEST_PROFILER 1 .000063 1 PROCEDURE TEST_PROFILER
TEST_PROFILER 1001 .000653 13 For i IN 1 .. 1000 Loop
TEST_PROFILER 1000 .001463 16 n1 := i ;
TEST_PROFILER 1000 .005228 17 v1 := i ;
TEST_PROFILER 1000 .005106 18 v1 := To_char( i ) ;
TEST_PROFILER 1000 .007010 19 v1 := Ltrim( To_Char( i ) ) ;
TEST_PROFILER 1000 .007110 20 v1 := Replace( To_Char( i ), ' ', '' ) ;
TEST_PROFILER 1000 .003209 21 i1 := Instr( v1, '0' ) ;
TEST_PROFILER 1000 .003021 22 v1 := Substr( v1, 1, 1 ) ;
TEST_PROFILER 1000 .006956 23 t1(i) := i ;
TEST_PROFILER 1000 .002220 24 t2(i) := i ;
TEST_PROFILER 1000 .006157 25 n1 := Round( i * 1.37 ) ;
TEST_PROFILER 1000 .002388 26 n1 := Trunc( i * 1.37 ) ;
TEST_PROFILER 1000 .002861 27 n1 := Round( i * 1.37999999999999 ) ;
TEST_PROFILER 1000 .001619 28 n1 := Trunc( i * 1.37999999999999 ) ;
TEST_PROFILER 1000 .011119 29 d1 := SYSDATE + i ;
TEST_PROFILER 1 .000065 33 END TEST_PROFILER ;
19 rows selected.
SQL>
As we can see, PLS_INTEGER type is 4/5 times faster than NUMBER
The NESTED TABLE INDEX BY PLS_INTEGER is 3 times faster than the INDEX BY BINARY_INTEGER
Operation on double-precision number is faster than operation on single-precision number
Handle the LDAP with the DBMS_LDAP package
This is a simple PL/SQL package that allows to insert, update and check existence of users in the LDAP.
The package Specification
CREATE OR REPLACE PACKAGE Pkg_Ldap
IS
/*
* Package to handle the LDAP From PL/SQL
* All functions use the DBMS_LDAP package
*
*/
-- User Insert --
FUNCTION Insert_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2 ;
-- User Update --
FUNCTION Modif_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2 ;
-- User Authentification --
FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
RETURN VARCHAR2 ;
-- Add to group --
FUNCTION add_in_group (p_session dbms_ldap.SESSION,
p_group VARCHAR2,
p_user VARCHAR2)
RETURN PLS_INTEGER ;
END Pkg_Ldap;
/
The package body
CREATE OR REPLACE PACKAGE BODY Pkg_Ldap
IS
-- globale variables --
GN$Errcode NUMBER ;
GC$ErrLib VARCHAR2(2000) ;
GC$ldap_host VARCHAR2(256) := 'company.my_dc.org'; -- your LDAP Host url
GC$ldap_port VARCHAR2(256) := '3060'; -- your LDAP Port
GC$ldap_user VARCHAR2(256) := 'cn=orcladmin';
GC$ldap_passwd VARCHAR2(256) := 'welcome1';
GC$ldap_base VARCHAR2(256) := 'cn=my_cn,dc=my_dc,dc=fr';
GC$Groupe VARCHAR2(200) := 'cn=authenticated_users,cn=portal_groups,cn=groups,dc=my_dc,dc=fr';
-------------------------
-- Insert a new user --
-------------------------
FUNCTION Insert_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2
IS
retval PLS_INTEGER;
my_session DBMS_LDAP.SESSION;
emp_dn VARCHAR2(256);
emp_array DBMS_LDAP.MOD_ARRAY;
emp_vals DBMS_LDAP.STRING_COLLECTION;
BEGIN
retval := -1;
-- Insert into LDAP --
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);
DBMS_LDAP.USE_EXCEPTION := TRUE;
my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);
DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');
retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);
DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));
emp_array := DBMS_LDAP.create_mod_array(20);
-- Properties --
emp_vals(1) := login;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'uid',emp_vals);
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'cn',emp_vals);
emp_vals(1) := name;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'sn',emp_vals);
emp_vals(1) := givenname;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'givenname',emp_vals);
emp_vals(1) := pwd;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'userpassword',emp_vals);
emp_vals(1) := company;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'st',emp_vals);
emp_vals(1) := title;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'title',emp_vals);
emp_vals(1) := address;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'homepostaladdress',emp_vals);
emp_vals(1) := street;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'street',emp_vals);
emp_vals(1) := cp;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'postalcode',emp_vals);
emp_vals(1) := city;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'l',emp_vals);
emp_vals(1) := tel;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'telephonenumber',emp_vals);
emp_vals(1) := mobile;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mobile',emp_vals);
emp_vals(1) := mail;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mail',emp_vals);
emp_vals(1) := 'top';
emp_vals(2) := 'person';
emp_vals(3) := 'organizationalPerson';
emp_vals(4) := 'inetOrgPerson';
emp_vals(5) := 'orcluser';
emp_vals(6) := 'orcluserv2';
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'objectclass',emp_vals);
emp_dn := 'cn=' || login || ', ' || GC$ldap_base;
DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') ||': [' || emp_dn || ']');
retval := DBMS_LDAP.add_s(my_session,emp_dn,emp_array);
DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ') || ': '|| TO_CHAR(retval));
DBMS_LDAP.free_mod_array(emp_array);
-- Add the user to the portal group --
retval := add_in_group (my_session, GC$Groupe, emp_dn) ;
retval := DBMS_LDAP.unbind_s(my_session);
DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' ||
TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE('Resultt -> OK');
RETURN 'OK' ;
-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
GN$ErrCode := SQLCODE ;
GC$ErrLib := SQLERRM ;
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN ( GC$ErrLib ) ;
END Insert_User;
-----------------------------
-- Update existing user --
-----------------------------
FUNCTION Modif_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2
IS
retval PLS_INTEGER;
my_session DBMS_LDAP.SESSION;
emp_session DBMS_LDAP.SESSION;
emp_dn VARCHAR2(256);
emp_array DBMS_LDAP.MOD_ARRAY;
emp_vals DBMS_LDAP.STRING_COLLECTION;
BEGIN
retval := -1;
emp_dn := 'cn=' || login || ', ' || GC$ldap_base ;
DBMS_OUTPUT.PUT('ADD Example: ');
DBMS_OUTPUT.PUT_LINE('Connecting to directory .. ');
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);
-- Unabling exceptions --
DBMS_LDAP.USE_EXCEPTION := TRUE;
-- Initialization --
my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);
DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');
-- Connexion au LDAP --
retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);
DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));
-- Tableau pour les modifications --
emp_array := DBMS_LDAP.create_mod_array(20);
-- Populate the varray --
IF login IS NOT NULL THEN
emp_vals(1) := login;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'uid',emp_vals);
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'cn',emp_vals);
END IF ;
IF name IS NOT NULL THEN
emp_vals(1) := name;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'sn',emp_vals);
END IF ;
IF givenname IS NOT NULL THEN
emp_vals(1) := givenname;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'givenname',emp_vals);
END IF ;
IF company IS NOT NULL THEN
emp_vals(1) := company;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'st',emp_vals);
END IF ;
IF address IS NOT NULL THEN
emp_vals(1) := address;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'homepostaladdress',emp_vals);
END IF ;
IF street IS NOT NULL THEN
emp_vals(1) := street;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'street',emp_vals);
END IF ;
IF cp IS NOT NULL THEN
emp_vals(1) := cp;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'postalcode',emp_vals);
END IF ;
IF city IS NOT NULL THEN
emp_vals(1) := city;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'l',emp_vals);
END IF ;
IF title IS NOT NULL THEN
emp_vals(1) := title;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'title',emp_vals);
END IF ;
IF tel IS NOT NULL THEN
emp_vals(1) := tel;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'telephonenumber',emp_vals);
END IF ;
IF mobile IS NOT NULL THEN
emp_vals(1) := mobile;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mobile',emp_vals);
END IF ;
IF pwd IS NOT NULL THEN
emp_vals(1) := pwd;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'userpassword',emp_vals);
END IF ;
IF mail IS NOT NULL THEN
emp_vals(1) := mail;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mail',emp_vals);
END IF ;
-- Update LDAP --
retval := DBMS_LDAP.modify_s(my_session,emp_dn,emp_array);
DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' || TO_CHAR(retval));
-- Free the varray --
DBMS_LDAP.free_mod_array(emp_array);
-- Logout from LDAP --
retval := DBMS_LDAP.unbind_s(my_session);
DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' || TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE('Résultat -> OK');
RETURN 'OK' ;
-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
GN$ErrCode := SQLCODE ;
GC$ErrLib := SQLERRM ;
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN ( GC$ErrLib ) ;
END Modif_User;
/*
*****************************************************************
-- Function : add_in_group
-- Parameters : group name / user name (DN) / session
-- Return : 0 = Ok / others KO
*****************************************************************
*/
FUNCTION add_in_group (p_session dbms_ldap.SESSION,
p_group VARCHAR2,
p_user VARCHAR2)
RETURN PLS_INTEGER IS
my_vals dbms_ldap.string_collection;
v_array dbms_ldap.mod_array;
retval PLS_INTEGER;
BEGIN
-- Initialize the pl/sql table for the new entry
my_vals(1) := p_user;
-- Initialize the varray for the modify command
v_array := dbms_ldap.create_mod_array(num => 1);
IF v_array = NULL THEN
dbms_output.put_line('Error add_in_group: v_array not initialized.');
NULL;
END IF;
dbms_output.put_line ('v_array initialisee avec succes.');
-- Populate the varray
dbms_ldap.populate_mod_array(v_array,
dbms_ldap.mod_add,
'uniquemember',
my_vals);
dbms_output.put_line(p_user);
-- Group Modification
retval := dbms_ldap.modify_s(p_session, p_group, v_array);
-- Free the varray
dbms_ldap.free_mod_array(v_array);
RETURN retval;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('add_in_group : '|| SQLCODE||' '||SQLERRM);
RETURN -1 ;
END add_in_group;
-----------------------------
-- User Authentification --
-----------------------------
FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
RETURN VARCHAR2
IS
ldap_host VARCHAR2(256);
ldap_port PLS_INTEGER;
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
retval PLS_INTEGER;
my_session DBMS_LDAP.SESSION;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
sub_type PLS_INTEGER;
subscriber_id VARCHAR2(2000);
my_pset_coll DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
my_property_names DBMS_LDAP.STRING_COLLECTION;
my_property_values DBMS_LDAP.STRING_COLLECTION;
user_handle DBMS_LDAP_UTL.HANDLE;
user_id VARCHAR2(2000);
user_type PLS_INTEGER;
user_password VARCHAR2(2000);
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
user_dn VARCHAR2(256);
BEGIN
-- Please customize the following variables as needed
ldap_user := 'cn=orcladmin';
ldap_passwd := 'welcome1';
sub_type := DBMS_LDAP_UTL.TYPE_DEFAULT;
subscriber_id := NULL;
user_type := DBMS_LDAP_UTL.TYPE_NICKNAME;
user_id := p_user;
user_password := p_pwd;
-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;
DBMS_OUTPUT.PUT_LINE('Input Parameters: ');
DBMS_OUTPUT.PUT_LINE('LDAP HOST: ' || GC$ldap_host );
DBMS_OUTPUT.PUT_LINE('LDAP PORT: ' || GC$ldap_port);
DBMS_OUTPUT.PUT_LINE('LDAP BIND USER: ' || ldap_user);
DBMS_OUTPUT.PUT_LINE('USER ID : ' || user_id);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
-----------------------------------------------
-- Connect to the LDAP server
-- and obtain and ld session.
-----------------------------------------------
DBMS_OUTPUT.PUT('Connecting to ' || GC$ldap_host || ' ...');
my_session := DBMS_LDAP.init(GC$ldap_host, GC$ldap_port);
DBMS_OUTPUT.PUT_LINE(': Connected.');
-----------------------------------------------
-- Bind to the directory
-----------------------------------------------
DBMS_OUTPUT.PUT('Binding to directory as ' || ldap_user || ' ... ');
retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Create Subscriber Handle
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Creating Realm Handle ... ');
retval := DBMS_LDAP_UTL.create_subscriber_handle(subscriber_handle,
sub_type,
subscriber_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
RETURN('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
END IF;
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Create User Handle
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Creating user handle for ' || user_id || ' ... ');
retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: create_user_handle returns : ' || TO_CHAR(retval));
RETURN('Error: create_user_handle returns : ' || TO_CHAR(retval));
END IF;
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Set user handle properties
-- (link subscriber to user )
---------------------------------------------------------------------
retval := DBMS_LDAP_UTL.set_user_handle_properties(user_handle,
DBMS_LDAP_UTL.SUBSCRIBER_HANDLE,
subscriber_handle);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
RETURN('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
END IF;
---------------------------------------------------------------------
-- Authenticate User
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Authenticating user ' || user_id || ' ... ');
retval := DBMS_LDAP_UTL.authenticate_user(my_session,
user_handle,
DBMS_LDAP_UTL.AUTH_SIMPLE,
user_password,
NULL);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Authentification error : ' || TO_CHAR(retval));
IF retval = -5 THEN
RETURN('User unknown');
ELSIF retval = -16 THEN
RETURN ('Incorrect password');
ELSIF retval = DBMS_LDAP_UTL.PARAM_ERROR THEN
RETURN (' Invalid input parameters.');
ELSIF retval = DBMS_LDAP_UTL.GENERAL_ERROR THEN
RETURN (' Authentication failed.');
ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_USER THEN
RETURN (' USER doesn''t exist.');
ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_USER_ENTRIES THEN
RETURN (' Multiple NUMBER OF USER DN entries exist IN the DIRECTORY FOR the given USER.');
ELSIF retval = DBMS_LDAP_UTL.INVALID_SUBSCRIBER_ORCL_CTX THEN
RETURN (' Invalid Subscriber Oracle Context.');
ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_SUBSCRIBER THEN
RETURN (' Subscriber doesn''t exist.');
ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_SUBSCRIBER_ENTRIES THEN
RETURN (' Multiple NUMBER OF subscriber DN entries exist IN the DIRECTORY FOR the given subscriber.');
ELSIF retval = DBMS_LDAP_UTL.INVALID_ROOT_ORCL_CTX THEN
RETURN (' Invalid Root Oracle Context.');
ELSIF retval = DBMS_LDAP_UTL.AUTH_PASSWD_CHANGE_WARN THEN
RETURN (' PASSWORD should be changed.');
ELSIF retval = DBMS_LDAP_UTL.ACCT_TOTALLY_LOCKED_EXCEPTION THEN
RETURN (' USER account IS locked.');
ELSIF retval = DBMS_LDAP_UTL.PWD_EXPIRED_EXCEPTION THEN
RETURN (' USER PASSWORD has expired.');
ELSIF retval = DBMS_LDAP_UTL.PWD_GRACELOGIN_WARN THEN
RETURN (' Grace login FOR USER.');
ELSE
RETURN('Authentification error : ' || TO_CHAR(retval));
END IF ;
ELSE
DBMS_OUTPUT.PUT_LINE(': Successful.');
END IF;
------------------------------------------
-- Free Mod Propertyset
------------------------------------------
DBMS_LDAP_UTL.free_mod_propertyset(my_mod_pset);
---------------------------------------------------------------------
-- Free handles
---------------------------------------------------------------------
DBMS_LDAP_UTL.free_handle(subscriber_handle);
DBMS_LDAP_UTL.free_handle(user_handle);
-- unbind from the directory
DBMS_OUTPUT.PUT('Unbinding from directory ... ');
retval := DBMS_LDAP.unbind_s(my_session);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('unbind_s returns : ' || TO_CHAR(retval));
RETURN('unbind_s returns : ' || TO_CHAR(retval));
ELSE
DBMS_OUTPUT.PUT_LINE(': Successful.');
END IF;
RETURN( 'OK' ) ;
-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN SQLERRM ;
END;
END Pkg_Ldap;
/
Calling from PL/SQL
--------------------
-- Insert entry --
--------------------
Declare
LC$Return Varchar2(2000) ;
BEGIN
LC$Return := PKG_LDAP.Insert_User
(
'f.degrelle@free.fr',
'musicisthebest',
'degrelle',
'francois',
'znort',
'researcher',
'analyst',
'10 World place',
'',
'75000',
'Paris',
'0102030405',
'0606060606',
'f.degrelle@free.fr'
);
If LC$Return <> 'OK' Then
message( LC$Return ) ;
End if ;
END;
--------------------
-- Update entry --
--------------------
DECLARE
LC$Return Varchar2(2000) ;
BEGIN
LC$Return := PKG_LDAP.Modif_User
(
'f.degrelle@free.fr',
'musicisthebest',
'degrelle',
'francois',
'znort',
'researcher',
'analyst',
'10 World place',
'',
'75000',
'Paris',
'0102030405',
'0606060606',
'f.degrelle@free.fr'
);
If LC$Return <> 'OK' Then
message( LC$Return ) ;
End if ;
END;
Calling from Java
/*---------------------------------------
* Check existence of user in the LDAP
*--------------------------------------*/
public String Check_User (OracleConnection conn, String p_user, String p_pwd)
throws Exception, SQLException
{
String sReturn = "" ;
// Call the stored function
CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Existe_User (?,?)}");
call.registerOutParameter (1, Types.VARCHAR);
call.setString (2, p_user);
call.setString (3, p_pwd);
call.execute ();
sReturn = call.getString (1) ;
if( ! sReturn.equals("OK") ) System.out.println("*** Unknown user *** " + sReturn) ;
call.close();
return( sReturn ) ;
}
/*-------------------------
* Update the LDAP
*------------------------*/
public String SetUserInfo (OracleConnection conn, int iUtiId)
throws Exception, SQLException
{
String sReturn = "" ;
// Call the stored function
CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Modif_User (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
call.registerOutParameter (1, Types.VARCHAR);
call.setString (2, "login");
call.setString (3, "pwd");
call.setString (4, "name");
call.setString (5, "givenname");
call.setString (6, "company");
call.setString (7, "title");
call.setString (8, "function");
call.setString (9, "address");
call.setString (10, "street");
call.setString (11, "cp");
call.setString (12, "city");
call.setString (13, "tel");
call.setString (14, "mobile");
call.setString (15, "mail");
call.execute ();
sReturn = call.getString (1) ;
if( ! sReturn.equals("OK") ) System.out.println("*** LDAP update error *** " + sReturn) ;
call.close();
return sReturn ;
}
You can download the source code here
Function Hex to decimal
CREATE OR REPLACE FUNCTION hex_to_dec ( pc$hex IN VARCHAR2 )
RETURN PLS_INTEGER
IS
hexch VARCHAR2(40) := UPPER( pc$hex) ;
len PLS_INTEGER := LENGTH( hexch) ;
res PLS_INTEGER := 0 ;
pos PLS_INTEGER := 0 ;
val PLS_INTEGER ;
car VARCHAR2(1) ;
BEGIN
FOR i IN REVERSE 1..len LOOP
car := SUBSTR( hexch, i , 1 ) ;
IF ASCII(car) > 57 THEN
val := ASCII(car) - 55 ;
ELSE
val := ASCII(car) - 48 ;
END IF ;
res := res + (val * ( POWER(16,pos) ) ) ;
pos := pos + 1 ;
END LOOP ;
RETURN( res ) ;
END;
/
Output BLOB columns into file system
It is frequently asked how to load a BLOB table column from the content of an external file.
This can be achieved by using the DBMS_LOB package's functions.
Sometimes it is also useful to extract the BLOB content back to a file.
Here is a procedure that achieves this task:
(Available since Oracle database 9i, that introduced the UTL_FILE.PUT_RAW() function)
CREATE OR REPLACE PROCEDURE Write_Binary_file
(
PC$Directory IN VARCHAR2
,PC$File_Name IN VARCHAR2
,PC$SQL_Order IN VARCHAR2
,PB$Raise IN BOOLEAN DEFAULT FALSE
)
-- -------------------------------------------
-- Procedure to dump a BLOB column onto a file
-- -------------------------------------------
-- parameters:
-- PC$Directory : name of an existing Oracle Directory
-- PC$File_Name : name of the expected output file
-- PC$SQL_Order : SQL order to select the BLOB column
-- PB$Raise : boolean to indicate if the process
-- would be stopped after an error
--
IS
src_lob BLOB;
buffer RAW(16384);
amt BINARY_INTEGER := 16384;
pos INTEGER := 1;
LF$FicOUT UTL_FILE.FILE_TYPE ;
LC$Msg VARCHAR2(2000) ;
BEGIN
-- get the BLOB column --
BEGIN
EXECUTE IMMEDIATE PC$SQL_Order INTO src_lob ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LC$Msg := 'Write_Binary_File(): NO_DATA_FOUND' ;
IF PB$Raise THEN
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
END IF ;
Dbms_Output.Put_Line(LC$Msg) ;
RETURN ;
END ;
-- open the output file --
LF$FicOUT := UTL_FILE.FOPEN( PC$Directory, PC$File_Name, 'W', 32764 ) ;
-- write the file --
LOOP
-- read the chunks --
Dbms_Lob.READ (src_lob, amt, pos, buffer);
-- write the chunks --
Utl_File.Put_Raw(LF$FicOut, buffer);
pos := pos + amt;
END LOOP;
-- close the file --
Utl_File.Fclose(LF$FicOut);
EXCEPTION
WHEN NO_DATA_FOUND THEN
utl_file.fclose(LF$FicOut);
WHEN OTHERS THEN
LC$Msg := 'Write_Binary_File() Error : ' || TO_CHAR( SQLCODE ) || ' while managing file ('
|| PC$Directory || ') ' || PC$File_Name ;
IF PB$Raise THEN
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
END IF ;
Dbms_Output.Put_Line(LC$Msg);
END Write_Binary_file;
/
Here is a calling sample:
BEGIN
Write_Binary_file
(
'OUTPUT_DIRECTORY'
,'image.jpg'
,'select image from images where id=3'
);
END;
Encrypt/decrypt strings with dbms_obfuscation_toolkit package
CREATE OR REPLACE PACKAGE CryptStr AS
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW;
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2;
END Cryptit;
/
CREATE OR REPLACE PACKAGE BODY CryptStr AS
crypt_raw RAW(2000);
crypt_str VARCHAR(2000);
-- Encrypt the string --
FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW AS
l INTEGER := LENGTH(str);
i INTEGER;
padblock RAW(2000);
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');
BEGIN
i := 8-MOD(l,8);
padblock := utl_raw.cast_to_raw(str||RPAD(CHR(i),i,CHR(i)));
dbms_obfuscation_toolkit.DESEncrypt(
input => padblock,
KEY => Cle,
encrypted_data => crypt_raw );
RETURN crypt_raw ;
END;
-- Decrypt the string --
FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2 AS
l NUMBER;
Cle RAW(8) := UTL_RAW.CAST_TO_RAW('frankzap');
crypt_raw RAW(2000) := utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(xCrypt)) ;
BEGIN
dbms_obfuscation_toolkit.DESDecrypt(
input => xCrypt,
KEY => Cle,
decrypted_data => crypt_raw );
crypt_str := utl_raw.cast_to_varchar2(crypt_raw);
l := LENGTH(crypt_str);
crypt_str := RPAD(crypt_str,l-ASCII(SUBSTR(crypt_str,l)));
RETURN crypt_str;
END;
END CryptStr;
/
Simple Send SMS
CREATE OR REPLACE PROCEDURE sendSMS
( pRecipient IN VARCHAR2
, pBody IN VARCHAR2
)
IS
ESENDEX_USERNAME CONSTANT VARCHAR2(40) := 'your_username';
ESENDEX_PASSWORD CONSTANT VARCHAR2(40) := 'your_password';
ESENDEX_ACCOUNT CONSTANT VARCHAR2(40) := 'your_account';
--
vRequest Utl_Http.req;
vPostText VARCHAR2(500);
vResponse Utl_Http.resp;
vResponseText VARCHAR2(2000);
vErrorText VARCHAR2(200);
BEGIN
----------------------------------------------------------------------------
-- Build text for the post action.
-- For a field description, see
-- http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx
----------------------------------------------------------------------------
vPostText :=
'EsendexPlainText=YES' ||CHR(38)||
'EsendexUsername=' ||Utl_Url.escape(ESENDEX_USERNAME, TRUE)||CHR(38)||
'EsendexPassword=' ||Utl_Url.escape(ESENDEX_PASSWORD, TRUE)||CHR(38)||
'EsendexAccount=' ||Utl_Url.escape(ESENDEX_ACCOUNT, TRUE)||CHR(38)||
'EsendexRecipient='||Utl_Url.escape(pRecipient, TRUE)||CHR(38)||
'EsendexBody=' ||Utl_Url.escape(pBody, TRUE);
----------------------------------------------------------------------------
-- if you need to set a proxy, uncomment next line.
----------------------------------------------------------------------------
/* Utl_Http.set_proxy('proxy.it.my-company.com', 'my-company.com'); */
----------------------------------------------------------------------------
-- Send SMS through the Esendex SMS service.
----------------------------------------------------------------------------
vRequest := Utl_Http.begin_request
( url => 'http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx'
, method => 'POST'
);
Utl_Http.set_header
( r => vRequest
, name => 'Content-Type'
, value => 'application/x-www-form-urlencoded'
);
Utl_Http.set_header
( r => vRequest
, name => 'Content-Length'
, value => LENGTH(vPostText)
);
Utl_Http.write_text
( r => vRequest
, data => vPostText
);
vResponse := Utl_Http.get_response(vRequest);
IF vResponse.status_code = '200'
THEN
Utl_Http.read_text(vResponse, vResponseText);
--
IF vResponseText NOT LIKE 'Result=OK%'
THEN
vErrorText := vResponseText;
END IF;
ELSE
vErrorText := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
END IF;
--
Utl_Http.end_response(vResponse);
--
IF vErrorText IS NOT NULL
THEN
RAISE_APPLICATION_ERROR(-20001, 'Sending SMS failed with '||vErrorText);
END IF;
END sendSMS;
Simple Mail Message
CREATE OR REPLACE PROCEDURE SimpleTextMessage IS
mailHOST VARCHAR2(64) := 'mail.yourmailserver.com';
mailFROM VARCHAR2(64);
mailTO VARCHAR2(64);
mailCONN utl_smtp.connection;
mailDATE VARCHAR2(20);
vreply utl_smtp.reply;
vreplies utl_smtp.replies;
i number;
c utl_smtp.connection;
BEGIN
mailFROM := 'you@mail.yourmailserver.com';
mailTO := 'freemail@yahoo.com';
mailDATE := TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS');
mailCONN := utl_smtp.open_connection(mailHOST);
utl_smtp.ehlo(mailCONN, mailHOST);
utl_smtp.mail(mailCONN, mailFROM);
utl_smtp.rcpt(mailCONN, mailTO);
utl_smtp.open_data(mailCONN);
utl_smtp.write_data(mailCONN, 'Subject: '|| 'CR EOD Notification' || chr(13)|| chr(10));
utl_smtp.write_data(mailCONN, 'Hello Friend.' || chr(13)|| chr(10));
utl_smtp.write_data(mailCONN, 'Drop me an e-mail.' || chr(13));
utl_smtp.close_data(mailCONN);
utl_smtp.quit(mailCONN);
END;
/
Subscribe to:
Posts (Atom)