CODEWITHSUNDEEP
oracle-databasegenericsplsqlprocedure
Adam Paynter
Adam Paynter

Reputation: 46888

Generic-like behavior in PL/SQL procedure parameters

Suppose I have some data types defined in PL/SQL:

TYPE foo_t IS RECORD (...);
TYPE foo_table_t IS TABLE OF foo_t INDEX BY BINARY_INTEGER;

TYPE bar_t IS RECORD (...);
TYPE bar_table_t IS TABLE OF bar_t INDEX BY BINARY_INTEGER;

Is it possible for me to write a procedure capable of accepting any data type derived from TABLE (for example, either a foo_table_t or a bar_table_t) as a parameter? The procedure has no need for knowledge of the table's row types. A trivial example:

PROCEDURE remove_last(some_table ...) IS
BEGIN
    some_table.DELETE(some_table.LAST);
END;

Upvotes: 4

Views: 3441

Answers (1)

DCookie
DCookie

Reputation: 43523

Not directly. From the PL/SQL programmer's guide:

"The actual parameter and its corresponding formal parameter must have compatible datatypes."

PL/SQL does an implicit conversion of actual parameter datatypes to formal parameter datatypes. So, you could pass a number value to a procedure that wants a string, and it would work because you can do an implicit conversion.

The best you could do would be to write overloaded procedures:

PROCEDURE generic(foo IN OUT foo_t);

PROCEDURE generic(bar IN OUT bar_t);

Then you can call generic with either record type. This loses attractiveness in proportion to the number of record types to handle :-D

Upvotes: 7

Related Questions