Create procedure where table owner, name and row is passed as parameter-CodePudding

I'm trying to create a Procedure in Oracle PL/SQL where I pass the table owner's name, the table name and the column name to the procedure, so that I can print it to the screen.

The base of the procedure would be:

CREATE PROCEDURE table_printer(owner VARCHAR, table_name VARCHAR, column_name VARCHAR)
IS
...

My base idea was to pass it to a CURSOR inside the procedure but I learnt that you cannot really do that and I cannot seem to find the dynamic way of it.

My optimum result would be to iterate through the cursor of the given parameters and print out each row.

Thanks for the help!

CodePudding user response：

I'd think of a function that returns e.g. ref cursor which actually can be used elsewhere. With a dbms_output.put_line, you can use it only with tools that support it (e.g. SQL*Plus or SQL Developer); in other tools (e.g. Oracle Apex or Forms), you wouldn't see anything.

Here's an example: dynamic SQL it is. dbms_assert is used to prevent SQL injection.

SQL> create or replace function f_test
  2    (par_owner in varchar2, par_table_name in varchar2, par_column_name in varchar2)
  3    return sys_refcursor
  4  is
  5    l_str varchar2(200);
  6    rc    sys_refcursor;
  7  begin
  8    l_str := 'select ' || dbms_assert.simple_sql_name (par_column_name) ||
  9             ' from '  || dbms_assert.schema_name     (par_owner)       ||'.'||
 10                          dbms_assert.sql_object_name (par_table_name);
 11    open rc for l_str;
 12    return rc;
 13  end;
 14  /

Function created.

SQL> select f_test('SCOTT', 'DEPT', 'DNAME') result from dual;

RESULT
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS


SQL>

[EDIT: if it has to be dbms_output.put_line, then ...]

SQL> set serveroutput on
SQL>
SQL> create or replace procedure p_test
  2    (par_owner in varchar2, par_table_name in varchar2, par_column_name in varchar2)
  3  is
  4    l_str   varchar2(200);
  5    rc      sys_refcursor;
  6    --
  7    l_dname dept.dname%type;
  8  begin
  9    l_str := 'select ' || dbms_assert.simple_sql_name (par_column_name) ||
 10             ' from '  || dbms_assert.schema_name     (par_owner)       ||'.'||
 11                          dbms_assert.sql_object_name (par_table_name);
 12    open rc for l_str;
 13    loop
 14      fetch rc into l_dname;
 15      exit when rc%notfound;
 16      dbms_output.put_line(l_dname);
 17    end loop;
 18  end;
 19  /

Procedure created.

SQL> exec p_test('SCOTT', 'DEPT', 'DNAME');
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>