3

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"

6

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 SQLPlus, the '/' tells SQLPlus 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.