CODEWITHSUNDEEP
oracle-databasesqlplus
Diogo Moreira
Diogo Moreira

Reputation: 1082

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

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?

Upvotes: 6

Views: 13944

Answers (3)

Sunil Karthikeyan
Sunil Karthikeyan

Reputation: 1

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;

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

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;
/

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191570

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

Upvotes: 16

Related Questions