Stored Procedure with multiple IN Parameter

Question

I got the following Procedure:

create or replace PROCEDURE create_indexes 
(tbl_name_index IN VARCHAR2, 
tbl_name_vehicle IN VARCHAR2, 
tbl_name_dealer IN VARCHAR2, 
tbl_name_hst IN VARCHAR2, 
tbl_name_dms IN VARCHAR2, 
tbl_name_usertype IN VARCHAR2, 
tbl_name_search IN VARCHAR2) as

  COUNT_INDEXES INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO COUNT_INDEXES
  FROM USER_INDEXES
  WHERE table_name = tbl_name_index and index_name not like '%UNIQUE%';

  IF COUNT_INDEXES <= 0 THEN
         EXECUTE IMMEDIATE 'COMMAND';


    end If;
end;

As you can see there are several parameters defined. My problem is, how can I execute this procedure with multiple inputs?

Answer 1

From an SQL Worksheet, call your procedure from an anonymous block:

begin
  create_indexes (
    tbl_name_index    => 'TABLE_NAME',
    tbl_name_vehicle  => 'Vehicle name',
    tbl_name_dealer   => 'value 3',
    tbl_name_hst      => 'value 4',
    tbl_name_dms      => 'value 5',
    tbl_name_usertype => 'value 6',
    tbl_name_search   => 'value 7');
end;
/

As Jeffrey Kemp noted, it's a good idea to use the named parameter notation, and not positional notation; it makes it clearer, helps avoids mistakes (particularly getting the order of arguments wrong), can reduce the impact of future changes to the procedure, and is more flexible when you have parameters with default values - so you can skip any you don't want to explicitly set.

You can also use the execute command as a shortcut, but with a lot of parameters it's probably easier to stick to an anonymous block - it's the same under the hood anyway. A lot of the SQL*Plus documentation also applied to SQL Developer.

The SQL Developer documentation also shows you how to execute and debug a procedure.

If this is your first foray into PL/SQL then it might be worth reviewing the documentation. I'm not sure where you're going with the code you've shown; it seems to be a bit confused. It's unusual to create objects like indexes in PL/SQL as they should be built once when the schema is created or updated, so having it as reusable code seems a bit pointless.