oracle - run stored procedure from script

Question

I've a script that I am using to build/drop tables and basically setting up the entire schema. After googling, I still can't figure out how to run a stored procedure.

The script is a .txt file, and I run it using Apex SQL Oracle.

If I write only this line in a script:

execute procedurename(1); --where 1 is paramter.

You have requested to run a script that does not contain any runnable statements.

Is it a script or a stored procedure? To run a script in sql*plus (not sure what Apex is), you'd preface it with @, e.g. @c:\temp\your_script.txt. — Marc
– Marc, Commented Apr 28, 2012 at 23:47
@Marc script is running fine. I just need that script o call the stored procedure. Does it make sense? Do you need more details? — TPR
– TPR, Commented Apr 28, 2012 at 23:58
The script should be able to call the stored proc with call packagename.procname(1); — Marc
– Marc, Commented Apr 28, 2012 at 23:59
@Marc what is packagename? how do i find out what's corresponding package name for my environment? or do I create a package from scratch for this task? — TPR
– TPR, Commented Apr 29, 2012 at 0:05
if your stored proc is not in a package, you don't need a package name. Just call procname(param); should work. — Marc
– Marc, Commented Apr 29, 2012 at 0:05

ShoeLace · Accepted Answer · 2012-04-30 02:18:03Z

2

SQL>create or replace procedure procedurename(p_num number) 
as 
begin 
null; 
end;
/

Procedure created.

SQL>execute procedurename(1);

PL/SQL procedure successfully completed.

everything seems ok on SQLPLUS with oracle 11.

so it must be an apex thing.

answered Apr 30, 2012 at 2:18

ShoeLace

3,6063 gold badges33 silver badges44 bronze badges

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

Comments

psaraj12 · Accepted Answer · 2012-04-29 04:28:53Z

2

Since execute is a sqlplus statement ,try calling the procedure using begin-end PLSQL block in Apex SQL

BEGIN
procedurename(1); 
END;
/

save this in a file proc_call.sql and then call it in your script like

 @C:\proc_call.sql

where C: is the sample path

For some information refer the below link

https://forums.oracle.com/forums/thread.jspa?threadID=618393

edited Apr 29, 2012 at 4:28

answered Apr 29, 2012 at 4:14

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

oracle - run stored procedure from script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related