2

I have a stored procedure in oracle i want to call that procedure from cygwin. This is the procedure 

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
 dbms_output.put_line('Hello World!');
 END;

i am doing this 

sqlplus -s system@orcl/oracle10g<<END
execute greetings();
commit;
exit;
END
lucifer
  • 2,297
  • 18
  • 58
  • 100
  • when i am doing sh test(it is the name of the vi editor file where i write this codes)it shows PLS-00201: identifier 'GREETINGS' must be declared – lucifer Aug 30 '13 at 10:25

3 Answers3

2

Connect Oracle using SqlPlus, then run the procedure like this:

execute package_name.procedure_name (parameters...)

If you want to call sqlplus from within Windows shell:

@echo execute some_procedure | sqlplus username/password@databasename

(See this question )

On Unix, try this:

echo "execute <some_procedure>" | sqlplus -s username/password@host:1521/service
Community
  • 1
  • 1
chance
  • 6,307
  • 13
  • 46
  • 70
  • Thanks chance but i have to call a stored procedure from shell if you do sqlplus then the shell environment will change to oracle and the procedure will run fine.But i have to retain the shell environment, and have to call a procedure – lucifer Aug 30 '13 at 07:40
  • disconnected frm oracle database – lucifer Aug 30 '13 at 08:40
  • $ echo "procPrintHelloWorld" | sqlplus -s system/oracle10g@orcl:1521/orcl – lucifer Aug 31 '13 at 07:00
  • this is i am doing and my procedure is CREATE OR REPLACE PROCEDURE procPrintHelloWorld IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; / – lucifer Aug 31 '13 at 07:01
  • you have to create that procedure first, then try to call it from your shell. – chance Aug 31 '13 at 09:36
0

try to add 

set serveroutput on

before execute greetings();

schurik
  • 7,798
  • 2
  • 23
  • 29
  • ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'GREETINGS' must be declared ORA-06550: line 1, column 7: – lucifer Aug 30 '13 at 13:58
  • in what schema is greetings defined? either connect as the owner of greetings(and not as system ) or set the shema name befor the procedure name: `execute SCOTT.greetings();` – schurik Aug 30 '13 at 16:07
0

sqlplus -s system/oracle10g@orcl @proc.sql this is working fine where system is my oracle username oracle10g is my pass and orcl is the localhost and the proc.sql is the file name where the procedure is stored.All we have to do is to go to specific directory from shell then execute this script

lucifer
  • 2,297
  • 18
  • 58
  • 100