Alternative ideas for INTO CLAUSE in DYNAMIC SQL

Question

I just learned,I will not able to use INTO CLAUSE in stored procedure,while using dynamic sql.

Below is my Dynamic SQL.

SET V_SELECT =                   
  'SELECT ' || SELECT_FIELDS ||    
  ' FROM '  || TABLE_NAME    ||  
  ' WHERE ' || WHERE_CLAUSE  ||  
  ' WITH UR';    
EXECUTE IMMEDIATE V_SELECT;

requirement to use the procedure for multiple queries.

This is how my queries looks a like after passing values.

  SELECT B.PROD_TYP,
          A.PROD_LINE,
          B.PROD_TXT,
          B.PROD_TXT1
   FROM TABLE_A   A
   INNER JOIN TABLE_B  B
   ON A.ROW_ID = B.ROW_ID
   WHERE A.PROD_CD = HOST_VAR_PROD_CD;

I have to return the SELECT fields.How do I return value of these fields.These fields and table and query will change.

Answer 1

I used below code and it working fine. I was able to run multiple select's using Dynamic.

DECLARE DESC_CSR CURSOR WITH HOLD FOR V_SQL;  

  SET V_SELECT =                              
  'SELECT ' || SELECT_FIELDS ||               
  ' FROM '  || TABLE_NAME    ||               
  ' WHERE ' || WHERE_CLAUSE  ||               
  ' WITH UR';                                 


  PREPARE V_SQL FROM V_SELECT;  

  OPEN DESC_CSR USING HOST_VAR_B;

  FETCH DESC_CSR INTO HOST_VAR_A;

I pass parm as below. using the where clause HOST_VAR_B as Parameter marker "?" and assigning the field at OPEN.

SELECT_FIELDS = B.COLUMN_A  INTO HOST_VAR_A
TABLE_NAME =  TABLE_A A   INNER JOIN TABLE_B B      
              ON A.ROW_ID = B.ROW_ID
WHERE_CLAUSE = A.COLUMN_B = ?