How to map the output parameter with Dynamic sql

[naga_92]

Hello All,

I want to map the output parameter with Dynamic sql variable out_tab

Exec 'out_tab =' || select * from temp';

Please assist

Thanks

Nagendra

[Amin_Omidy]

Hi Negenda,

You follow a code like below:

DECLARE
  out_tab YOUR_TABLE_TYPE; -- replace YOUR_TABLE_TYPE with the appropriate type for yours
  cursor_var CURSOR FOR SELECT * FROM temp;
BEGIN
  OPEN cursor_var;
  FETCH cursor_var INTO out_tab;
  WHILE (SQLSTATE = '00000')
  DO
    -- process each row of results here
    FETCH cursor_var INTO out_tab;
  END WHILE;
  CLOSE cursor_var;
END;

YOUR_TABLE_TYPE should be replaced with the appropriate type for your table. You'll also need to modify the SELECT statement in the cursor declaration to retrieve those columns you need from the temp table.

The OPEN statement opens the cursor, and the FETCH statement retrieves the first row of results into the out_tab variable. The WHILE loop processes each row of results as needed until there are no more rows to fetch. The SQLSTATE variable is used to check if the fetch was successful or not. If the fetch was successful, the value of SQLSTATE will be '00000'. If the fetch failed, the value of SQLSTATE will be different.

Finally, the CLOSE statement closes the cursor.

Hope this helps,