Reputation:
I have a sql file that contains a simple procedure to print "Hi" like,
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi');
END;
When I try to execute this from sql file itself, it just gets compiled and it is not running.
I added the as,
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi');
END;
/ /* this is required else it throws me compilation error*/
set serveroutput on
EXECUTE skeleton;
I tried calling the same skeleton from other sql file also and even from the sqldeveloper GUI, that also didnt execute this. Only the sqlplus commandline helps me. Let me know what I am missing and the solution for this.
Upvotes: 0
Views: 6203
Reputation: 46673
In SQL Developer, you need the "execute script" button which is the second button at the top left (little green arrow in front of document) instead of the one with the big green arrow.
That threw me the first time I used it.
(source: devshed.com)
The reason your first example gets compiled and not run, is that you are not actually asking it to run. You are creating a procedure inside the database, which works correctly, but not calling it. In your second block you have an EXECUTE
which is where it actually runs.
Upvotes: 0
Reputation: 46673
The only change I had to make to your sql to allow running it as an @file was to remove the comment. This is the whole content of the the .sql file:
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi');
END;
/
set serveroutput on
EXECUTE skeleton;
You should get an output something like this:
C:\Temp>sqlplus username/password @skeleton.sql
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 5 17:10:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Procedure created.
Hi
PL/SQL procedure successfully completed.
SQL>
Upvotes: 1
Reputation: 8626
Here are the steps I took using SQL Plus
SQL> CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Hi');
5 END;
6 /
Procedure created.
SQL> set serveroutput on
SQL> EXECUTE skeleton;
Hi
PL/SQL procedure successfully completed.
SQL>
Can you start a new sqlplus session replicate these steps and post the content?
Upvotes: 2