Databases
StackOverflow2013
Postsdbo
POplsql block to get the dynamic sql query result
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POplsql block to get the dynamic sql query result
    text
    copied!<p>we have created following anonymous block...........</p> <pre><code>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&lt;&gt;0 and rownum&lt;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)||'&lt;&gt; 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; </code></pre> <p>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.</p> <p>Let us know are we on the rite track. Or we need to do something else to get the result out.</p> <p>the result is </p> <pre><code>SQL&gt; / 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&lt;&gt;0 and rownum&lt;25 AND ( A.wk_units2&lt;&gt; B.wk_units1 or A.wk_units3&lt;&gt; B.wk_units2 or A.wk_units4&lt;&gt; B.wk_units3 or A.wk_units5&lt;&gt; B.wk_units4 or A.wk_units6&lt;&gt; B.wk_units5) PL/SQL procedure successfully completed. </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload