Oracle External tables

1) Create Oracle Directory
2) Put file (txt, csv) into Directory

Parametrized view

Following is an example which shows use of parametrized view:

CREATE OR REPLACE VIEW test_vw AS
SELECT empno,ename,sal
FROM emp
WHERE empno = to_number(userenv('client_info')) ;

In Stored Procedure, will have to call following procedure before executing the View query, as in following example:

exec dbms_application_info.set_client_info(7934);

select * from test_vw ;

And the output is:

EMPNO ENAME SAL
---------- ---------- ----------
7934 MILLER 13040

Oracle hash function

use of function
select dbms_utility.get_hash_value('your string', 999999, 32768) from dual

Oracle read/write into file

Write to file
declare
  output utl_file.file_type;
  line   varchar2(20000);
begin
--  execute immediate 'alter session set NLS_CHARACTERSET = CL8MSWIN1251';
  output := utl_file.fopen(location => 'directory_name', filename => to_char(sysdate, 'file_name', open_mode => 'A');
  for rec in
    ()
  loop
    line := rec.field1|| '|' || rec.field2 || '|' || rec.field3 || '|' || rec.field4 || '|' || rec.field5;
    utl_file.put(output, line); 
    utl_file.new_line(file => output);
  end loop;
  utl_file.fclose(file => output);
end;