oracle - Function the return number by using dynamic sql -

i want make function return data_byte later can use in rpad function. heres previous question have asked , got helpful replies. function return data type can used in sql(oracle)

here current function code add:

create or replace function get_data(tab_name in varchar2, column_name in varchar2) return number   return_val number;   str varchar2(1000) := 'select data_length user_tab_columns table_name = ' || ''''||tab_name||''''|| ' , column_name = ' || '''' || column_name || ''''; begin  dbms_output.put_line(str);   execute immediate(str);   return(return_val); end; / select get_data('employees', 'salary') dual; 

i keep bear in mind function need return type. after compiled function, tried select dual, , got null value. expected return 22 me got null. because return type wrong? or how should fix it.

your problem return_val not being populated. below code example works:

create or replace function get_data(tab_name in varchar2, column_name in varchar2) return number   return_val number;   str varchar2(1000) := 'select data_length user_tab_columns table_name = ' || ''''||tab_name||''''|| ' , column_name = ' || '''' || column_name || ''''; begin   dbms_output.put_line(str);   execute immediate(str) return_val;   return(return_val); end;