Oracle PL/SQL Error ORA-06512 and ORA-00911 giving me issues

1
votes

I have the following PL/SQL Procedure:

create or replace 
PROCEDURE DboOpdracht2(
  table_name_in IN VARCHAR2,
  amount_rows_in IN NUMBER)
IS
  insert_query VARCHAR2 (2000);
  --cursors voor tabel layout
  CURSOR table_cur IS
  SELECT * FROM User_tab_cols
  WHERE table_name = UPPER(table_name_in);

BEGIN
  FOR COUNT IN 1 .. amount_rows_in
  LOOP
  BEGIN
    insert_query := 'INSERT INTO ';
    insert_query := insert_query || UPPER(table_name_in);
    insert_query := insert_query || ' VALUES(';
    --loop voor elke cursor om te vullen
    FOR col IN table_cur
    LOOP
      --dbms_output.put_line(col.data_type);
      CASE col.data_type
      WHEN 'NUMBER' THEN
      insert_query := insert_query || ' dbms_random.value(0, (power(10,'||col.data_precision||')-1)/power(10,'||col.data_scale||'))';
      WHEN 'VARCHAR2' THEN
      insert_query := insert_query || ' dbms_random.string(''A'',' || col.data_length || ')';
      WHEN 'TIMESTAMP(6)' THEN
      insert_query := insert_query || ' ' || current_timestamp;
      ELSE
      insert_query := insert_query || ' NULL ';
      END CASE;
      --add comma
      insert_query := insert_query || ' ,';
    END LOOP;
    insert_query := SUBSTR(insert_query,1,LENGTH(insert_query)-1);
    insert_query := insert_query || ' );';
    dbms_output.put_line(insert_query);
    EXECUTE IMMEDIATE insert_query;
    insert_query := '';
    --EXCEPTION
    --WHEN OTHERS THEN
      --dbms_output.put_line('Exception! Something went wrong.');
      --insert_query:='';
    END;
  END LOOP;
  COMMIT;   
END;

Upon trying to run it with the IN values:

(AUTEUR , 10)

It gives me the following error: ORA-00911: invalid character ORA-06512: at "SYSTEM.DBOOPDRACHT2", line 38 ORA-06512: at line 8

On line 38 there is a print that shows me the INSERT statement i generated in the loop which is:

INSERT INTO AUTEUR VALUES( dbms_random.value(0, (power(10,10)-1)/power(10,0)) , dbms_random.string('A',64) , dbms_random.string('A',255) , dbms_random.string('A',255) , dbms_random.string('A',2048)  );

Dump part is, this INSERT as is, just works fine! ,however somehow there is an issue with this whole procedure whenever i add the line: EXECUTE IMMEDIATE...

Any idea why this causes a problem while the actual string is no issue at all if run on it's own?

Thanks in advance, Smiley

1
sqlplsqloracle11goracle-sqldeveloper

1 Answers

4
votes

Try to remove ; character from concatenated string in line:

insert_query := insert_query || ' );';

so EXECUTE IMMEDIATE's query won't end with it:

insert_query := insert_query || ' )';