Friday, January 28, 2011

PL/SQL Plus script just gives me numbers

I have an PL/SQL Script that I'm trying to run from the command shell with SQLPLUS. However, whenever I go to run it, All I get back is a number and a cursor waiting for some input. When I hit enter, it just increments the number and repeats the process. Here is a dummy query that does the same thing

set serveroutput on

DECLARE
    cursor getServerTime IS
        SELECT sysdate as t  from dual;

    myTime getServerTime%ROWTYPE;
BEGIN
    open getServerTime;

    FETCH getServerTime into myTime;

    dbms_output.put_line(myTime.t);

    close getServerTime;
END;

to run this from the command shell I use: sqlplus me/myPass@myDB @"dummy.sql"

  • You need to terminate your script with another line after the END; containing a '/' in the first column, thusly:

    set serveroutput on
    
    DECLARE
        cursor getServerTime IS
            SELECT sysdate as t  from dual;
        myTime getServerTime%ROWTYPE;
    BEGIN
        open getServerTime;
        FETCH getServerTime into myTime;
        dbms_output.put_line(myTime.t);
        close getServerTime;
    END;
    /
    

    When you create a PL/SQL block via SQL*Plus, the '/' tells SQL*Plus to run everything you've put into the buffer since the last command executed. The incrementing numbers you're getting is SQL*Plus giving you the next line number in your script - it's waiting for you to tell it you're done.

    From DCookie

0 comments:

Post a Comment