Can't create a table that does not exist with PL/SQL Dynamic SQL

Question

I was trying to check if a table exists, if it doesn't then it must be created BUT i'm getting an error below when I try to insert data into it when supposedly it is already created. Here is my script:

PROCEDURE CREAR_CLAVES
AS
  VERIFACION INTEGER;
BEGIN
  SELECT COUNT(TNAME) INTO VERIFACION FROM TAB WHERE TNAME = 'CLAVES';
  IF VERIFACION = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE CLAVES (IDESTUDIANTE NUMBER(5) PRIMARY KEY, USUARIO VARCHAR2(1000), CONTRASENA VARCHAR2(1000))';
    EXECUTE IMMEDIATE 'ALTER TABLE CLAVES ADD CONSTRAINT FK_IDESTUDIANTE FOREIGN KEY (IDESTUDIANTE) REFERENCES ESTUDIANTES(ID)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('LA TABLA YA EXISTE');
  END IF;
END CREAR_CLAVES;


PROCEDURE IDENTIFICAR_ESTUDIANTES
AS
  VESTUDIANTES SYS_REFCURSOR;
  ACTUAL ESTUDIANTES%ROWTYPE;
  CONSULTA VARCHAR2(1000);
BEGIN
  CONSULTA := 'SELECT ID, NOMBRE, APELLIDO FROM ESTUDIANTES';
  OPEN VESTUDIANTES FOR CONSULTA;
  FETCH VESTUDIANTES INTO ACTUAL;
  WHILE VESTUDIANTES%FOUND
  LOOP
    INSERT
    INTO CLAVES VALUES
      (
        ACTUAL.ID,
        LOWER(ACTUAL.APELLIDO)
        ||LOWER(ACTUAL.NOMBRE),
        LOWER(ACTUAL.NOMBRE)
        ||DBMS_RANDOM.VALUE(100, 999)
      );
    FETCH VESTUDIANTES INTO ACTUAL;
  END LOOP;
  CLOSE VESTUDIANTES;
END IDENTIFICAR_ESTUDIANTES;

I get the table or view doesn't exist error. Any idea how can I solve it?

Hi, it is not a good approach to create a table in pl/sql, Rethink in your solution, There is a better way to do the job without creating table like this in runtime, Maybe we can help if you provide us what you are trying to do — hmmftg
– hmmftg, Commented May 22, 2017 at 4:42
plus: if the table does exist, and it does already have the constraint you want to add: what then? — Paul Maxwell
– Paul Maxwell, Commented May 22, 2017 at 5:56
This is not a good approach. However, your actual problem may be that you don't insert a row to TAB once the table is created so each run through CREAR_CLAVES will see that a table needs creating even if one was created previously. Try a test from a 'clean' environment. — BriteSponge
– BriteSponge, Commented May 22, 2017 at 7:50
I know it is not a good practice to create the table on the go, but it is an excercise form a course i'm taking, thanks everyone for the help. — JMz
– JMz, Commented May 22, 2017 at 18:54

KFx · Accepted Answer · 2017-05-22 10:02:05Z

1

Most probably the problem is that the CLAVES does not exist when the procedure is compiled. It does not even get to the point where it checks its existence and creates it.

You can solve it by putting the 'INSERT INTO CLAVES...' statement into an execute immediate. Use bind variables there to avoid any possibility of sql injection:

CREATE or replace PROCEDURE IDENTIFICAR_ESTUDIANTES 
AS
    VESTUDIANTES SYS_REFCURSOR;
    ACTUAL ESTUDIANTES%ROWTYPE;
    CONSULTA VARCHAR2(1000);
BEGIN
    CONSULTA := 'SELECT ID, NOMBRE, APELLIDO FROM ESTUDIANTES';
    OPEN VESTUDIANTES FOR CONSULTA;
    FETCH VESTUDIANTES
    INTO ACTUAL;
    WHILE VESTUDIANTES%FOUND LOOP
        execute immediate 'INSERT INTO CLAVES VALUES (:IDESTUDIANTE, :USUARIO, :CONTRASENA)'
          using ACTUAL.ID, LOWER(ACTUAL.APELLIDO)||LOWER(ACTUAL.NOMBRE), LOWER(ACTUAL.NOMBRE)||DBMS_RANDOM.VALUE(100, 999);

        FETCH VESTUDIANTES
        INTO ACTUAL;
    END LOOP;
    CLOSE VESTUDIANTES;
END IDENTIFICAR_ESTUDIANTES;
/

BTW, I also disourage the creation of the CLAVES on-the-fly. Moreover, it would be better to implement the cursor loop and row-by-row (slow-by-slow) insert in a single 'insert as select' statment, unless you have a very specific, procedural-heavy logic here that transforms the ESTUDIATES rows into CLAVES.

answered May 22, 2017 at 10:02

KFx

1315 bronze badges

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

1 Comment

JMz Over a year ago

Thanks for the answer, as I said, it is a class excercise and i just wanted to know if it was possible.

Collectives™ on Stack Overflow

Can't create a table that does not exist with PL/SQL Dynamic SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related