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,

Njal · Accepted Answer · 2013-07-31 16:21:00Z

3

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

answered Jul 31, 2013 at 16:21

Njal

5775 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

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.

Alex Poole · Accepted Answer · 2013-07-31 15:10:21Z

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.

ETO · Accepted Answer · 2019-01-22 18:01:03Z

0

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

edited Jan 22, 2019 at 18:01

ETO

7,3471 gold badge23 silver badges40 bronze badges

answered Jan 22, 2019 at 17:48

user10951635

1

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related