When executing a script on SQLPlus, it prints a sequence of numbers instead of output

Question

I'm trying to execute a script on SQL PLus, it's simple.

SET serveroutput ON;
DECLARE
    mode NUMBER(1) := 1;

IF (mode = 1) THEN

    prompt 'HERE'

END IF;

prompt 'fim'

I call the script from SQLPlus using sqlplus user/pw@db and @myscript.sql after a successful connection. But the output is strange for me:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @myscript.sql
 9
 10
 11

And it continues to print this sequence indefinitely. What am I doing wrong?

Definitely you get here printed and nothing else unless you have something else in your script. — Srini V
– Srini V, Commented Jan 7, 2014 at 12:42
It prints code's line number, and it probably prints it when you hit enter. The reason for such a behavior is probably your myscript.sql file contains not terminated by / PL/SQL block. — Nick Krasnov
– Nick Krasnov, Commented Jan 7, 2014 at 12:50
I don't think you're executing the script you think you are. (It does look like it contains a PL/SQL block without a termination). Is it in the same directory you launched SQL*Plus from? Do you have SQLPATH set in your environment? — Alex Poole
– Alex Poole, Commented Jan 7, 2014 at 12:50
till u give a '/', your script contents as PL/SQL block will be never sent to oracle, and it waits showing number starting from last line number of ur script as you keep pressing Enter! — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jan 7, 2014 at 12:58

Alex Poole · Accepted Answer · 2014-01-07 12:59:02Z

16

From your edited question... you have to terminate the PL/SQL block with a / on a new line to make it end and run, otherwise SQL*Plus will keep prompting for more lines of code (which is the numbers you're seeing). The documentation shows how to run PL/SQL blocks. And prompt is a SQL*Plus command so you can't use it inside a PL/SQL block. You also don't have your block syntax right:

SET serveroutput ON;
DECLARE
    mode NUMBER(1) := 1;
BEGIN
    IF mode = 1 THEN
        DBMS_OUTPUT.PUT_LINE('HERE');    
    END IF;
END;
/

prompt fim

answered Jan 7, 2014 at 12:59

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

@DiogoMoreira - please read the documentation to make sure you understand why it works now, and why your version didn't work. As well as the SQL*Plus guide, you should look the PL/SQL language reference.

psaraj12 · Accepted Answer · 2014-01-07 13:04:15Z

3

You cannot use sqlplus command in plsql.We can use dbms_output instead which will display the output in SQL prompt

SET serveroutput ON;
DECLARE
mode NUMBER(1) := 1;
BEGIN
IF (mode = 1) THEN
dbms_output.put_line('HERE');
END IF;
dbms_output.put_line('fim');
END;
/

edited Jan 7, 2014 at 13:04

answered Jan 7, 2014 at 12:57

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Comments

Antimony · Accepted Answer · 2017-10-10 19:57:04Z

0

Go to your oracle home Oracle\product\<version>\client_2\sqlplus\admin\glogin.sql and add the following lines to enable printing globally,

SET ECHO ON;
SET TERM ON;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
SET DEFINE OFF;

edited Oct 10, 2017 at 19:57

Antimony

2,2403 gold badges28 silver badges39 bronze badges

answered Oct 10, 2017 at 16:13

Sunil Karthikeyan

12 bronze badges

1 Comment

Mark Stewart Over a year ago

I don't think that solves the actual problem; your answer will show what is being executed, but the comments to the original post answer the question.

Collectives™ on Stack Overflow

When executing a script on SQLPlus, it prints a sequence of numbers instead of output

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related