Why I cannot run this script in SQL Developer?

Question

Guys I do really need your help. I have written few lines of code and now I am stuck because I cannot run below procedure in SQL Developer.

PROCEDURE

create or replace 
PROCEDURE ROUTSETTER(
    var IN VARCHAR2)
IS
  COUNT_RECORDS NUMBER       := 0;
  BASE_TABLE    VARCHAR2(20) := 'DROGA';
  NEW_TABLE     VARCHAR2(20) := 'DROGA_COPY';
BEGIN
  SELECT COUNT(*)
  INTO COUNT_RECORDS
  FROM ALL_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'
  AND OBJECT_NAME   = NEW_TABLE;
  IF COUNT_RECORDS  > 0 THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE '||NEW_TABLE;
    dbms_output.put_line('TABLE '||NEW_TABLE||' HAS BEEN TRUNCATED');
  ELSE
    EXECUTE IMMEDIATE 'CREATE TABLE '||NEW_TABLE||' AS SELECT * FROM '||BASE_TABLE||' WHERE WYCENA = '||var;
    dbms_output.put_line('TABLE '||NEW_TABLE||' HAS BEEN CREATED');
  END IF;
END ROUTSETTER;

ERROR

ORA-00933: SQL command not properly ended
ORA-06512: at "PROJEKT.ROUTSETTER", line 17
ORA-06512: at line 6

Do you mean you can't create it or really that you can't run it? If the latter, how are you trying to run it? — Joachim Isaksson
– Joachim Isaksson, Commented Mar 12, 2016 at 16:03

Joachim Isaksson · Accepted Answer · 2016-03-12 17:41:08Z

1

Your line;

EXECUTE IMMEDIATE 'CREATE TABLE '||NEW_TABLE||
     ' AS SELECT * FROM '||BASE_TABLE||' WHERE WYCENA = '||var;

...puts var (which is a varchar) straight into an SQL statement without appropriate quoting.

If we assume var is free from single quotes, your line should be something like;

EXECUTE IMMEDIATE 'CREATE TABLE '||NEW_TABLE||
     ' AS SELECT * FROM '||BASE_TABLE||' WHERE WYCENA = '''||var||'''';

answered Mar 12, 2016 at 17:41

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

2 Comments

Gee Bee Over a year ago

Yes! I found it is useful to print out any dynamically created SQL statements prior to call execute immediate. That helps pointing out these simple but nasty bugs.

Joachim Isaksson Over a year ago

@GeeBee Adding what commands you use to print them may be useful for others with the same problem :)

Collectives™ on Stack Overflow

Why I cannot run this script in SQL Developer?

PROCEDURE

ERROR

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

PROCEDURE

ERROR

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related