CREATE OR REPLACE PROCEDURE sp_generate_csv (p_dir     IN  VARCHAR2, p_file IN VARCHAR2, p_query   IN  VARCHAR2) 
AS
BIGIN
.....................
END;

CREATE OR REPLACE PROCEDURE SP_GENERATE_RA_CSV
IS
      l_error_message VARCHAR2(512); -- for error message  
    l_file_name VARCHAR2(50);
    l_dir_name VARCHAR2(50) := 'CRMW_DEV_DIR';
    
    l_query VARCHAR2(32767) := 'SELECT * FROM book_dtls';
BEGIN

    SELECT trim(TO_CHAR(SYSDATE,'YYYYMMDD'))||'_ra_invoice.txt' AS fileName INTO l_file_name FROM dual;
    
    DBMS_OUTPUT.PUT_LINE('EXEC sp_generate_csv('''||l_dir_name||''', '''||l_file_name||''', '''||l_query||''');');
    
    EXECUTE IMMEDIATE 'EXECUTE sp_generate_csv('''||l_dir_name||''', '''||l_file_name||''', '''||l_query||''');';
   
    -- Exception handeling  
    EXCEPTION
    WHEN OTHERS THEN
      
   l_error_message := SQLERRM;
   
   INSERT INTO EXCEPTION_TABLE(ERROR_MSG,ERROR_SOURCE,ERROR_DATE)
   VALUES(l_error_message,'SP_GENERATE_RA_CSV',SYSDATE);
   
END SP_GENERATE_RA_CSV;

... EXCEPTION
WHEN OTHERS THEN

l_error_message := SQLERRM;

INSERT INTO EXCEPTION_TABLE(ERROR_MSG,ERROR_SOURCE,ERROR_DATE)
VALUES(l_error_message,'SP_GENERATE_RA_CSV',SYSDATE);

....

http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22

DO NOT do that, just do not. If you want to log an error, then:

a) create an autonomous transaction routine, eg:

create procedure log_error( .... )
as 
   pragma autonomous_transaction;
begin
   insert ....
   commit;
end;

and then code:

when others then
    log_error( ..... );
    RAISE;
end;

PLSQL is a programming language like any other - C, Java, VB, whatever. You call procedures rather "naturally" (you will have to lose many of your bad programming practices 'learned' from your sql server experience)


Your code will look like this:

ops$tkyte%ORA11GR1> CREATE OR REPLACE PROCEDURE SP_GENERATE_RA_CSV
  2  IS
  3      l_error_message VARCHAR2(512); -- for error message
  4      l_file_name VARCHAR2(50);
  5      l_dir_name VARCHAR2(50) := 'CRMW_DEV_DIR';
  6
  7      l_query VARCHAR2(32767) := 'SELECT * FROM book_dtls';
  8  BEGIN
  9
 10      l_file_name := trim(TO_CHAR(SYSDATE,'YYYYMMDD'))||'_ra_invoice.txt';
 11      sp_generate_csv( l_dir_name, l_file_name, l_query );
 12
 13  exception
 14      when others then
 15          log_error( sqlerrm );
 16          RAISE;
 17  END SP_GENERATE_RA_CSV;
 18  /

Procedure created.

I suggest, programmer to programmer, you read this document through before coding further. You would do that for Java, or C or VB - do it for PLSQL - you will not be sorry, you will be very surprised to find that a stored procedure language can be much more than a scripting language like t-sql - you can write mature, production quality code that follows all of the 'good practices' of real programming languages with it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/toc.htm