Overblog Suivre ce blog
Administration Créer mon blog

Search

Free tool

Look and Feel Project

22 octobre 2005 6 22 /10 /octobre /2005 00:00

This is a Javabean component that allows to display a Jslider



Read the Forms-Javabean : a JSlide component in your Forms application

Francois





Repost 0
Francois Degrelle - dans Oracle Forms
commenter cet article
21 octobre 2005 5 21 /10 /octobre /2005 00:00

This is a Javabean component that allows to display a JfileChooser dialog box with image preview



Read the Forms-Javabean Open-dialog box with preview image

Francois



Repost 0
Francois Degrelle - dans Oracle Forms
commenter cet article
21 octobre 2005 5 21 /10 /octobre /2005 00:00

Sometimes, on forums, I see questions about dynamic SQL.
This is one of them:

"Hello,
I would like to execute a function wich name is stored in a string.
How could I do ?"

The solution for this kind of problem is allways the dynamic SQL:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE OR REPLACE FUNCTION text_xx RETURN VARCHAR2
  2  IS
  3  BEGIN
  4    RETURN 'Hello' ;
  5  END ;
  6  /

Function created.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    LC$Proc   VARCHAR2(100) := 'text_xx' ;
  3    LC$Return VARCHAR2(100) ;
  4  BEGIN
  5    EXECUTE IMMEDIATE 'SELECT ' || LC$Proc || ' FROM DUAL' INTO LC$Return ;
  6    Dbms_Output.put_Line( LC$Return ) ;
  7  END ;
  8 
  9  /
Hello

PL/SQL procedure successfully completed.

SQL>


Francois
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
19 octobre 2005 3 19 /10 /octobre /2005 00:00

This is a new Forms-Javabean tutorial that shows how to display in a Forms application buttons with HTML labels



 Forms-Javabean button with HTML label

Francois





Repost 0
Francois Degrelle - dans Oracle Forms
commenter cet article
18 octobre 2005 2 18 /10 /octobre /2005 00:00

ON NUMBERS

This function allows to make two types of action on a number: 

- Keep the number of wished decimals (precision > = 0) 
- Centre the whole part of a number in a range (precision < 0) 

Example of preservation of the decimal part

<pre>SQL> select
  2   trunc(12.98764, 5) "+5 dec."
  3  ,trunc(12.98764, 4) "+4 dec."
  4  ,trunc(12.98764, 3) "+3 dec."
  5  ,trunc(12.98764, 2) "+2 dec."
  6  ,trunc(12.98764, 1) "+1 dec."
  7  ,trunc(12.98764, 0) "+0 dec."
  8   from dual ;

   +5 dec.    +4 dec.    +3 dec.    +2 dec.    +1 dec.    +0 dec.
---------- ---------- ---------- ---------- ---------- ----------
  12,98764    12,9876     12,987      12,98       12,9         12

SQL></pre>


Example of centring by ranges

<pre>SQL> select
  2   trunc(1234567,-6) "million"
  3  ,trunc(1234567,-5) "cent-mille"
  4  ,trunc(1234567,-4) "dix-mille"
  5  ,trunc(1234567,-3) "mille"
  6  ,trunc(1234567,-2) "cent"
  7  ,trunc(1234567,-1) "dix"
  8  from dual ;

   million cent-mille  dix-mille      mille       cent        dix
---------- ---------- ---------- ---------- ---------- ----------
   1000000    1200000    1230000    1234000    1234500    1234560

SQL></pre>


Extraction of the entire and decimal parts of a number

<pre>SQL> DECLARE
  2    LN$Num number := 100.95 ;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE( 'Entire part  : ' || To_char( TRUNC ( LN$Num ) )) ;
  5    DBMS_OUTPUT.PUT_LINE( 'Decimal part : ' || To_char( LN$Num - TRUNC (LN$Num ) ) ) ;
  6  END ;
  7  /
Entire part  : 100
Decimal part : ,95

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

SQL></pre>



ON DATES

<pre>SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;

Session modifiée.

SQL>
SQL> -- Current date --
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

Curent date
-------------------                                                             
08/10/2004 14:08:48                                                             

SQL>
SQL> -- date truncated to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;

'YEAR'                                                                         
-------------------                                                             
01/01/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;

'IYYY'                                                                         
-------------------                                                             
29/12/2003 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the quarter --
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;

'Q'                                                                             
-------------------                                                             
01/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;

'MONTH'                                                                         
-------------------                                                             
01/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the week --
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'                                                                           
-------------------                                                             
04/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'W')  "'W'" FROM DUAL;

'W'                                                                             
-------------------                                                             
08/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'WW')  "'WW'" FROM DUAL;

'WW'                                                                           
-------------------                                                             
07/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IW')  "'IW'" FROM DUAL;

'IW'                                                                           
-------------------                                                             
04/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the day (suppress hours) --
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;

'DD'                                                                           
-------------------                                                             
08/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the hour (suppress minutes) --
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;

'HH'                                                                           
-------------------                                                             
08/10/2004 14:00:00                                                             

SQL>
SQL> -- date truncated to the minute (suppress seconds) --
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;

'MI'                                                                           
-------------------                                                             
08/10/2004 14:08:00                                                             

SQL></pre>



Francois
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
15 octobre 2005 6 15 /10 /octobre /2005 00:00
Repost 0
Francois Degrelle - dans Other
commenter cet article
15 octobre 2005 6 15 /10 /octobre /2005 00:00

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

@<ORACLE_HOME>/rdbms/admin/proftab.sql
@<ORACLE_HOME>/rdbms/admin/profload.sql
@<ORACLE_HOME>/plsql/demo/profrep.sql


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
--------------- ------- ----------- ---------- ---------------------------------------------------
<anonymous>           2     .000084          4
<anonymous>           1     .000006          5
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

Francois
Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
14 octobre 2005 5 14 /10 /octobre /2005 00:00

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>


I do not know, at this moment which application i could find to this, but, maybe later...

Francois


Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article
13 octobre 2005 4 13 /10 /octobre /2005 00:00
"Three witches watch three Swatch watches. Which witch watch which Swatch watch?"


I can, now, clean-up my screen...

Francois

Repost 0
Francois Degrelle - dans Other
commenter cet article
13 octobre 2005 4 13 /10 /octobre /2005 00:00

How to encrypt/decrypt strings with the dbms_obfuscation_toolkit / dbms_crypto packages

Oracle 9i dbms_obfuscation_toolkit package

CREATE OR REPLACE PACKAGE Cryptit AS
   FUNCTION encrypt( Str VARCHAR2 ) RETURN RAW;
   FUNCTION decrypt( xCrypt VARCHAR2 ) RETURN VARCHAR2;
END Cryptit;
/

CREATE OR REPLACE PACKAGE BODY Cryptit 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 Cryptit;
/

 SQL*Plus: Release 9.0.1.3.0 - Production on Je Oct 13 10:28:55 2005

(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> set serveroutput on
SQL>
SQL> DECLARE
  2    LC$Code VARCHAR2(100) := 'Music is the best!' ;
  3  BEGIN
  4 
  5    -- Get the encrypted string --
  6    LC$Code := Cryptit.Encrypt( LC$Code ) ;
  7    dbms_output.put_line( LC$Code ) ;
  8 
  9    -- Get the decrypted string --
 10    LC$Code := Cryptit.Decrypt( LC$Code ) ;
 11    dbms_output.put_line( LC$Code ) ;
 12 
 13  END ;  
 14 
 15  /
7840712517939382157F5BA660E3AAB094E9C2BA67FF4234
Music is the best!

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

SQL>

 
Oracle 10g dbms_crypto package

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2   LC$Source    VARCHAR2(19) := 'Music is the best!';
  3   LR$Source    RAW(128) := utl_raw.cast_to_raw(LC$Source);
  4   LR$Key       RAW(128) := utl_raw.cast_to_raw('FrankZappa');
  5   LR$Crypted   RAW(2048);
  6   LR$Decrypted RAW(2048);
  7  
  8  BEGIN
  9 
 10    dbms_output.put_line('Source string : ' || LC$Source);
 11 
 12    LR$Crypted := dbms_crypto.encrypt(LR$Source,
 13    dbms_crypto.des_cbc_pkcs5, LR$Key);
 14 
 15    dbms_output.put_line('Encrypted raw : ' ||
 16    RAWTOHEX(utl_raw.cast_to_raw(LR$Crypted)));
 17 
 18    LR$Decrypted := dbms_crypto.decrypt(src => LR$Crypted,
 19    typ => dbms_crypto.des_cbc_pkcs5, key => LR$Key);
 20 
 21    dbms_output.put_line('Decrypted string : ' ||
 22    utl_raw.cast_to_varchar2(LR$Decrypted));
 23  END;
 24  /
Source string : Music is the best!
Encrypted raw :
33454635363638343931453734313342373337454141313133453637323639373934373433463442
4533464246333831
Decrypted string : Music is the best!

PL/SQL procedure successfully completed.

SQL>

Francois

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article