plsql block to get the dynmaic sql query result

Question

we have created following anonymous block...........

DECLARE
   sql_str long(32000);
   where_str long(32000);
   counter NUMBER(3):=0;
   BEGIN
   sql_str:='SELECT '||' A.bio_id ,';
   where_str:=' where '||'A.bio_id=B.bio_id AND'||' A.bio_id<>0 and rownum<25 AND (' ;
   LOOP
   counter:=counter+1;
  sql_str:=sql_str||'decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,A.wk_units'||(counter+1)||')';
 sql_str:=sql_str||', decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,B.wk_units'||(counter)||')' ;
  where_str:=where_str||' A.wk_units'||(counter+1)||'<> B.wk_units'||(counter) ;
  EXIT WHEN counter=5;
  sql_str:=sql_str||', ';
  where_str:=where_str||' or ';
  END LOOP;
  sql_str:=sql_str||' from cnt_sls_dm.fct_sales_summary A, cnt_sls_dm.fct_sales_summary B';
  sql_str:=sql_str||where_str||')';
  dbms_output.put_line(sql_str);
  EXECUTE IMMEDIATE(sql_str);
  END;

Result needed: We have written a dynamic sql query. It should get the result set of select query. But we are getting the query itself on running this block. rather than getting any result for this query.

Let us know are we on the rite track. Or we need to do something else to get the result out.

the result is

SQL> /
SELECT  A.bio_id ,decode(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2),
decode(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1), decode(A.wk_units3 -
B.wk_units2,0,NULL,A.wk_units3), decode(A.wk_units3 -
B.wk_units2,0,NULL,B.wk_units2), decode(A.wk_units4 -
B.wk_units3,0,NULL,A.wk_units4), decode(A.wk_units4 -
B.wk_units3,0,NULL,B.wk_units3), decode(A.wk_units5 -
B.wk_units4,0,NULL,A.wk_units5), decode(A.wk_units5 -
B.wk_units4,0,NULL,B.wk_units4), decode(A.wk_units6 -
B.wk_units5,0,NULL,A.wk_units6), decode(A.wk_units6 -
B.wk_units5,0,NULL,B.wk_units5) from cnt_sls_dm.fct_sales_summary A,
cnt_sls_dm.fct_sales_summary B where A.bio_id=B.bio_id AND A.bio_id<>0 and
rownum<25 AND ( A.wk_units2<> B.wk_units1 or  A.wk_units3<> B.wk_units2 or
A.wk_units4<> B.wk_units3 or  A.wk_units5<> B.wk_units4 or  A.wk_units6<>
B.wk_units5)

PL/SQL procedure successfully completed.

Answer 1

This:

dbms_output.put_line(sql_str);

...is what is printing the output, which is correct behavior. The DECLARE portion gives me the impression you are attempting to run an anonymous function, correct?

I've never used EXECUTE IMMEDIATELY - only the following:

FUNCTION MY_FUNCTION()
RETURN SYS_REFCURSOR

L_CURSOR SYS_REFCURSOR;
L_QUERY  VARCHAR2(5000) DEFAULT 'SELECT ...';

BEGIN

  OPEN L_CURSOR FOR L_QUERY;
  RETURN L_CURSOR;

END;

If you want to include bind variables in the dynamic SQL:

FUNCTION MY_FUNCTION()
RETURN SYS_REFCURSOR

L_CURSOR SYS_REFCURSOR;
L_QUERY  VARCHAR2(5000) DEFAULT 'SELECT ...';

BEGIN

  OPEN L_CURSOR FOR L_QUERY
   USING bind_var1;
  RETURN L_CURSOR;

END;

Answer 2

If you were running this as a hardcoded statement in a block it would fail with PLS-00428 because you are not selecting into a variable.

The EXECUTE IMMEDIATE doesn't fail because it runs in the SQL space. But if you want to get the result set you will still have to pass it into a local variable.

SQL> declare
  2      n number;
  3  begin
  4      execute immediate 'select sum(sal) from emp';
  5      dbms_output.put_line('sum of salaries = '||n);
  6  end;
  7  /
sum of salaries =

PL/SQL procedure successfully completed.

SQL> declare
  2      n number;
  3  begin
  4      execute immediate 'select sum(sal) from emp' into n;
  5      dbms_output.put_line('sum of salaries = '||n);
  6  end;
  7  /
sum of salaries = 48525

PL/SQL procedure successfully completed.

SQL>

Which type of variable you choose depends upon what you want to do with the retrieved data, and whether the resultset is a single row or many.

Incidentally is there a particular reason why you are using dynamic SQL for this query?

Answer 3

For me it is unclear what Irveen wants to achieve:

@Irveen, Why not a table with 105 records? Every week you drop one record and you add enother one. You will have never to change your query.

Another solution is to shift your data, so you put the data of column 2 into 1, 3 into 2, 4 into 3, ..., 103 into 102. You will never have to add a column and drop a column.