Wednesday, August 7, 2013

How to invoke/run a pl/sql function from sqldeveloper

I wanted to run a pl/sql function from sqldeveloper and want to unit test it.
That function returns a sys_refcursor, which I would like to print it and see the result.

After searching for long time, I found the following two ways which worked for me.

My function spec is as below

function get_data(
  in_abs_start_date    in  int,
  in_abs_end_date      in  int
) return sys_refcursor;


And I have used the following two methods to invoke this pl/sql function from sql developer.

1)

variable v_ref_cursor refcursor;

exec :v_ref_cursor := package_name.get_data(in_abs_start_date =>1375209000000,in_abs_end_date =>1375295400000);

print :v_ref_curso
r


2)

SELECT package_name.get_data(in_abs_start_date =>13752090000,in_abs_end_date =>1375295400000) FROM dual

No comments:

Post a Comment