PL/SQL

hello aya,

as you know the procedure you can’t use inside query so if you need to create procedure to return multiple row or values you can use :

create or replace procedure myprocedure(retval in out sys_refcursor) is
begin
open retval for
select employee_id,last_name from employees;
end myprocedure;

and here you can call procedure inside anonymous pl/sql block :

[code]

DECLARE
myrefcur SYS_REFCURSOR;
employee_id employees.employee_id%TYPE;
last_name employees.last_name%TYPE;
BEGIN
myprocedure (myrefcur);

LOOP
FETCH myrefcur INTO employee_id,last_name;

EXIT WHEN myrefcur%NOTFOUND;
DBMS_OUTPUT.put_line (‘Employee Id: ‘ || employee_id || ‘ Last Name: ‘|| last_name);
END LOOP;

CLOSE myrefcur;
END;
[/code]

hope this help.

utl_file.fopen takes the name of a directory, not the path. For example:

(you may need to login as SYS to execute these)

[code]Windows:
CREATE DIRECTORY CUST_DIR AS ‘C:’;

Linux
CREATE DIRECTORY CUST_DIR AS ‘/usr/tmp’;

GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code]

Then, you can refer to it in the call to fopen:

[code]
UTL_FILE.FOPEN(‘CUST_DIR’, ‘oraask_test.txt’, ‘W’);
[/code]

hope this help.