Execute Immediate bind variable

Question

I have the following function that calculates content of the table but when I pass any param it throws:

EXEC DBMS_OUTPUT.PUT_LINE(get_size('employees'))
Error report -
ORA-00903: invalid table name
ORA-06512: at "HR.GET_SIZE", line 5
ORA-06512: at line 1
00903. 00000 -  "invalid table name"

Function

CREATE OR REPLACE FUNCTION get_size(v_table_name IN VARCHAR2) 
   RETURN NUMBER IS total_size NUMBER(16);
   plsql_statement VARCHAR2(500) := 'SELECT COUNT(*) FROM  :param';
BEGIN
    EXECUTE IMMEDIATE plsql_statement INTO total_size USING v_table_name;
    RETURN(total_size); 
END;
/


EXEC DBMS_OUTPUT.PUT_LINE(get_size('employees'));

possible duplicate of Why cannot I use bind variables in DDL/SCL statements in dynamic SQL? — Sathyajith Bhat
– Sathyajith Bhat, Commented Mar 12, 2015 at 9:12

Community · Accepted Answer · 2017-05-23 11:56:50Z

3

You can't bind table names, it needs to be constructed.

CREATE OR REPLACE FUNCTION get_size(v_table_name IN VARCHAR2) 
   RETURN NUMBER IS total_size NUMBER(16);
   plsql_statement VARCHAR2(500)
BEGIN
    plsql_statement := 'SELECT COUNT(*) FROM  ' || v_table_name;
    EXECUTE IMMEDIATE plsql_statement INTO total_size;
    RETURN(total_size); 
END;
/


EXEC DBMS_OUTPUT.PUT_LINE(get_size('employees'));

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Mar 12, 2015 at 9:12

Sathyajith Bhat

22k22 gold badges101 silver badges140 bronze badges

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

2 Comments

Lalit Kumar B Over a year ago

USING v_table_name are you sure?

Sathyajith Bhat Over a year ago

missed correcting that one when pasted from question

Lalit Kumar B · Accepted Answer · 2015-03-12 09:25:47Z

You cannot bind table names or column names. You can bind only variables.

In your case, you just need to EXECUTE IMMEDIATE the dynamic SQL.

SQL> CREATE OR REPLACE FUNCTION get_size(v_table_name IN VARCHAR2)
  2     RETURN NUMBER IS total_size NUMBER(16);
  3     plsql_statement VARCHAR2(500);
  4  BEGIN
  5      plsql_statement := 'SELECT COUNT(*) FROM  ' || v_table_name;
  6      EXECUTE IMMEDIATE plsql_statement INTO total_size;
  7      RETURN(total_size);
  8  END;
  9  /

Function created.

SQL>
SQL>
SQL> EXEC DBMS_OUTPUT.PUT_LINE(get_size('EMP'));
14

PL/SQL procedure successfully completed.

SQL>

Collectives™ on Stack Overflow

Execute Immediate bind variable

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related