Mercredi 18 janvier 2006

Kind of LONG_RAW_TO_VARCHAR2 function

I have just seen a recent Laurent Schneider's blog entry about selecting from a LONG column
http://laurentschneider.blogspot.com/2006/01/select-from-test-where-mylong-like.html.

Then i remember another question on this same forum where the guy need to read some strings stored in a LONG RAW column
(don't ask me why !)
Then i provided the following solution, a kind of LONG_RAW_TO_VARCHAR2 function

SQL*Plus: Release 9.0.1.3.0 - Production on Me Jan 18 11:56:04 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connecté à :
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL>
SQL>
SQL> CREATE TABLE TEST_LONGRAW ( num NUMBER(3), lr LONG RAW )
  2  /

Table créée.

SQL> INSERT INTO TEST_LONGRAW VALUES (1, RAWTOHEX('This is some text in a long raw column'))
  2  /

1 ligne créée.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     ch VARCHAR2(1000);
  3  BEGIN
  4    SELECT lr INTO ch FROM TEST_LONGRAW WHERE num = 1 ;
  5    dbms_output.put_line( ch ) ;
  6  END;
  7  /
5468697320697320736F6D65207465787420696E2061206C6F6E672072617720636F6C756D6E

Procédure PL/SQL terminée avec succès.

SQL>

SQL> DECLARE
  2    LC$hex  VARCHAR2(4000) ;
  3    res     VARCHAR2(4000) ;
  4    j       PLS_INTEGER := 1 ;
  5    car     VARCHAR2(2) ;
  6  BEGIN
  7    SELECT lr
  8    INTO   LC$Hex
  9    FROM   TEST_LONGRAW
 10    WHERE  NUM = 1 ;
 11    FOR i IN 1 .. LENGTH( LC$hex ) / 2 LOOP
 12      car := SUBSTR( LC$hex, j, 2 ) ;
 13      res :=res || CHR(TO_NUMBER(car,'XXXXX')) ;
 14      j := j + 2 ;
 15    END LOOP ;
 16    dbms_output.put_line( res ) ;  
 17  END ;
 18  /
This is some text in a long raw column

Procédure PL/SQL terminée avec succès.

SQL>

Francois

Par Francois Degrelle - Publié dans : Oracle PL/SQL
Ecrire un commentaire - Voir les 0 commentaires - Recommander
Mardi 10 janvier 2006

Hex to decimal

I have just read the Jeff Moss article concerning Hex to decimal conversion and vice versa.

In this article we can find some great conversions routines.

This is one that allows to convert hexadecimal numbers greatest than 255 to a decimal format:

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;
/

SQL> SELECT hex_to_dec('FFFF') FROM dual
  2  /

HEX_TO_DEC('FFFF')
------------------
             65535

SQL>

Francois

Par Francois Degrelle - Publié dans : Oracle PL/SQL
Ecrire un commentaire - Voir les 1 commentaires - Recommander
Mardi 6 décembre 2005

A "StringTokenizer like" PL/SQL function

This is a small function that allows to extract tokens from a string.

CREATE OR REPLACE FUNCTION Split
(
   PC$Chaine IN VARCHAR2,         -- input string
   PN$Pos IN PLS_INTEGER,         -- token number
   PC$Sep IN VARCHAR2 DEFAULT ',' -- separator character
)
RETURN VARCHAR2
IS
  LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
  LI$I      PLS_INTEGER ;
  LI$I2     PLS_INTEGER ;
BEGIN
  LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
  IF LI$I > 0 THEN
    LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
    IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;
    RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ) ) ;
  ELSE
    RETURN NULL ;
  END IF ;
END;
/


SQL> DECLARE
  2    LC$String  VARCHAR2(50) := 'one|two|three|four|five|six|seven' ;
  3    LC$Token   VARCHAR2(100) ;
  4    i          PLS_INTEGER := 1 ; 
  5  BEGIN
  6    LOOP
  7      LC$Token := Split( LC$String, i , '|') ;
  8      EXIT WHEN LC$Token IS NULL ;
  9      dbms_output.put_line( LC$Token ) ;
 10      i := i + 1 ;
 11    END LOOP ;
 12  END ;
 13 
 14  /
one
two
three
four
five
six
seven

Procédure PL/SQL terminée avec succès.

SQL>

Francois

Par Francois Degrelle - Publié dans : Oracle PL/SQL
Ecrire un commentaire - Voir les 0 commentaires - Recommander
Jeudi 1 décembre 2005

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

Francois
Par Francois Degrelle - Publié dans : Oracle PL/SQL
Ecrire un commentaire - Voir les 10 commentaires - Recommander
Mercredi 23 novembre 2005

How to calculate average on DATE columns ?

I have seen an interresting question on a forum about how to calculate average on DATE columns.

It is not possible to calculate average on non-numeric values:

SQL> SELECT AVG( creation ) FROM paragraphes
  2  /
SELECT AVG( creation ) FROM paragraphes
            *
ERREUR à la ligne 1 :
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL>

So you have to convert the date to a numeric value:

SQL> SELECT TO_CHAR( creation ) FROM PARAGRAPHES WHERE ROWNUM <= 5
  2  /

TO_CHAR(CR
----------
18/02/2004
25/02/2004
04/03/2004
04/03/2004
17/03/2004

SQL> SELECT TO_DATE(TRUNC(AVG( TO_CHAR(creation,'J'))),'J') FROM PARAGRAPHES
  2  WHERE ROWNUM <= 5
  3  /

TO_DATE(TR
----------
02/03/2004

SQL>

Francois

Par Francois Degrelle - Publié dans : Oracle PL/SQL
Ecrire un commentaire - Voir les 0 commentaires - Recommander
Blog : Marketing sur over-blog.com - Contact - C.G.U. - Rémunération en droits d'auteur - Signaler un abus