Home » SQL & PL/SQL » SQL & PL/SQL » How to run PL/SQL scripts
How to run PL/SQL scripts [message #195492] Fri, 29 September 2006 01:21 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Hi,
I'm new to PL/SQL and I need some help on running PL/SQL scripts on Oracle version 9.2.0.7.0.

I am able to run normal SQL scripts but when I run PL/SQL scripts I get no output(no errors or message informing that the script was executed). Is there any setup needed before running PL/SQL scripts?

As a test I created the below two PL/SQL script but I not getting any output.


PL/SQL script 1:

declare
    test_var date;
  begin
    select sysdate into test_var from dual;
  end;
  /



PL/SQL script 2:
DECLARE
    x NUMBER;
BEGIN
    x := 72600;
 dbms_output.put_line('The variable X = ');
 dbms_output.put_line(x);
END;
/



Below is how I am executing the scripts (the scripts are located at the current directory):

SQL> @test.sql


Any help will be appreciated.
Re: How to run PL/SQL scripts [message #195507 is a reply to message #195492] Fri, 29 September 2006 02:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The first script won't provide any output because there is nothing in it that would produce any output.

The second script will produce output from the DBMS_OUTPUT IF you issue the command
SET SERVEROUTPUT ON
at the sql prompt before running the script.
Re: How to run PL/SQL scripts [message #195520 is a reply to message #195492] Fri, 29 September 2006 03:16 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
I executed the command "SET SERVEROUTPUT ON" before running the script but there's still no output.

By the way when a PL/SQL script get executed without any errors shouldn't a message "PL/SQL procedure successfully completed" gets displayed? In my case I'm not getting this message or any errors and a new SQL promt just gets displayed.

Do I have to change any settings in Oracle to enable PL/SQL?
Re: How to run PL/SQL scripts [message #195523 is a reply to message #195520] Fri, 29 September 2006 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
could you upload your second scriptfile?
Re: How to run PL/SQL scripts [message #195524 is a reply to message #195492] Fri, 29 September 2006 03:56 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Well, this script

declare
    test_var date;
  begin
    select sysdate into test_var from dual;
  end;
  /


won't display anything at all - there's not "printing" instruction in here... Wink

Second one, instead, works fine for me.
Re: How to run PL/SQL scripts [message #195526 is a reply to message #195520] Fri, 29 September 2006 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
Open a new sql*plus session
Set serveroutput on
Copy and paste the block from the second file into sql*plus
Run the pl/sql.

Do you get any output?
Re: How to run PL/SQL scripts [message #195622 is a reply to message #195526] Fri, 29 September 2006 13:31 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
to get "PL/SQL procedure successfully completed", do this:
set feedback on
Re: How to run PL/SQL scripts [message #195924 is a reply to message #195492] Tue, 03 October 2006 03:11 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Thank you all for your feedback.

I tried "SET SERVEROUTPUT ON" and "SET FEEDBACK ON" and ran the script but I'm still not getting any output.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK ON
SQL> @script2.sql
SQL>
SQL> !more script2.sql
DECLARE
    x NUMBER;
BEGIN
    x := 72600;
 dbms_output.put_line('The variable X = ');
 dbms_output.put_line(x);
END;
/

SQL>
Re: How to run PL/SQL scripts [message #195926 is a reply to message #195492] Tue, 03 October 2006 03:21 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
When I pasted the PL/SQL source code directly into the sqlplus section it worked. But how do I run it from a script file?
Re: How to run PL/SQL scripts [message #195929 is a reply to message #195924] Tue, 03 October 2006 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it's not the code that's the problem.

SQL> set serveroutput on
SQL> set feedback on
SQL> @c:\temp\script.sql
The variable X =
72600

PL/SQL procedure successfully completed.


Re: How to run PL/SQL scripts [message #195934 is a reply to message #195492] Tue, 03 October 2006 03:48 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

Have you tried
set term on


I tried this both ways

SQL> set term off
SQL> @test
SQL> set term on
SQL> @test
The variable X =
72600
 
PL/SQL procedure successfully completed.
 
SQL>


Re: How to run PL/SQL scripts [message #195935 is a reply to message #195492] Tue, 03 October 2006 03:49 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
I don't know if this will help but I am running Oracle on HP Unix version B.11.11.
Re: How to run PL/SQL scripts [message #195936 is a reply to message #195492] Tue, 03 October 2006 03:51 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Thanks scorpio_biker!!!

It worked by running "set term on".

cheers
Steve
Re: How to run PL/SQL scripts [message #195941 is a reply to message #195936] Tue, 03 October 2006 04:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
go look for a file called login.sql or glogin.sql
This file must have a 'set termout off' or something alike in it.

(probably for some batch jobs)
Re: How to run PL/SQL scripts [message #196079 is a reply to message #195524] Wed, 04 October 2006 01:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> ed
Wrote file afiedt.buf

1 declare
2 test_var date;
3 begin
4 select sysdate into test_var from dual;
5 dbms_output.put_line ( test_var);
6* end;
SQL> /
04-OCT-06

PL/SQL procedure successfully completed.
Re: How to run PL/SQL scripts [message #196191 is a reply to message #196079] Wed, 04 October 2006 08:05 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And what additional information did this posting offer?
Previous Topic: Urgent Help on Sql Query
Next Topic: Cost based and Rule Based Optimization
Goto Forum:
  


Current Time: Sun Dec 11 06:16:38 CST 2016

Total time taken to generate the page: 0.11418 seconds