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

Question

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,

Solution 1

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;
/

Solution 2

It would have been more useful to show the report_setup.sql than the script that calls the procedure it creates... but from the symptoms you describe, the report_setup.sql doesn't have a / at the end of the procedure declaration.

It presumably has something like:

CREATE OR REPLACE PROCEDURE REPORT(NAME VARCHAR2, VERSION VARCHAR2,
  STARTDATE DATE, ENDDATE DATE) AS
...
BEGIN
  ...
END REPORT;

It needs to have

...
BEGIN
  ...
END REPORT;
/

Since you're running it from the command line with @ it should also have an EXIT at the end; but without the / that will be treated as part of the procedure, which is never compiled.

You can suppress the line number display, incidentally, by calling SQL*Plus with the -s flag - though at the moment they are useful since they show roughly what the problem is.

Comments

[Alex Poole]

Good spot; though if I understand the description in the question then it hasn't got as far as running this block yet. Also might as well point out that it's doing implicit date conversions when setting startdate and enddate (and there doesn't seem to be much point declaring/setting those anyway), which will probably fail one day.