Running Oracle stored Procedure

Question

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.

Answer 1

Try setting set serveroutput on before the dbms_output.

Answer 2

Hi, Thanks for your replies. But even though I have the EXECUTE command within it, but it is not running it when i call as @filename.sql. It just says Procedure compiled. How do I run it ?

Answer 3

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?

Answer 4

Hi Priya,

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>