Thank_u. This is mitul. Welcome to my site. Enjoy downloads, help me 2 go ahead, help peoples to get help from peoples. |
Mostafizur Rahman (MITUL) PGD-IT Jahangirnagar University OCP Oracle University Cell no: +8801717084664 E_mail : ortho_hin07@yahoo.com |
Site Address: | Downloads and Details : | |
Advanced Yahoo Search Arif's personal website citi - Jahangirnagar university email - azaman.16@gmail.com cell no - 01716918704 Tuhin's's personal website citi - Jahangirnagar university email - tuhin_tr@yahoo.com cell no - 01199157228 Scripting help site |
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 .............
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...............................
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 ----------------------------------------------------------------------- <
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
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; --=====================================================
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 /
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; /
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 --
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;
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
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
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; --====================================================================================================
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
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;
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
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 ----------------------------------------------------------------------- <
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
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;
|
Mitul Search | Mitul Search | Mitul Search |