Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL procedure issue (Windows)
PL/SQL procedure issue [message #667595] Sun, 07 January 2018 23:08 Go to next message
nicolewells
Messages: 2
Registered: December 2017
Junior Member
Hello All, I'm new to Oracle PL/SQL and have been trying to run this code but only getting the following message - PL/SQL procedure successfully completed.

SQL> DECLARE
v_num NUMBER:=10;
BEGIN
FOR v_num IN 1..3
LOOP
    DBMS_OUTPUT.PUT_LINE(v_num);
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_num);
END;

Help me on this,

Regards
Nicolewells


[EDITED by LF: removed link]

[Updated on: Mon, 08 January 2018 00:07] by Moderator

Report message to a moderator

Re: PL/SQL procedure issue [message #667596 is a reply to message #667595] Sun, 07 January 2018 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
it works OK for me

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_num NUMBER:=10;
BEGIN
FOR v_num IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_num);
END; 2 3 4 5 6 7 8 9
10 /
1
2
3
10

PL/SQL procedure successfully completed.

SQL>
Re: PL/SQL procedure issue [message #667597 is a reply to message #667596] Mon, 08 January 2018 03:38 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi nicolewells,

you have to execute command "set serveroutput on" before running the script.
Re: PL/SQL procedure issue [message #667645 is a reply to message #667597] Tue, 09 January 2018 07:02 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
please be aware that the "set serveroutput on" is a SQL*PLUS command
Re: PL/SQL procedure issue [message #667648 is a reply to message #667645] Tue, 09 January 2018 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; but this OP's line:

SQL> DECLARE

suggests that he *is* using SQL*Plus.

Besides, SET SERVEROUTPUT ON works in Oracle SQL Developer and is *required* if you want to see something. I thought that there must be some icon (button) that does that, but couldn't find it - you have to literally type that command. As if GUI emulates SQL*Plus as much as it can.
Re: PL/SQL procedure issue [message #667674 is a reply to message #667648] Wed, 10 January 2018 06:56 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Littlefoot wrote on Tue, 09 January 2018 07:13
Right; but this OP's line:

SQL> DECLARE

suggests that he *is* using SQL*Plus.

Besides, SET SERVEROUTPUT ON works in Oracle SQL Developer and is *required* if you want to see something. I thought that there must be some icon (button) that does that, but couldn't find it - you have to literally type that command. As if GUI emulates SQL*Plus as much as it can.
There is a button for that in SQL Dev

Unfortunately, I can't seem to past an image into these messages to show you where it it. When I open a connection and get the sql worksheet, I have four panes: The worksheet itself, a 'scrpit output' pane, a 'messages - log' pane, and a 'dbms output' pane. The dbms output pane has 4 icons - including a plus sign to turn on set dbms output on.
Re: PL/SQL procedure issue [message #667678 is a reply to message #667674] Wed, 10 January 2018 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which SQL Developer is it?

I use TOAD, but also have SQL Developer (version 4.1.3, an old one). I've just downloaded the latest version, 17.4.

In 4.1.3 I don't even see the "dbms output" pane and don't know how to open it (right-click doesn't do anything), but can see the result of DBMS_OUTPUT in "script output" pane once I enable it with SET SERVEROUTPUT ON. There's no "plus sign" either.

In 17.4 is just the same.

It's probably me, but - where is that "dbms output" pane? How to enable it? Oh yes, I know! Google is my friend, but only if I use it - it is in "View" menu > "Dbms output". Right, green plus icon is here!

Oh well.

Thank you, Ed.
Re: PL/SQL procedure issue [message #667687 is a reply to message #667678] Wed, 10 January 2018 12:05 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Littlefoot wrote on Wed, 10 January 2018 07:15
Which SQL Developer is it?

I use TOAD, but also have SQL Developer (version 4.1.3, an old one). I've just downloaded the latest version, 17.4.

In 4.1.3 I don't even see the "dbms output" pane and don't know how to open it (right-click doesn't do anything), but can see the result of DBMS_OUTPUT in "script output" pane once I enable it with SET SERVEROUTPUT ON. There's no "plus sign" either.

In 17.4 is just the same.

It's probably me, but - where is that "dbms output" pane? How to enable it? Oh yes, I know! Google is my friend, but only if I use it - it is in "View" menu > "Dbms output". Right, green plus icon is here!

Oh well.

Thank you, Ed.
Hmm. I really can't say. As much as I hate the answer "it's just there", that applies. I open SQL Developer it opens with 4 panes. On the entire left side is the pane with with tabs "Reports", "DBA", and "Connections", with "Connections" being the active tab. Extending from there all the way to the right border, on the top, is a pane with the "Start Page" tab. Under that, on the left (but to the right of the "Connections" tab), is a pane labeled "Dbms Output", with the icons I mentioned. To the right of that, and under the "Start Page" is a pane labeled "Messages - Log". When I open a connection, the "start page" pane gets a new tab, which is the worksheet for the connection I just opened. The two lower panes (Dbms output and Messages-Log) are still there. This is with v17 but it was the same with my old v4.
Re: PL/SQL procedure issue [message #667688 is a reply to message #667687] Wed, 10 January 2018 13:00 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I rarely use Sql Devleoper, but based on Ed's response, I started it up. I did not have the DBA windows or dbms_output window, but I went under View from the menu and clicked next to each of those and sure enough those windows showed up.
I used version 4.1.4.21.

[Updated on: Wed, 10 January 2018 13:01]

Report message to a moderator

Re: PL/SQL procedure issue [message #667689 is a reply to message #667688] Wed, 10 January 2018 13:19 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
joy_division wrote on Wed, 10 January 2018 13:00
I rarely use Sql Devleoper, but based on Ed's response, I started it up. I did not have the DBA windows or dbms_output window, but I went under View from the menu and clicked next to each of those and sure enough those windows showed up.
I used version 4.1.4.21.
Indeed! On your cue, I took another look. Each of the windows has a very small icon in the upper-right corner that allows you to dismiss the window. So I dismissed the 'dbms output' and 'Messages - Log', and they disappeared. Then went to the "View" menu and selected "dbms output" and "Log" and they re-appeared. Also play around with "docking" the windows or allowing them to float.

I'm a pretty rudimentary user of SQL Dev myself.
Re: PL/SQL procedure issue [message #667691 is a reply to message #667689] Wed, 10 January 2018 14:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, now it's three of us. Four more and we'll make the Fellowship of the Ring. I'll be the one that dies in all films he acts in (Sean Bean, that is Smile).
Re: PL/SQL procedure issue [message #667692 is a reply to message #667691] Wed, 10 January 2018 15:10 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
He survived in Flightplan.
Re: PL/SQL procedure issue [message #667698 is a reply to message #667692] Thu, 11 January 2018 00:17 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did he? It must be because something went wrong with that plan.
Previous Topic: Change the order of records in some variables
Next Topic: malformed cursor
Goto Forum:
  


Current Time: Fri Mar 29 03:23:26 CDT 2024