DW-BIAnalytics: May 2017

Sunday 28 May 2017

Calling Procedures from Functions in Oracle

I just want to clarify the confusion whether we can call procedure from function or no.

Can we call a function from a procedure?

Answer is Yes.

We can call procedure from functions in Oracle.

Following is the code for procedure which takes a number as input and it will multiply the number with 10 and gives the result as output.

CREATE or REPLACE PROCEDURE sp_Test(v_Num_IN IN Number,v_Num_out OUT Number)

Begin

v_Num_Out:= v_Num_IN*10;

END;

SQL> variable v_out number;

SQL> exec sp_Test(10,:V_out);

PL/SQL procedure successfully completed.

SQL> PRINT :V_out

V_OUT

----------

100

Following is the code for function which takes a number as input, and it calls the procedure ‘sp_Test’ and holds the output in a local variable. We are going to return the local variable from the function.

CREATE or REPLACE FUNCTION fn_Test_Calling_Sp(v_In number) RETURN NUMBER

v_Ret_Val number;

BEGIN

sp_test(V_In,v_Ret_val); /* Calling the procedure*/

Return v_Ret_val;

END;

Let’s call the above function through a SELECT statement.

SQL> select fn_Test_Calling_Sp(10) from dual;

FN_TEST_CALLING_SP(10)

----------------------

100

Hope it is clear.

Thanks.

Wednesday 10 May 2017

REF CURSORS in Oracle

REF CURSORS in Oracle

=================================================================
==================================================================

create or replace procedure sp_basic_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;

begin

open emp_ref for select * from emp;
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;

==================================================================
==================================================================

create or replace procedure sp_reuse_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;
dept_rec dept%rowtype;
begin
dbms_output.put_line(' Employee data ');
open emp_ref for select * from emp;
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename);
exit when emp_ref%notfound;
end loop;
close emp_ref;
dbms_output.put_line(' Dept data ');
open emp_ref for select * from dept;
loop
fetch emp_ref into dept_rec;
dbms_output.put_line(dept_rec.deptno||' '||dept_rec.dname||' '||dept_rec.loc);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;
/

==================================================================
==================================================================

create or replace procedure sp_Sample_output_refcur_demo
(v_dno in Number,v_emp_ref OUT SYS_REFCURSOR)
is
emp_rec emp%rowtype;
begin
dbms_output.put_line(' Employee data ');
open v_emp_ref for select * from emp where deptno=v_dno;
end;
/

==================================================================

create or replace procedure sp_Calling_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;
begin
dbms_output.put_line(' Employee data ');
sp_Sample_output_refcur_demo(20,emp_ref);
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;
/

==================================================================
==================================================================

create or replace procedure sp_dno_input_refcur_demo(dept_ref in SYS_REFCURSOR)
is
v_deptno dept.deptno%type;
emp_ref sys_refcursor;
emp_rec emp%rowtype;

begin
loop
fetch dept_ref into v_deptno;
exit when dept_ref%notfound;
dbms_output.put_line(' Employee Details of ' ||v_deptno);
dbms_output.put_line(' ---------------------------- ');
open emp_ref for select * from emp where deptno=v_deptno;
loop
fetch emp_ref into emp_rec;
exit when emp_ref%notfound;
dbms_output.put_line(emp_rec.ename||' '||emp_rec.deptno);
end loop;
end loop;
close emp_ref;
close dept_ref;
end;
/

create or replace procedure sp_dno_call_ip_refcur_demo
is
v_ref_cur sys_refcursor;
begin
open v_ref_cur for select distinct deptno from emp;
sp_dno_input_refcur_demo(v_ref_cur);
end;

==================================================================
==================================================================

Tuesday 9 May 2017

UTL_FILE Package

UTL_FILE Package

1. WE have to create a directory

Directory is an oracle object which points to Operating System Folder

Syntax for Creating a Directory

Create DIRECTORY <DIR_NAME> as ‘<Folder_Path>;create directory raghu_utl as 'D:\Raghu_utl_demos';

Query to get the directory information is –

Select Owner, Directory_Name,Directory_Path from all_directories;

2. We have to create a File Handler

Syntax for creating FileHandler –

<File_Handler_Name> UTL_FILE.FILE_TYPE;

UTL_FILE à Is a package

FILE_TYPE

F1 UTL_FILE.FILE_TYPE;

3. Open a file and associate the filehandler to that file.

FOPEN is a Function which opens the file which is in OS Folder in a Specified Mode

<File_Handler>:= UTL_FILE.FOPEN(‘ORACLE_DIR’,’FILE_NAME.EXT’,’MODE’);

Modes à R(Read), W(Write) and A(Append)

F1 := UTL_FILE.FOPEN(‘RAGHU_UTL’,’Dept_ff.txt’,’W’);

4. WE have to use the procedure to read and write into the files.

UTL_FILE.GET_LINE(<File_Handler>, <String_Variable>);

- This is a procedure which is used To read data from files

UTL_FILE.PUT_LINE(<FILE_HANDLER>,<String_Variable);

- This is a procedure which is used to write the data into the files.

5. Close the FileHandler

UTL_FILE.FCLOSE(<File_Handler>);

UTL_FILE.FCLOSE_ALL;

DB to Flat File

Create or replace procedure sp_Utl_DB_FF

F1 UTL_FILE.FILE_TYPE;

Begin

F1 := UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’A’);

For i in (Select * from dept) loop

UTL_File.put_line(F1,i.Deptno||’,’||i.Dname||’,’||i.loc);

End loop;

UTL_FILE.FCLOSE(F1);

End;

Flat File to Flat File

Create or Replace Procedure SP_UTL_DEPT_FF_FF

F1 UTL_FILE.FILE_TYPE;

F2 UTL_FILE.FILE_TYPE;

V_STR varchar2(200);

Begin

F1:=UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’R’);

F2:=UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff_Copy.txt’,’A’);

LOOP

Begin

UTL_FILE.GET_LINE(F1,v_STR);

DBMS_OUTPUT.PUT_LINE(V_Str);

UTL_FILE.PUT_LINE(F2,v_Str);

Exception

When No_Data_Found then

Exit;

End;

End loop;

UTL_FILE.FCLOSE(F1);

UTL_FILE.FCLOSE(F2);

End;

Flat File to Dept table

Create or replace procedure sp_UTl_FF_DB

F1 UTL_FILE.FILE_TYPE;

V_Str varchar2(200);

V_DEPTNO NUMBER;

V_DNAME VARCHAR2(20);

V_LOC VARCHAR2(20);

Begin

F1 := UTL_FILE.FOPEN(’RAGHU_UTL’,’Dept_ff.txt’,’R’);

Loop

Begin

UTL_FILE.GET_LINE(F1,v_Str);

--- ’10,sales,newyork’;

V_deptno := substr(v_Str,1,instr(v_Str,’,’,1)-1);

V_dname := substr(v_Str,instr(v_str,’,’,1,1)+1,instr(v_Str,’,’,1,2)-instr(v_Str,’,’,1,1)-1);

V_loc := substr(v_Str,instr(v_str,’,’,1,2)+1);

Insert into dept values(v_deptno,v_dname,v_loc);

Exception

When no_data_found then

Exit;

End;

End loop;

UTL_FILE.FCLOSE(F1);

End;

DW-BIAnalytics

Sunday 28 May 2017

Calling Procedures from Functions in Oracle

Wednesday 10 May 2017

REF CURSORS in Oracle

Tuesday 9 May 2017

UTL_FILE Package

Sunday 7 May 2017

SUMMERIZE Transformation in Alteryx

RUNNING TOTAL Transformation in Alteryx

CROSS TAB Transformations in Alteryx

CROSS TAB and TRANSPOSE Transformations in Alteryx

Thursday 4 May 2017

SAMPLE and RANDOM % SAMPLE Transformations in Alteryx

RecordID Transformation in Alteryx

GENERATE ROWS Transformation in Alteryx

FIND REPLACE Transformation in Alteryx

DATE TIME Transformation in Alteryx

DATE FILTER Transformation in Alteryx

DATA CLEANSING Transformation in Alteryx

Cartesian join using APPEND FIELDS in Alteryx

Wednesday 3 May 2017

JOIN in Alteryx (All types of Joins) with TEXT INPUT

UNION and UNIQUE Transformations in Alteryx

Query Over ride in Alteryx

FORMULA and IMPUTATION Transformations in Alteryx

SORT Transformation in Alteryx

FILTER Transformation in Alteryx

SELECT Transformation in Alteryx