Run a query script inside a for in loop (Oracle)

Question

I have a function as you can see below. I have a query string because i need to execute it with a parameter. My problem is: how can i put the query inside the "for"? I've tried with

EXECUTE IMMEDIATE query_string

in my for loop but it doesn't work.

Anyone?

Regards!

create or replace FUNCTION RO_FUN_TOTAL_SCORE_DATE_PORTAL
(
  v_DATE_FIELD  IN VARCHAR2
) RETURN RO_OBJ_NESTED_TOTAL_SCORE IS
  v_ret               RO_OBJ_NESTED_TOTAL_SCORE;
  query_string       VARCHAR2(1000);
BEGIN
  /*Initialize object type to save data*/
  v_ret := RO_OBJ_NESTED_TOTAL_SCORE();

  query_string := 'SELECT ROUND(AVG(generic_score),2) AS score,
    yrp.comment_date_pk AS comment_date,
    yrp.hot_id_pk AS hot_id,
    yp.portal_id_pk AS portal_id,
    AVG(total_opinions) AS total_opinions
   FROM yrportalreview yrp JOIN yrhotellinks yrh ON yrp.hot_id_pk =
    yrh.hot_id
    AND yrp.portal_id_pk = yrh.portal_id AND yrp.comment_date.pk = ' ||    
    v_DATE_FIELD || 
  ' WHERE yrp.hot_id_pk IN (92, 27)
    AND concept_id_pk = 1
    AND yp.active = 1
   GROUP BY hot_id_pk,
    yp.portal_id_pk,
    yrp.comment_date_pk';

FOR I IN (EXECUTE IMMEDIATE query_string)
  LOOP
    /*insert in table type rows with data*/
    v_ret.extend;
    v_ret(v_ret.count) := RO_OBJ_TOTAL_SCORE_DATE_PORTAL(i.score, 
      i.comment_date, i.hot_id, i.portal_id, i.total_opinions);
  END LOOP;
  RETURN v_ret;
END RO_FUN_TOTAL_SCORE_DATE_PORTAL;

Why would you use dynamic SQL here? Why wouldn't you use static SQL? Why do you want to use a loop rather than doing a `bulk collect` into your collection? If you do want to use a loop, are you sure you don't want to use a pipelined table function? — Justin Cave, Jul 06 '15 at 21:48
I agree, nothing here can't be done with a cursor,no need to use execute immediate — kevinskio, Jul 06 '15 at 22:12

score 0 · Accepted Answer · answered Jul 06 '15 at 22:38

You shouldn't concatenate data into your query. Use bind variables. Read up on SQL Injection, and hard parsing to see two reasons why you should use bind variables.

You might try something like the following (no need for loops at all):

  create or replace FUNCTION RO_FUN_TOTAL_SCORE_DATE_PORTAL
  (
    v_DATE_FIELD  IN VARCHAR2
  ) RETURN RO_OBJ_NESTED_TOTAL_SCORE IS
    v_ret               RO_OBJ_NESTED_TOTAL_SCORE;

  BEGIN

    SELECT RO_OBJ_TOTAL_SCORE_DATE_PORTAL
              (ROUND(AVG(generic_score),2)  -- score,
              ,yrp.comment_date_pk          -- comment_date
              ,yrp.hot_id_pk                -- hot_id
              ,yp.portal_id_pk              -- portal_id
              ,AVG(total_opinions))         -- total_opinions
    BULK COLLECT INTO v_ret
     FROM yrportalreview yrp 
     JOIN yrhotellinks yrh 
           ON yrp.hot_id_pk = yrh.hot_id
           AND yrp.portal_id_pk = yrh.portal_id 
           AND yrp.comment_date.pk = v_DATE_FIELD 
     WHERE yrp.hot_id_pk IN (92, 27)
      AND concept_id_pk = 1
      AND yp.active = 1
     GROUP BY hot_id_pk,
      yp.portal_id_pk,
      yrp.comment_date_pk';

    RETURN v_ret;
  END RO_FUN_TOTAL_SCORE_DATE_PORTAL;

I understand, but my problem is: the field yrp.comment_date_pk is a date and the content of v_DATE_FIELD is a string with the name of the field to compare, not with the date itself. — Light1988, Jul 07 '15 at 08:56
How many distinct possible values can v_DATE_FIELD have? You could use DECODE or CASE to map the v_DATE_FIELD to a column: `DECODE(v_DATE_FIELD,'C1',yrh.c1,'C2',yrh.c2)` — daivrz, Jul 07 '15 at 12:34

score 0 · Answer 2 · answered Jul 07 '15 at 10:43

Something similar which worked for test types and data:

create or replace function f_test(v_date_field in varchar2) return t_test_tab is

  v_ret t_test_tab;
  v_sql varchar2(4000);
begin
  v_sql := 'select t_test_row(id, date1) from test where date1 = '||v_date_field;
  execute immediate v_sql bulk collect into v_ret;
  return v_ret;
end f_test;

Data definitions and test:

create table test (id number, date1 date, date2 date, date3 date);
insert into test values (1, date '2015-01-01', date '2015-01-01', date '2015-01-02');
insert into test values (2, date '2015-01-02', date '2015-01-01', date '2015-01-02');

create type t_test_row is object (id number, date1 date);
create type t_test_tab is table of t_test_row;

select * from table(f_test('DATE2'));

ID  DATE1
--  ----------
 1  2015-01-01

Run a query script inside a for in loop (Oracle)

2 Answers2