Why I cannot run this script in SQL Developer?

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

Answers


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

Need Your Help

ipython notebook rmagic extension blank lines in output

ipython ipython-notebook

When I run any %R code with ipython notebook (windows version), the output contains a 480 x 480 blank image before the result. I have applied the fix #2433 to get rmagic to work on windows - not sure

NSJSONSerialization with C# Rest Service

c# iphone objective-c json

So I'm a complete noob with C# and web stuff and trying to figure something out. There is some code that says this: