I am currently trying to pass a parameter into a stored procedure and use this parameter when creating a cursor unsuccessfully. Here is a simplified snippet of the code I currently have for creating the stored procedure:

     CREATE OR REPLACE PROCEDURE PROC_MODEL_CREATE_TABLE_PROFILE(TABLE2PROFILE string)
        RETURNS TABLE()
        LANGUAGE SQL
        AS
        $$
        
       
        
        declare
        
            sql string;
            final_sql string;
            c1 cursor for (
            
        
        SELECT
    
        TABLE_NAME as TABLENAME
        from TABLE_OF_TABLES
        WHERE tablename LIKE ANY (' ||:TABLE2PROFILE|| ')
        ORDER BY TABLENAME;
            res resultset;
        begin
        
          final_sql := '';
          
          for record in c1 do
    sql := 'SELECT COUNT(*) AS Number_Of_Rows'
    FROM '||record.tablename||';

    final_sql := final_sql || sql;
        
  end for;
  
   final_sql := 'create or replace table data_profiles_LATEST as (' || final_sql || ')';

  res := (execute immediate :final_sql);
return table(res);

I then call the stored procedure using:

CALL PROC_MODEL_CREATE_TABLE_PROFILE('TABLE_OF_INTEREST');

However, when this stored procedure is run, it just runs the string ' ||:TABLE2PROFILE|| ' without passing the parameter. I can hard code 'TABLE2PROFILE' with 'TABLE_OF_INTEREST' it works fine. How can I pass a parameter into the stored procedure and use it in the cursor?

CodePudding user response：

There are syntactical issue in the code shared. As I understand you want to pass a multi-valued string, select based on that and then do further operations.

Please refer sample code below that shows same.

Notice how to use multi-values variable for LIKE ANY.

Notice how to use parameter in cursor variable. Refer this for details on passing parameter to cursor variables.

CREATE OR REPLACE PROCEDURE PROC_MODEL_CREATE_TABLE_PROFILE(TABLE2PROFILE string)
        RETURNS string
LANGUAGE SQL
AS
        $$
        declare
            sql string;
            final_sql string;
            c1 cursor for
SELECT *
from test_tab
where name like ANY (select value from table(split_to_table(?,',')));

        begin
  open c1 using (:TABLE2PROFILE);
          final_sql := '';
          for record in c1 do
    sql := 'SELECT  '
||record.name||';';
    final_sql := final_sql || sql;
  end for;
return final_sql;
end;
$$;

Procedure execution -

call PROC_MODEL_CREATE_TABLE_PROFILE('%oh%,%ar%');

Source table used in procedure -

select * from test_tab limit 1;