pl/sql procedure with variable numbers of parameters-CodePudding

I want to know if I can create a PL/SQL procedure that the number of parameters and their types changes.

For example procedure p1.

I can use it like this

p1 (param1, param2,......., param n);

i want to pass table name and data in procedure, but the attributes change for every table,

create or replace PROCEDURE INSERTDATA(NOMT in varchar2) is num int;
BEGIN
   EXECUTE IMMEDIATE 'SELECT count(*) FROM user_tables WHERE table_name = :1' into num using NOMT ;
  IF( num < 1 )
  THEN  
        dbms_output.put_line('table not exist !!! ');
  ELSE
       dbms_output.put_line('');
--       i want to insert parametrs in the table, but the table attributs are not the same !!
  END IF; 
  NULL;
END INSERTDATA;

CodePudding user response：

As far as I can tell, no, you can not. Number and datatypes of all parameters must be fixed.

You could pass a collection as a parameter (and have different number of values within it), but - that's still a single parameter.

Where would you want to use such a procedure?

CodePudding user response：

If you need to store, update and query a variable amount of information, might I recommend switching to JSON queries and objects in Oracle. Oracle has deep support for both fixed and dynamic querying of json data, both in SQL and PLSQL.