CREATE OR REPLACE PROCEDURE delete_rows (table_name IN USER_TABLES.table_name%type, row_count in number)
IS
BEGIN
      execute immediate 'delete from'||table_name||' where rowid in (select rowid from table_name fetch first Row_count rows only)';
      dbms_output.put_line(sql%rowcount);
END;
/

I get this error:

PLS-00357: Table View Or Sequence reference 'EMPLOYEES' not allowed in this context

CodePudding user response：

There are 2 mistakes.

1. You are missing a space after from.
2. row_count must be passed as parameter.

create or replace procedure delete_rows (table_name IN USER_TABLES.table_name%type, row_count in number)
IS
BEGIN
      execute immediate 'delete from '||table_name||' where rowid in (select rowid from table_name fetch first '|| Row_count || ' rows only)';
      dbms_output.put_line(sql%rowcount);
END;
/

CodePudding user response：

When working with dynamic SQL, it is almost ALWAYS useful to prepare statement you'll execute, review it and - once you're satisfied with it - actually execute it. Here's what your code is trying to do:

SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE delete_rows
  2    (table_name IN USER_TABLES.table_name%type,
  3     row_count  in number)
  4  IS
  5    l_str varchar2(200);
  6  BEGIN
  7    l_str := 'delete from'||table_name||' where rowid in ' ||
  8             '(select rowid from table_name fetch first Row_count rows only)';
  9    dbms_output.put_line(l_str);
 10    -- execute immediate l_str;
 11    dbms_output.put_line(sql%rowcount);
 12  END;
 13  /

Procedure created.

SQL> exec delete_rows('test', 2);
delete fromtest where rowid in (select rowid from table_name fetch first
Row_count rows only)

PL/SQL procedure successfully completed.

SQL>

What do you think, can you really run it? Sure you can, but it'll fail:

SQL>     delete fromtest where rowid in (select rowid from table_name fetch first
  2      Row_count rows only)
  3  /
    delete fromtest where rowid in (select rowid from table_name fetch first
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Let's fix it. It is a good habit to precede parameters' names with a prefix so that you'd easily recognize them in the code; it also helps in where clause because - if you name the parameter the same as column, you'll get wrong result.

Also

, if you're worried about SQL injection, you could include call(s) to the `DBMS_ASSERT` package:

SQL> CREATE OR REPLACE PROCEDURE delete_rows
  2    (par_table_name IN USER_TABLES.table_name%type,
  3     par_row_count  in number)
  4  IS
  5    l_str varchar2(200);
  6    l_table_name user_tables.table_name%type;
  7  BEGIN
  8    l_table_name := dbms_assert.sql_object_name(par_table_name);
  9
 10    l_str := 'delete from ' || l_table_name ||' where rowid in ' ||
 11             '(select rowid from ' || l_table_name ||
 12             ' fetch first ' || par_row_count || ' rows only)';
 13    dbms_output.put_line(l_str);
 14    -- execute immediate l_str;
 15    dbms_output.put_line(sql%rowcount);
 16  END;
 17  /

Procedure created.

OK; what's the new result:

SQL> exec delete_rows('test', 2);
delete from test where rowid in (select rowid from test fetch first 2 rows only)

PL/SQL procedure successfully completed.

Does this work? Yes, it does:

SQL> delete from test where rowid in (select rowid from test fetch first 2 rows only);

2 rows deleted.

SQL> rollback;

Rollback complete.

SQL>

OK; the final fix of the procedure: include execute immediate:

SQL> CREATE OR REPLACE PROCEDURE delete_rows
  2    (par_table_name IN USER_TABLES.table_name%type,
  3     par_row_count  in number)
  4  IS
  5    l_str varchar2(200);
  6    l_table_name user_tables.table_name%type;
  7  BEGIN
  8    l_table_name := dbms_assert.sql_object_name(par_table_name);
  9
 10    l_str := 'delete from ' || l_table_name ||' where rowid in ' ||
 11             '(select rowid from ' || l_table_name ||
 12             ' fetch first ' || par_row_count || ' rows only)';
 13    -- dbms_output.put_line(l_str);
 14    execute immediate l_str;
 15    dbms_output.put_line('Deleted ' || sql%rowcount || ' row(s)');
 16  END;
 17  /

Procedure created.

SQL> exec delete_rows('test', 2);
Deleted 2 row(s)

PL/SQL procedure successfully completed.

SQL>