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

2
votes

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,

3
oraclestored-proceduresplsqlsqlplusoracle-sqldeveloper

3 Answers

3
votes

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

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.

0
votes

I had a similar issue. The problem was the encoding used, sqlplus expects UTF-8 standard enconding, wherever different encoding cause weird behavior.