How to call Oracle Stored Procedure in OraCmd

by

Posted on by

Is it possible to call a Stored Procedure within the OraCmd ?

Yes, You can.

For example, you have a Oracle stored procedure “test_procedure”,

create or replace procedure test_procedure is
begin
  delete from employees where employee_id < 100;
end test_procedure;

In OraCmd, you can enter “call test_procedure();” to execute it.

OraCmd> call test_procedure();

 

You can also call function, for example, a function “test_function”,

create or replace function test_function(first_name in varchar2, last_name in varchar2) return varchar2 is
  Result varchar2(255);
begin
  Result:=first_name||' '||last_name;
  return(Result);
end test_function;

You can enter”select test_function(‘John’,’Smith’) from dual;” to execute it.

OraCmd> select test_function('John','Smith') from dual;

Wiil get

TEST_FUNCTION('JOHN','SMITH')
----------------------------
John Smith

If you want to execute procedure on batch file, you need make a sql script file, like this:

execute schema_name.test_procedure;

save this file as “D:\data\exe_test_procedure.sql”.

then make a batch file, like this:

"C\Program Files\OraCmd\OraCmd" userid=username/password@localhost:1521:xe schema=hr script="D:\data\exe_test_procedure.sql" quit=y log="D:\data\exe_test_procedure.log"

save and run this batch file.