Showing posts with label PLSQL. Show all posts
Showing posts with label PLSQL. Show all posts

Wednesday, 2 May 2012

Convert between different numbering systems ,binary, octal, decimal and hex 


set serveroutput on
 
CREATE OR REPLACE PACKAGE dbms_numsystem AS
   function bin2dec (binval in char  ) RETURN number;
   function dec2bin (N      in number) RETURN varchar2; 
   function oct2dec (octval in char  ) RETURN number;
   function dec2oct (N      in number) RETURN varchar2; 
   function hex2dec (hexval in char  ) RETURN number;
   function dec2hex (N      in number) RETURN varchar2; 
END dbms_numsystem;
/
show errors
 
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
 
FUNCTION bin2dec (binval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(binval);
  for i in 1..digits loop
     current_digit := SUBSTR(binval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 2) + current_digit_dec;
  end loop;
  return result;
END bin2dec;
 
FUNCTION dec2bin (N in number) RETURN varchar2 IS
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
 
FUNCTION oct2dec (octval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(octval);
  for i in 1..digits loop
     current_digit := SUBSTR(octval, i, 1);
     current_digit_dec := to_number(current_digit);
     result := (result * 8) + current_digit_dec;
  end loop;
  return result;
END oct2dec;
 
FUNCTION dec2oct (N in number) RETURN varchar2 IS
  octval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     octval := mod(N2, 8) || octval;
     N2 := trunc( N2 / 8 );
  end loop;
  return octval;
END dec2oct;
 
FUNCTION hex2dec (hexval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(hexval);
  for i in 1..digits loop
     current_digit := SUBSTR(hexval, i, 1);
     if current_digit in ('A','B','C','D','E','F') then
        current_digit_dec := ascii(current_digit) - ascii('A') + 10;
     else
        current_digit_dec := to_number(current_digit);
     end if;
     result := (result * 16) + current_digit_dec;
  end loop;
  return result;
END hex2dec;
 
FUNCTION dec2hex (N in number) RETURN varchar2 IS
  hexval varchar2(64);
  N2     number := N;
  digit  number;
  hexdigit  char;
BEGIN
  while ( N2 > 0 ) loop
     digit := mod(N2, 16);
     if digit > 9 then 
        hexdigit := chr(ascii('A') + digit - 10);
     else
        hexdigit := to_char(digit);
     end if;
     hexval := hexdigit || hexval;
     N2 := trunc( N2 / 16 );
  end loop;
  return hexval;
END dec2hex;
 
END dbms_numsystem;
/
show errors
 
-- Examples:
select dbms_numsystem.dec2bin(22)      from dual;
select dbms_numsystem.bin2dec('10110') from dual;
select dbms_numsystem.dec2oct(44978)   from dual;
select dbms_numsystem.oct2dec(127662)  from dual;
select dbms_numsystem.dec2hex(44978)   from dual;
select dbms_numsystem.hex2dec('AFB2')  from dual;
No comments:
Labels:

Random number or string generator package 


create or replace package random
is
   procedure srand(new_seed in number);
   procedure get_rand(r OUT number);
   procedure get_rand_max(r OUT number, n IN number);
   function  rand return number;
   function  rand_max(n IN number) return number;
   function  rand_string(ssiz IN number) return varchar2;
   function  smaller(x IN number, y IN number) return number;
   pragma restrict_references(rand, WNDS);
   pragma restrict_references(rand_max, WNDS);
   pragma restrict_references(random, WNDS, RNPS);
   pragma restrict_references(rand_string, WNDS);
   pragma restrict_references(smaller, WNDS);
end random;
/
 
create or replace package body random
is
   multiplier   constant number := 22695477;
   increment    constant number := 1;
   "2^32"       constant number := 2 ** 32;
   "2^16"       constant number := 2 ** 16;
   "0x7fff"     constant number := 32767;
   Seed         number          := 1;
 
   function  smaller(x IN number, y IN number) return number is
   begin
        if x <= y then
            return x;
        else
            return y;
        end if;
   end smaller;
 
   function rand_string(ssiz IN number) return varchar2 is
     i      number;
     m      number;
     c      char;
     result varchar2(2000) := '';
   begin
        m := smaller(ssiz,2000);
        for i in 1..m loop
            c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
            result := result || c;
        end loop;
        return result;
   end rand_string;
 
   procedure srand(new_seed in number) is
   begin
     Seed := new_seed;
   end srand;
 
   function rand return number is
   begin
     Seed := mod(multiplier * Seed + increment, "2^32");
     return bitand(Seed/"2^16", "0x7fff");
   end rand;
 
   procedure get_rand(r OUT number) is
   begin
     r := rand;
   end get_rand;
 
   function rand_max(n IN number) return number is
   begin
     return mod(rand, n) + 1;
   end rand_max;
 
   procedure get_rand_max(r OUT number, n IN number) is
   begin
     r := rand_max(n);
   end get_rand_max;
 
begin
   select userenv('SESSIONID')
   into   Seed
   from   dual;
end random;
/
 
-- Some examples:
select random.rand_max(10) from dual;
select random.rand_max(10) from dual;
select random.rand_string(20) from dual;
select random.rand_string(20) from dual;
No comments:
Labels:

Function to test for Leap Years 


CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS
BEGIN
  -- A year is a leap year if it is evenly divisible by 4 
  -- but not if it's evenly divisible by 100 
  -- unless it's also evenly divisible by 400 
 
   IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN
      return TRUE;
   ELSE 
      return FALSE;
   END IF;
END;
/
show errors
 
-- Let's test it
SET SERVEROUTPUT ON
BEGIN
  IF isLeapYear(2004) THEN
     dbms_output.put_line('Yes, it is a leap year');
  ELSE
     dbms_output.put_line('No, it is not a leap year');
  END IF;
END;
/
No comments:
Labels:

Print the ASCII table 


set serveroutput on size 10240
 
declare
   i number;
   j number;
   k number;
begin
   for i in 2..15 loop
       for j in 1..16 loop
           k:=i*16+j;
           dbms_output.put((to_char(k,'000'))||':'||chr(k)||'  ');
           if k mod 8 = 0 then
              dbms_output.put_line('');
           end if;
       end loop;
   end loop;
end;
/
show errors
No comments:
Labels:

Pass result sets ,REF CURSOR, between procedures and functions 


set serveroutput on
 
-- Define TYPES package separately to be available to all programming
-- environments...
CREATE OR REPLACE PACKAGE types AS
   TYPE cursortyp is REF CURSOR;   -- use weak form
END;
/
 
-- Create test package to demonstrate passing result sets...
CREATE OR REPLACE PACKAGE test_ref_cursor AS
   PROCEDURE main;
   FUNCTION  get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
   PROCEDURE process_cursor(cur types.cursortyp);
END;
/
show errors
 
CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS
 
  -- Main program entry point
  PROCEDURE main IS
  BEGIN
    process_cursor( get_cursor_ref(1) );
    process_cursor( get_cursor_ref(2) );
  END;
 
  -- Get and return a CURSOR REF/ Result Set
  FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
    cur  types.cursortyp;
  BEGIN
    if typ = 1 THEN
      OPEN cur FOR SELECT * FROM emp  WHERE ROWNUM < 5;
    ELSE
      OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
    END IF;
    RETURN cur;
  END;
 
  -- Process rows for an EMP or DEPT cursor
  PROCEDURE process_cursor(cur types.cursortyp) IS
    empRec  emp%ROWTYPE;
    deptRec dept%ROWTYPE;
  BEGIN
    LOOP
      FETCH cur INTO empRec;    -- Maybe it was an EMP cursor, try to fetch...
      EXIT WHEN cur%NOTFOUND;
      dbms_output.put_line('EMP ROW: '||empRec.ename);
    END LOOP;
  EXCEPTION
    WHEN ROWTYPE_MISMATCH THEN  -- OK, so it was't EMP, let's try DEPT.
       LOOP
         FETCH cur INTO deptRec;
         EXIT WHEN cur%NOTFOUND;
         dbms_output.put_line('DEPT ROW: '||deptRec.dname);
       END LOOP;
  END;
 
END;
/
show errors
 
 
EXEC test_ref_cursor.main;
No comments:
Labels:

Monday, 30 April 2012

Password encrypt,decrypt using DBMS Obfuscation Toolkit 


CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors
 
 
CREATE OR REPLACE PACKAGE BODY PASSWORD AS
 
  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';
 
  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));
 
     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;
 
  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;
 
 
end PASSWORD;
/
show errors
 
-- Test if it is working...
select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;
No comments:
Labels:

Select records from a cursor into PL SQL table 


set serveroutput on
 
declare
  -- Declare the PL/SQL table
  type deptarr is table of dept%rowtype
       index by binary_integer;
  d_arr deptarr;
 
  -- Declare cursor
  type d_cur is ref cursor return dept%rowtype;
  c1 d_cur;
 
  i number := 1;
begin
  -- Populate the PL/SQL table from the cursor
  open c1 for select * from dept;
  loop
    exit when c1%NOTFOUND;
    fetch c1 into d_arr(i);
    i := i+1;
  end loop;
  close c1;
 
  -- Display the entire PL/SQL table on screen
  for i in 1..d_arr.last loop
    dbms_output.put_line('DEPTNO : '||d_arr(i).deptno );
    dbms_output.put_line('DNAME  : '||d_arr(i).dname  );
    dbms_output.put_line('LOC    : '||d_arr(i).loc    );
    dbms_output.put_line('---------------------------');
  end loop;
end;
/
No comments:
Labels:

Profile PL SQL code for execution statistics 


conn / as sysdba
 
-- Install the profiler...
@?/rdbms/admin/proftab
@?/rdbms/admin/profload
@?/plsql/demo/profrep.sql
 
-- Create a test procedure to time...
CREATE OR REPLACE PROCEDURE proc1 IS
  v_dummy CHAR;
BEGIN
   FOR i IN 1..100 LOOP
      SELECT dummy INTO v_dummy FROM dual;
   END LOOP;
END;
/
SHOW ERRORS
 
-- Do the profilling and print the report...
set line 5000 serveroutput on size 1000000
DECLARE
  v_run NUMBER;
BEGIN
  DBMS_PROFILER.START_PROFILER('test','test1',v_run);
  proc1;
  DBMS_PROFILER.STOP_PROFILER;
  DBMS_PROFILER.ROLLUP_RUN(v_run);
  PROF_REPORT_UTILITIES.PRINT_RUN(v_run);
END;
/
No comments:
Labels:

Simple program to demonstrate BULK COLLECT and BULK BIND operations 


set serveroutput on size 50000
 
DECLARE
  CURSOR emp_cur IS SELECT * FROM EMP;
 
  TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab emp_tab_t;            -- In-memory table
 
  rows NATURAL        := 10000;   -- Number of rows to process at a time
  i    BINARY_INTEGER := 0;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Bulk collect data into memory table - X rows at a time
    FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
    EXIT WHEN emp_tab.COUNT = 0;
 
    DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');
 
    FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
      -- Manipumate data in the memory table...
      dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
    END LOOP;
 
    -- Bulk bind of data in memory table...
    FORALL i in emp_tab.FIRST..emp_tab.LAST
      INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);
 
  END LOOP;
  CLOSE emp_cur;
END;
No comments:
Labels:

Update, delete from a huge table with intermittent commits 


loop
      update tab1 set col1 = 'value2'
             where rowid = c1.rowid;
 
      i := i + 1;              -- Commit after every X records
      if i > 10000 then
         commit;
         i := 0;
      end if;
 
  end loop;
  commit;
end;
/
 
-- Note: More advanced users can use the mod() function to commit every N rows. 
--       No counter variable required:
--
-- if mod(i, 10000) 
--    commit;
--    dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount);
--  end if;
No comments:
Labels:
Subscribe to: Posts (Atom)