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.

1. Procedure may or may not return value where as function should return one value.

2. Function can be called from SQL statement where as procedure can’t be called from the SQL statement.

3. Function are normally used for computation where as procedure are normally used for executing business logic.

4. Stored procedure is pre-compiled execution plan where as function are not.

5. We can call function within procedure but we can not call procedure within function.

6. A FUNCTION must be part of an executable statement, as it cannot be executed independently, whereas procedure represents an independent executable statement.

and for example of both syntax of function and procedure :

[code]CREATE OR REPLACE PROCEDURE test_proc
(p_id IN VARCHAR2) as begin … end

CREATE OR REPLACE FUNCTION test_func
(p_id IN VARCHAR2) return varchar2 as begin … end[/code]

hope this help.

one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)

ex:

[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]

and here you can call the function created above to identify the value passed is number or not like :

[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]

hope this help you.