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.

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.

You can find the following view to access all metadata about the columns :

user_tab_cols; — For all tables owned by the user

all_tab_cols ; — For all tables accessible to the user

dba_tab_cols; — For all tables in the Database.

and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column

[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;

if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]

hope this help.