{"id":2767,"date":"2013-10-25T02:37:28","date_gmt":"2013-10-25T02:37:28","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=2767"},"modified":"2015-10-27T08:23:38","modified_gmt":"2015-10-27T08:23:38","slug":"how-to-call-oracle-stored-procedure-in-oracmd","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/oracmd\/how-to-call-oracle-stored-procedure-in-oracmd.html","title":{"rendered":"How to call Oracle Stored Procedure in OraCmd"},"content":{"rendered":"

Is it possible to call a Stored Procedure within the OraCmd ?<\/p>\n

Yes, You can.<\/p>\n

For example, you have a Oracle stored procedure “test_procedure”,<\/p>\n

create or replace procedure test_procedure is\r\nbegin\r\n  delete from employees where employee_id < 100;\r\nend test_procedure;<\/pre>\n
In OraCmd, you can enter “call test_procedure();” to execute it.<\/p>\n
OraCmd> call test_procedure();<\/pre>\n
 <\/p>\n
You can also call function, for example, a function “test_function”,<\/p>\n
create or replace function test_function(first_name in varchar2, last_name in varchar2) return varchar2 is\r\n  Result varchar2(255);\r\nbegin\r\n  Result:=first_name||' '||last_name;\r\n  return(Result);\r\nend test_function;<\/pre>\n
You can enter”select test_function(‘John’,’Smith’) from dual;” to execute it.<\/p>\n
OraCmd> select test_function('John','Smith') from dual;<\/pre>\n
Wiil get<\/p>\n
TEST_FUNCTION('JOHN','SMITH')\r\n----------------------------\r\nJohn Smith<\/pre>\n
If you want to execute procedure on batch file, you need make a sql script file, like this:<\/p>\n
execute schema_name.test_procedure;<\/pre>\n
save this file as “D:\\data\\exe_test_procedure.sql”.<\/p>\n
then make a batch file, like this:<\/p>\n
\"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\"<\/pre>\n
save and run this batch file.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[1325,484],"class_list":["post-2767","post","type-post","status-publish","format-standard","hentry","category-oracmd","tag-oracle","tag-oracle-stored-procedure"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/2767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=2767"}],"version-history":[{"count":4,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/2767\/revisions"}],"predecessor-version":[{"id":2769,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/2767\/revisions\/2769"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=2767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=2767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=2767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}