Executing SQL with SQL Developer

Question

I have a set of SQL statements that I would like to execute to run a test

create table t2 (x varchar2 (1), y int, z date, constraint pk_t2 primary key (y, z) );
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 1: ');

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 2: ');

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Time 3: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

 -- Pause for 1 second.
sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
DBMS_OUTPUT.put_line('Final: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));


drop table t2;

If I select this set of statements in SQL Developer and click on 'RUN' I get errors like:

Error starting at line : 8 in command -
sys.DBMS_SESSION.sleep(1)
Error report -
Unknown Command

Do I have a syntax problem or a basic misunderstanding of how to run SQL commands in SQL Developer Studio?

EJ Egyed · Accepted Answer · 2020-10-19 13:59:36Z

You can not run PL/SQL code (DBMS_OUTPUT.PUT_LINE and DBMS_SESSION.SLEEP) outside of a PL/SQL block. You will need to wrap all your code in BEGIN/END and run your DML and insert statements as EXECUTE IMMEDIATE statements.

BEGIN
    EXECUTE IMMEDIATE 'create table t2 (x varchar2 (1), y int, z date, constraint pk_t2 primary key (y, z) )';
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 1: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 2: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Time 3: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    -- Pause for 1 second.
    sys.DBMS_SESSION.sleep (1);
    EXECUTE IMMEDIATE 'insert into t2 values (''a'', 111, sysdate )';
    DBMS_OUTPUT.put_line ('Final: ' || TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SS.FF'));

    EXECUTE IMMEDIATE 'drop table t2';
END;

kfinity · Accepted Answer · 2020-10-19 14:07:33Z

To build on EJ's answer (which puts the SQL inside a PL/SQL block) you can also run PL/SQL as part of a SQL script using anonymous BEGIN .. END blocks, or using exec (a sql*plus command).

create table t2 (x varchar2 (1), y int, z date, constraint pk_t2 primary key (y, z) );
insert into t2 values ('a', 111, sysdate );
-- anonymous block
begin 
    DBMS_OUTPUT.put_line('Time 1: ');
end;
/

-- Pause for 1 second.
-- using exec
exec sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
exec DBMS_OUTPUT.put_line('Time 2: ');

 -- Pause for 1 second.
exec sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
exec DBMS_OUTPUT.put_line('Time 3: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

 -- Pause for 1 second.
exec sys.DBMS_SESSION.sleep(1);
insert into t2 values ('a', 111, sysdate );
exec DBMS_OUTPUT.put_line('Final: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));


drop table t2;

I like exec for one-line commands, and anonymous blocks for larger amounts of code.

Collectives™ on Stack Overflow

Executing SQL with SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related