CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Shylock77
Shylock77

Reputation: 35

Stored procedure in Oracle PL/SQL

Is it possible to have a sql query like this embedded somewhere in a stored procedure :

Select id, val from prmtable where id in ('app','config');

Can this parameter list be initialised at runtime with any number of string values(both number and values of the strings at runtime) at the discretion of the user? If yes, then how is it done?

Upvotes: 3

Views: 1862

Answers (1)

Peter Lang
Peter Lang

Reputation: 55624

If you need a dynamic number of parameters, you could create a type

CREATE TYPE my_type AS TABLE OF VARCHAR2(128);

and pass this table as parameter for your procedure

CREATE OR REPLACE PROCEDURE my_procedure( vals IN my_type ) AS
BEGIN
  FOR c IN (
    SELECT dummy
      FROM dual
     WHERE dummy IN ( SELECT column_value FROM TABLE( vals ) )
  ) LOOP
    dbms_output.put_line( c.dummy );
  END LOOP;
END my_procedure;

This procedure can then be executed like this:

EXEC my_procedure( NEW my_type( 'X', 'Y' ) );

Upvotes: 7

Related Questions