0

Situation : I'm trying to run an Oracle Stored procedure from a Linux shell script. I created a .sql file called my_procedure.sql as shown below:

set serveroutput on
set serveroutput off
DECLARE
    my_variable NUMBER(10);
BEGIN
   exec schema.my_procedure();
END;
/
EXIT

The above .sql file is in turn called from a simple shell script called myscript.sh like this:

sqlplus -s /@connection_sid @my_procedure

Problem: When I execute sh myscript.sh, it throws an error in the my_procedure.sql file as below:

Encountered the symbol 'schema' when expecting one of the following
:= . ( @ % ;

What I've tried so far : I guess-worked by removing the exec keyword. Then I tried using username.schema.myprocedure, but nothing worked.

This procedure runs fine from the command line thus:

sqlplus -s username/password <<END
exec myprocedure();
END;

Any suggestions for this shall be very helpful.

Abhishek
  • 743
  • 1
  • 15
  • 28
  • What do you mean with "but nothing worked", after removing the `exec`? `exec` is for SQLPlus, and you can not use it within a PlSQL block. – Aleksej Aug 06 '19 at 10:43
  • Removing exec gives the error: "Identifier 'schema.my_procedure' must be declared". Where should it be declared ? I just saved and ran it within the schema. – Abhishek Aug 06 '19 at 10:48
  • https://stackoverflow.com/a/44283970/7998591 – Kaushik Nayak Aug 06 '19 at 10:52
  • Kaushik, in my .sql file, neither "exec" nor removing exec worked. Only the error message differed. With "exec", the error message is "Encountered the symbol schema..." and without it, the error message is, "Identifier schema.my_procedure" must be declared. – Abhishek Aug 06 '19 at 12:04

1 Answers1

0

This line:

exec schema.my_procedure();

in your procedure should be just

schema.my_procedure();
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi Littlefoot. I just tried that but it gives the error: "Identifier 'schema.my_procedure' must be declared". Where should it be declared ? I just saved and ran it within the schema. – Abhishek Aug 06 '19 at 10:49
  • Does user - which runs the procedure - have `execute` privilege on that procedure? Its owner should grant it directly to you (i.e. not via role). – Littlefoot Aug 06 '19 at 10:58
  • Littlefoot, that worked ! I went to Oracle SQL Developer, right clicked on the Procedure, then clicked Grant, searched for the user and granted all rights. – Abhishek Aug 06 '19 at 12:08
  • OK, I'm glad you fixed it. – Littlefoot Aug 06 '19 at 13:01
  • Thanks. The error message is quite misleading though, and can send one on a wild goose chase, whereas the real issue is about execute privileges. – Abhishek Aug 08 '19 at 12:41