Oracle – Creating an Oracle Stored Procedure and Executing it from .sql file

oracleoracle-sqldeveloperplsqlsqlplusstored-procedures

I have two .sql files both are Oracle stored procedures which both take in input parameters. I would like to first connect to a remote oracle database using sqlplus in command line and want to first use both files to create their respective stored procedures so I see them under procedures for that connection in Oracle SQL Developer.

After this I have two more .sql files which look like this and are designed to take input parameters and execute the stored procedures. This is one of the files that is meant to execute the stored procedure "REPORT".

 DECLARE
   NAME VARCHAR2(200);
   VERSION VARCHAR2(200);
   STARTDATE DATE;
   ENDDATE DATE;
BEGIN
   NAME := '&1';
   VERSION := '&2';
   STARTDATE := '&3';
   ENDDATE := '&4';

   exec REPORT(NAME, VERSION, STARTDATE, ENDDATE);
   EXCEPTION
   WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20101,SQLERRM);
   END;
   /

In command prompt I first try to create the stored procedure in the database by:
C:\Users\Desktop>sqlplus username/password @report_setup.sql

When I try this the output get is just empty lines that are numbered and beginning at the number that is 1 greater then the last line of my .sql file. My report_setup.sql file is 81 lines long and the output of the sqlplus command is blank numbered lines beginning at 83.

Please let me know how I can create and execute these stored procedures properly through sqlplus.

Thanks in advance,

Best Answer

I think you have to remove the 'exec'-word, and it's crucial to have the slash at the bottom at the very start of the line, with no spaces in front of it:

DECLARE
    NAME VARCHAR2(200);
    VERSION VARCHAR2(200);
    STARTDATE DATE;
    ENDDATE DATE;
BEGIN
    NAME := '&1';
    VERSION := '&2';
    STARTDATE := '&3';
    ENDDATE := '&4';

    REPORT(NAME, VERSION, STARTDATE, ENDDATE);
EXCEPTION
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20101,SQLERRM);
END;
/
Related Topic