PL/SQL execute immediate parameter with cursor

Question

CREATE OR REPLACE PROCEDURE ZAD_CZWARTE AS 

row_num number;
sql_stmnt varchar2(200);
cursor kursor is
select table_name from user_tables;
tab_name varchar2(200);
BEGIN

  FOR c_kur IN kursor LOOP
  tab_name := c_kur.table_name;
  --dbms_output.put_line(tab_name);
  sql_stmnt :='SELECT COUNT(*) FROM :1';
  execute immediate sql_stmnt into row_num using tab_name;
  DBMS_OUTPUT.PUT_LINE(tab_name||' : '||row_num);

  END LOOP;

END zad_czwarte;

Theres a problem with execute immediate line. Anybody knows whats the problem? everything seems just fine for me. The task is to output table names of the user with number of rows in them.

Possible duplicate of How to add a table name in EXECUTE IMMEDIATE query? — Lukas Eder
– Lukas Eder, Commented Nov 8, 2015 at 11:21

Husqvik · Accepted Answer · 2015-11-06 13:49:01Z

4

You can't bind physical table as bind variable. Your command must be sql_stmnt :='SELECT COUNT(*) FROM ' || tab_name; and then execute immediate sql_stmnt into row_num;

answered Nov 6, 2015 at 13:49

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

PL/SQL execute immediate parameter with cursor

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related