CODEWITHSUNDEEP
sqloracle-databaseplsql
med brk
med brk

Reputation: 1

pl/sql procedure with variable numbers of parameters

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('');
--     here i want to insert parameters in the table,
--     but the table attributes are not the same !!
  END IF; 
  NULL;
END INSERTDATA;

Upvotes: 0

Views: 820

Answers (3)

APC
APC

Reputation: 146239

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

The problem with such a universal procedure is that something needs to know the structure of the target table. Your approach demands that the caller has to discover the projection of the table and arrange the parameters in a correct fashion.

In no particular order:

  1. This is bad practice because it requires the calling program to do the hard work regarding the data dictionary.
  2. Furthermore it breaks the Law Of Demeter because the calling program needs to understand things like primary keys (sequences, identity columns, etc), foreign key lookups, etc
  3. This approach mandates that all columns must be populated; it makes no allowance for virtual columns, optional columns, etc
  4. To work the procedure would have to use dynamic SQL, which is always hard work because it turns compilation errors into runtime errors, and should be avoided if at all possible.

It is trivially simple to generate a dedicated insert procedure for each table in a schema, using dynamic SQL against the data dictionary. This is the concept of the Table API. It's not without its own issues but it is much safer than what your question proposes.

Upvotes: 0

REW
REW

Reputation: 1247

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.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

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?

Upvotes: 1

Related Questions