Mostafiz MITUL B N School '98

Thank_u. This is mitul. Welcome to my site. Enjoy downloads, help me 2 go ahead, help peoples to get help from peoples.

Site Address:

Downloads and Details :

Create installer.............. :

rem -----------------------------------------------------------------------
rem Filename:   Create installer..............
rem Purpose:    Create installer..............
rem Date:       11-aug-2010
rem Author:     mostafiz mitul, Oracle FAQ
rem -----------------------------------------------------------------------

create ur installer .............

>>> Enjoy & EXECUTE
...Mostafiz mitul

Convert oracle report into excel....... :

rem -----------------------------------------------------------------------
rem Filename:   Convert oracle report into excel....... :
rem Purpose:    Convert oracle report into excel....... :
rem Date:       11-aug-2010
rem Author:     Mostafiz mitul, Oracle FAQ
rem -----------------------------------------------------------------------
Declare
v_conn_str  varchar2(50) := 'scott/tiger@devdb'; --To be changed
v_rep2excel_path varchar2(100) := 'D:\thepath\rep2excel.exe';--To be changed
v_cache_path varchar2(100) :='c:\Temp\';--To be changed
--
v_random_name varchar2(100);
v_cmd_rwrun   varchar2(300);
v_cmd_rep2excel   varchar2(300);
v_input_path varchar2(300) ;
v_output_path varchar2(300) ;
Begin
--error   select to_char(round(dbms_random.value(100000, 999999)))||
--error   to_char(sysdate,'DDHH24MISS')
--error    into v_random_name from dual;
v_input_path := v_cache_path||v_random_name||'.htm' ;
v_output_path := v_cache_path||v_random_name||'.xls' ;
 
v_cmd_rwrun := 'RWRUN60 REPORT=C:\orant\REPORT60\pol_ben.rdf USERID='||v_conn_str;
v_cmd_rwrun := v_cmd_rwrun || ' DESTYPE=FILE DESFORMAT=HTML' ;
v_cmd_rwrun := v_cmd_rwrun || ' DESNAME='||v_input_path||' batch=yes';
 :cmd1 := v_cmd_rwrun ;
HOST(v_cmd_rwrun);
 
v_cmd_rep2excel:='cmd /c start '||v_rep2excel_path||' -i:'||v_input_path ||' -o:'||v_output_path ||' -open' ;
 :cmd2 := v_cmd_rep2excel;
 HOST(v_cmd_rep2excel);

End;

download...............................

 

>>> Enjoy & EXECUTE
...Mostafiz mitul

Oracle e-mail... from forms 6i using outlook express :

rem -----------------------------------------------------------------------
rem Filename:   Oracle e-mail... from forms 6i using outlook express
rem Purpose:    Oracle e-mail... from forms 6i using outlook express
rem Date:       11-aug-2010
rem Author:     mostafiz mitul, Oracle FAQ
rem -----------------------------------------------------------------------

<>
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');
Declare
  v_conn_str  varchar2(50) := 'usr_mgmt/usr_mgmt@aspl'; --To be changed
  v_cache_path varchar2(100) :='E:\all_is_well\tst\';--To be changed
  --
  v_random_name varchar2(100);
  v_cmd_rwrun   varchar2(300);
  v_cmd_rep2excel   varchar2(300);
  v_input_path varchar2(300) ;
  v_output_path varchar2(300) ;
  v_boolean boolean := false;
Begin
  if :SYSTEM.CURSOR_BLOCK <> 'USER_INFO' then
    go_block('USER_INFO');
  end if;
  
First_Record;
while :System.Last_Record <> 'TRUE' loop
--create report to pdf
    v_cmd_rwrun := 'RWRUN60 REPORT=E:\all_is_well\tst\tst_mail.rdf USERID='||v_conn_str;
    v_cmd_rwrun := v_cmd_rwrun||' P_EMP_ID='||:USER_INFO.EMP_ID;
    v_cmd_rwrun := v_cmd_rwrun || ' DESTYPE=FILE DESFORMAT=PDF' ;
    v_cmd_rwrun := v_cmd_rwrun || ' DESNAME='||v_cache_path||:USER_INFO.EMP_ID||'.pdf batch=yes';
    host('cmd /c DEL '||v_cache_path||:USER_INFO.EMP_ID||'.PDF',no_screen);--
    HOST(v_cmd_rwrun);
--create report to pdf close    

    v_boolean := mail_to(:USER_INFO.E_MAIL, v_cache_path||:USER_INFO.EMP_ID||'.pdf', ' all is well>> report pdf as attatchment ');-- RETURN boolean IS
    
    if v_boolean = true then
      --message('successful.....');
      --message('successful.....');
      null;
    elsif v_boolean = false then
        message('is not successful.....');
        message('is not successful.....');
    else
        message('error.....');
        message('error.....');
    end if;
    
next_record;
END loop;

end;
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'Arrow');


---############----------mail to function ---------- defination
FUNCTION mail_to(p_to varchar2, p_attatch_loc varchar2,p_msg varchar2) RETURN boolean IS
  objOutlook OLE2.OBJ_TYPE;
  objMail OLE2.OBJ_TYPE;
  objArg OLE2.LIST_TYPE;
  objAttach OLE2.OBJ_TYPE;
  v_to varchar2(30) := p_to;
  v_attatch_loc varchar2(511) := p_attatch_loc;
  v_body_txt varchar2(2048) := p_msg;
BEGIN
  objOutlook := OLE2.CREATE_OBJ('Outlook.Application');
  objarg := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(objarg,0);
  objMail := OLE2.INVOKE_OBJ(objOutlook,'CreateItem',objarg);
  OLE2.DESTROY_ARGLIST(objarg);
  
  objAttach := OLE2.GET_OBJ_PROPERTY(objmail, 'Attachments');
  
  objarg := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(objarg,v_attatch_loc); --attachment file
  
  --v_body_txt := ' 4rm oracle forms................. with an attatchment';
  OLE2.SET_PROPERTY(objmail,'To',v_to);
  OLE2.SET_PROPERTY(objmail,'Body',v_body_txt);
  
  
  OLE2.INVOKE(objattach, 'Add', objarg);  --add attachment file
  OLE2.INVOKE(objmail,'Send');
  --OLE2.INVOKE(objmail,’Display’); --display before sending
  OLE2.RELEASE_OBJ(objmail);
  OLE2.RELEASE_OBJ(objOutlook);
  OLE2.DESTROY_ARGLIST(objarg);

return true;
EXCEPTION
  WHEN OTHERS THEN
    return false;
END;

---############----------another mail to function ---------- defination
FUNCTION mail_service(p_send_to varchar2, p_subj_127 varchar2, p_mail_body_1023 varchar2, p_attatchment_loc varchar2)
RETURN boolean IS
  objOutlook OLE2.OBJ_TYPE;
  objMail OLE2.OBJ_TYPE;
  objArg OLE2.LIST_TYPE;
  objAttach OLE2.OBJ_TYPE;
  Attachments OLE2.OBJ_TYPE;
  v_attatchment_loc varchar2(1023) := p_attatchment_loc;
  v_body_txt varchar2(1023) := '';
BEGIN
  objOutlook := OLE2.CREATE_OBJ('Outlook.Application');
  objarg := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(objarg,0);
  objMail := OLE2.INVOKE_OBJ(objOutlook,'CreateItem',objarg);
  OLE2.DESTROY_ARGLIST(objarg);
  
  v_body_txt := '<>'||CHR(10)||p_mail_body_1023;

  OLE2.SET_PROPERTY(objmail,'To',p_send_to);
  OLE2.SET_PROPERTY(objmail,'Subject',p_subj_127);
  OLE2.SET_PROPERTY(objmail,'Body',v_body_txt);
  
  --add an attachment
  if v_attatchment_loc is null or
    v_attatchment_loc = '' then
    v_attatchment_loc := 'E:\ASPL\att.txt';
  end if;
  Attachments := OLE2.GET_OBJ_PROPERTY(objmail,'Attachments');
  objarg := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(objarg, v_attatchment_loc);
  Attachments := OLE2.INVOKE_OBJ(Attachments,'add',objarg);
  OLE2.DESTROY_ARGLIST(objarg);
  
  
  OLE2.INVOKE(objmail,'Send');
  --OLE2.INVOKE(objmail,’Display’); --display before sending
  OLE2.RELEASE_OBJ(objmail);
  OLE2.RELEASE_OBJ(objOutlook);
  
  return true;  
EXCEPTION
  WHEN OTHERS THEN
  return false;
END;

>>> Enjoy & EXECUTE
...Mostafiz mitul

Count the number of rows for ALL tables in the current schema :

rem -----------------------------------------------------------------------
rem Filename:   countall.sql
rem Purpose:    Count the number of rows for ALL tables in the current schema
rem Date:       05-jan-2010
rem Author:     mostafiz mitul, Oracle FAQ
rem -----------------------------------------------------------------------

set termout off echo off feed off trimspool on head off pages 0

spool countall.tmp
select 'SELECT count(*), '''||table_name||''' from '||table_name||';'
from   user_tables
/
spool off
set termout on
@@countall.tmp
set head on feed on

 

>>> Enjoy & EXECUTE
...Mostafiz mitul

Demontrate VARRAY (variable array in one database column) :

rem -----------------------------------------------------------------------
rem Filename:   varray.sql
rem Purpose:    Demontrate VARRAY (variable array in one database column)
rem             collection types
rem Date:       05-jan-2010
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from   varray_table t1, TABLE(t1.col1) t2
/

-- Use PL/SQL to access the varray...
set serveroutput on
declare
  v_vcarray vcarray;
begin
  for c1 in (select * from varray_table) loop
      dbms_output.put_line('Row fetched...');
      FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
          dbms_output.put_line('...property fetched: '|| c1.col1(i));
      END LOOP;
  end loop;
end;
/

-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;
--=====================================================
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

create duplicate table in sql server 2000 & oracle :

rem -----------------------------------------------------------------------
rem Filename:   xyz.sql
rem Purpose:    create duplicate table in sql server 2000 & oracle
rem Date:       05-jan-2010
rem Author:     mostafiz mitul
rem -----------------------------------------------------------------------
-- for sql server 2000,  to create duplicate table with data
select * into new_table from old_table
/
-- for oracle  to create duplicate table with data
create table new_table_name
as
select * from old_table_name
/
-- for oracle  to create duplicate table without data
create table new_table_name
as
select * from old_table_name
where 1=2
/

 

>>> Enjoy & EXECUTE
...Mostafiz mitul

function to test leap year :

rem -----------------------------------------------------------------------
rem Filename:   leapyear.sql
rem Purpose:    Check if a year is a leap year
rem Author:     mostafiz mitul, Oracle FAQ
rem -----------------------------------------------------------------------

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

>>> Enjoy & EXECUTE
...Mostafiz mitul

to generate matrix report :

 rem -----------------------------------------------------------------------
rem UPDATED VERSION
rem Filename:   matrix.sql
rem Purpose:    Example of a CROSS MATRIX report implemented using
rem             standard SQL.
rem Date:       12-Feb-2000
rem Author:     Frank Naude, Oracle FAQ
rem
rem Updated By  Mahesh Pednekar. (bunty609@hotmail.com)
rem Description Removed the Main query because the sub query itself
rem             will full fill the requirement.
rem -----------------------------------------------------------------------

SELECT job,
                sum(decode(deptno,10,sal)) DEPT10,
                sum(decode(deptno,20,sal)) DEPT20,
                sum(decode(deptno,30,sal)) DEPT30,
                sum(decode(deptno,40,sal)) DEPT40
           FROM scott.emp
       GROUP BY job
/

-- Sample output:
--
-- JOB           DEPT10     DEPT20     DEPT30     DEPT40
-- --------- ---------- ---------- ---------- ----------
-- ANALYST                    6000
-- CLERK           1300       1900        950
-- MANAGER         2450       2975       2850
-- PRESIDENT       5000
-- SALESMAN                              5600
--
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

delete duplicate values form a table :

rem -----------------------------------------------------------------------
rem Purpose:    Delete duplicate values from a table
rem Date:       04-Mar-2005
rem Notes:      Verify that the correct rows are deleted before you COMMIT!
rem Author:     Dharmendra Srivastava,Associate,
rem             MindTree Consulting Pvt Ltd. India
rem -----------------------------------------------------------------------

DELETE FROM my_table
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                       FROM my_table
                      GROUP BY delete_col_name);

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- To delete the duplicate values:
-- 
-- DELETE FROM emp
--  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id);
-- 
-- COMMIT;
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

Select the Nth highest value from a table :

rem -----------------------------------------------------------------------
rem Filename:   maxvalue.sql
rem Purpose:    Select the Nth highest value from a table
rem Date:       18-Apr-2001
rem Author:     Deepak Rai, SSE, Satyam Computer Services Ltd. India
rem -----------------------------------------------------------------------

select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;

-- Example :
--
-- Given a table called emp with the following columns:
--   id   number
--   name varchar2(20)
--   sal  number
--
-- For the second highest salary:
--
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

convert number to text word :

rem -----------------------------------------------------------------------
rem Filename:   no thing special.sql
rem Purpose:    convert number to text word
rem Date:       01-nov-1981
rem Author:     mostafiz mitul bangladesh
rem -----------------------------------------------------------------------

select to_char(to_date(5373484,'J'), 'JSP') as converted_form
from dual
/

maximum allowed number ==== 5373484

 

>>> Enjoy & EXECUTE
...Mostafiz mitul

convert column to row >>>>> row to column :

--====================================================================================================
a table contains data as given
name exam marks
-------- -------- -----------
Test Phy 30
Test Chm 40
Test Eng 50
XYZ Eng 50
XYZ Mat 11
XYZ SS 10
XYZ Phy 40
output>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Name Sub1 mark1 sub2 mark2 sub3 mark3 sub4 mark4
-------- -------- -------- --------- -------- ------- --------- -------- --------
Test Phy 30 Chm 40 Eng 50
XYZ Eng 50 Mat 11 SS 10 Phy 40

pls send me query
hi Please find the query below

select name
, max (case exam when 'Chm' then exam end) as Sub1
, max (case exam when 'Chm' then marks end) as Mark1
, max (case exam when 'Eng' then exam end) as Sub2
, max (case exam when 'Eng' then marks end) as Mark2
, max (case exam when 'Mat' then exam end) as Sub3
, max (case exam when 'Mat' then marks end) as Mark3
, max (case exam when 'Phy' then exam end) as Sub4
, max (case exam when 'Phy' then marks end) as Mark4
, max (case exam when 'SS' then exam end) as Sub5
, max (case exam when 'SS' then marks end) as Mark5
from t
group by name;
--====================================================================================================
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

provide the following result :

rem -----------------------------------------------------------------------
create table tb_row_2_col (id number,val varchar2(100));
insert into tb_row_2_col values (1,'col1');
insert into tb_row_2_col values (1,'col2');
insert into tb_row_2_col values (1,'col3');
insert into tb_row_2_col values (2,'col4');
insert into tb_row_2_col values (2,'col5');
commit;
SQL> select * from tb_row_2_col;
ID VAL
------------------------------
1 col1
1 col2
1 col3
2 col4
2 col5
if i execute a query the output should be like this
ID VAL
------------------------------
1 col1,col2,col3
2 col4,col5
rem -----------------------------------------------------------------------
answer>>>>>>>>>>>>
SQL> select id,max(ltrim(sys_connect_by_path(val,','),',')) res
  from (select id,val,
          row_number() over(partition by id order by val) rn
       from tb_row_2_col)
  start with rn = 1
  connect by prior rn = rn -1
         and prior id = id
  group by id;

  ID RES
--------------
  1 col1,col2,col3
  2 col4,col5


 

>>> Enjoy & EXECUTE
...Mostafiz mitul

DBMS_OBFUSCATION_TOOLKIT_FFI PASSWORD encrypt decrypt :

rem -----------------------------------------------------------------------
rem            DBMS Obfuscation Toolkit
rem Note:      Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql --10G
rem Author:    Frank Naude
REM SQL>  select distinct name, type from user_source where name like 'DBMS_OBFUSCATION%';
REM  NAME                           TYPE
  ------------------------------ ------------
REM  DBMS_OBFUSCATION_TOOLKIT_FFI   PACKAGE
REM  DBMS_OBFUSCATION_TOOLKIT       PACKAGE
REM  DBMS_OBFUSCATION_TOOLKIT       PACKAGE BODY
REM  DBMS_OBFUSCATION_TOOLKIT_FFI   PACKAGE BODY
REM  grant execute on DBMS_OBFUSCATION_TOOLKIT_FFI to usr_mgmt; --BASICALY IS NOT NEEDED
REM  grant execute on DBMS_OBFUSCATION_TOOLKIT to usr_mgmt;  --BASICALY IS NOT NEEDED
REM  grant execute on PASSWORD to usr_mgmt;
rem -----------------------------------------------------------------------
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 sys.password.encrypt('PASSWORD1') from dual;
select sys.password.decrypt(password.encrypt('PASSWORD1')) from dual;
select sys.password.encrypt('PSW2') from dual;
select sys.password.decrypt(password.encrypt('PSW2')) from dual;
--select password.decrypt(app_password.encrypt('PSW2')) from dual;

>>> Enjoy & EXECUTE
...Mostafiz mitul

password encryptio-decription :

--====================================================password encryptio-decription
 
 DECLARE
   LC$Source    VARCHAR2(19) := 'b';
  LR$Source    RAW(128) := utl_raw.cast_to_raw(LC$Source);
   LR$Key       RAW(128) := utl_raw.cast_to_raw('asp#aspk');
   LR$Crypted   RAW(2048);
   LR$Decrypted RAW(2048);
  
  BEGIN

  dbms_output.put_line('Source string : ' || LC$Source);

  LR$Crypted := dbms_crypto.encrypt(LR$Source,
    dbms_crypto.des_cbc_pkcs5, LR$Key);

  dbms_output.put_line('Encrypted raw : ' ||
   RAWTOHEX(utl_raw.cast_to_raw(LR$Crypted)));

  LR$Decrypted := dbms_crypto.decrypt(src => LR$Crypted,
   typ => dbms_crypto.des_cbc_pkcs5, key => LR$Key);

  dbms_output.put_line('Decrypted string : ' ||
   utl_raw.cast_to_varchar2(LR$Decrypted));
  END;
/
--======================================finaly used
create or replace function encrypt_decrypt(p_source in varchar2, p_ad varchar2)  
return varchar2
as
   LC$Source    VARCHAR2(2048) := p_source;
  LR$Source    RAW(128) := utl_raw.cast_to_raw(LC$Source);
   LR$Key       RAW(128) := utl_raw.cast_to_raw('asplaspl');
   LR$Crypted   RAW(2048);
   LR$Decrypted RAW(2048);

  BEGIN

  --dbms_output.put_line('Source string : ' || LC$Source);
if lower(p_ad) = 'a' then
  LR$Crypted := dbms_crypto.encrypt(LR$Source,
    dbms_crypto.des_cbc_pkcs5, LR$Key);

  --dbms_output.put_line('Encrypted raw : ' ||
 --  RAWTOHEX(utl_raw.cast_to_raw(LR$Crypted)));
return RAWTOHEX(LR$Crypted);
elsif lower(p_ad) = 'd' then
  LR$Decrypted := dbms_crypto.decrypt(src => LC$Source,
   typ => dbms_crypto.des_cbc_pkcs5, key => LR$Key);

 -- dbms_output.put_line('Decrypted string : ' ||
 --  utl_raw.cast_to_varchar2(LR$Decrypted));

 
  return utl_raw.cast_to_varchar2(LR$Decrypted);
end if;
  
END;
/
--======================================finaly used
link_1  

 

>>> Enjoy & EXECUTE
...Mostafiz mitul

encript oracle forms with machine >>using systeminfo... product id :

rem -----------------------------------------------------------------------
Filename:     all is well
rem Purpose:    encript oracle forms with machine >>using systeminfo... product id
rem Date:       03-aug-2010
rem Author:     mostafiz mitul
rem -----------------------------------------------------------------------
<>  trigger
declare
  in_file  Text_IO.File_Type;
  linebuff  VARCHAR2(1024) := '';
  v_product_id varchar2(512) := '55274-648-8637434-23793';
  chk number(4) := 0;
begin
    host('cmd /c SYSTEMINFO /FO "LIST" > "E:\system.log"');--
    in_file := Text_IO.Fopen('E:\system.log', 'r');

    WHILE nvl(instr(linebuff,v_product_id),0) = 0 LOOP
      Text_IO.Get_Line(in_file,linebuff);
    END LOOP;
Text_IO.Fclose (in_file);
exception
  when no_data_found then
  exit_form;    
end;

select chr(251) from dual;
select chr(7) from dual;
 

>>> Enjoy & EXECUTE
...Mostafiz mitul

Oracle User export with three previous copy of Batch file :

rem ---file name xyz.bat
ECHO OFF
set LOCATION=F:\BACK\CMIS

rem -- Keep 3 versions of the export log file --
if exist %LOCATION%_2.log copy %LOCATION%.log /Y %LOCATION%_3.log >NUL:
if exist %LOCATION%_1.log copy %LOCATION%.log /Y %LOCATION%_2.log >NUL:
if exist %LOCATION%.log   copy %LOCATION%.log /Y %LOCATION%_1.log >NUL:

rem -- Keep 3 versions of the export dump file --
if exist %LOCATION%_2.dmp copy %LOCATION%.dmp /Y %LOCATION%_3.dmp >NUL:
if exist %LOCATION%_1.dmp copy %LOCATION%.dmp /Y %LOCATION%_2.dmp >NUL:
if exist %LOCATION%.dmp   copy %LOCATION%.dmp /Y %LOCATION%_1.dmp >NUL:

rem -- Do the export --
exp 'CMIS/ABC@ASPL' FULL=NO OWNER=(cmis) FILE=%LOCATION%.dmp LOG=%LOCATION%.log
rem exp 'user_name/password@database' FULL=NO OWNER=(user name to be exporded) FILE=%LOCATION%.dmp LOG=%LOCATION%.log

rem -- Add timestamp to top of export log file --
date /T >F:\BACK\timestamp.tmp
copy F:\BACK\timestamp.tmp + %LOCATION%.log %LOCATION%.tmp
copy %LOCATION%.tmp /Y %LOCATION%.log

rem -- Cleanup temporary files --
del  %LOCATION%.tmp
del  F:\BACK\timestamp.tmp

>>> Enjoy & EXECUTE
...Mostafiz mitul

DBMS_OBFUSCATION_TOOLKIT_FFI PASSWORD encrypt decrypt :

rem -----------------------------------------------------------------------
rem            DBMS Obfuscation Toolkit
rem Note:      Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql --10G
rem Author:    Frank Naude
REM SQL>  select distinct name, type from user_source where name like 'DBMS_OBFUSCATION%';
REM  NAME                           TYPE
  ------------------------------ ------------
REM  DBMS_OBFUSCATION_TOOLKIT_FFI   PACKAGE
REM  DBMS_OBFUSCATION_TOOLKIT       PACKAGE
REM  DBMS_OBFUSCATION_TOOLKIT       PACKAGE BODY
REM  DBMS_OBFUSCATION_TOOLKIT_FFI   PACKAGE BODY
REM  grant execute on DBMS_OBFUSCATION_TOOLKIT_FFI to usr_mgmt; --BASICALY IS NOT NEEDED
REM  grant execute on DBMS_OBFUSCATION_TOOLKIT to usr_mgmt;  --BASICALY IS NOT NEEDED
REM  grant execute on PASSWORD to usr_mgmt;
rem -----------------------------------------------------------------------
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 sys.password.encrypt('PASSWORD1') from dual;
select sys.password.decrypt(password.encrypt('PASSWORD1')) from dual;
select sys.password.encrypt('PSW2') from dual;
select sys.password.decrypt(password.encrypt('PSW2')) from dual;
--select password.decrypt(app_password.encrypt('PSW2')) from dual;

>>> Enjoy & EXECUTE
...Mostafiz mitul

Mitul Search
Powered by Yahoo……

Mitul Search
Powered by Google……

Mitul Search
Powered by MSN……