CODEWITHSUNDEEP
oracle-database
Priya
Priya

Reputation:

Running Oracle stored Procedure

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

Answers (4)

Colin Pickard
Colin Pickard

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.

SQL Developer
(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

Colin Pickard
Colin Pickard

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

Ian Carpenter
Ian Carpenter

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

Xaisoft
Xaisoft

Reputation: 46651

Try setting set serveroutput on before the dbms_output.

Upvotes: 0

Related Questions