dynamic query doesn't return any rows
Leonardo Zacché, July 08, 2003 - 4:43 pm UTC
Tom, can you ever imagine WHY the exact same query returns 10 rows (perfect result) if I run it from SQL*Plus prompt, but if I run it as a (pseudo-)Dynamic query, the fetch_rows returns always 0 (zero)? I mean pseudo-Dynamic because I just put the select statement in a string and run it. It seems to be no mis-written, it parses ok, runs ok, but return zero rows. Can you imagine why?
Thanks!
July 08, 2003 - 6:19 pm UTC
hmm, without the code, no -- I cannot see your issue from here. perhaps you are doing dynamic sql wrong.
it should look like this:
ops$tkyte@ORA920> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 type rc is ref cursor;
3 l_cursor rc;
4 l_ename emp.ename%type;
5 begin
6 open l_cursor for 'select ename from emp';
7 loop
8 fetch l_cursor into l_ename;
9 exit when l_cursor%notfound;
10 dbms_output.put_line( l_ename );
11 end loop;
12 close l_cursor;
13 end;
14 /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
DBMS_SQL vs Native Dynamic SQL?
Kamal Kishore, October 03, 2003 - 3:09 pm UTC
Hi Tom,
At the following link:
</code>
http://www.devx.com/gethelpon/10MinuteSolution/16610 <code>
The author indicates that:
<quote>
According to Oracle, Native Dynamic SQL provides 30 to 60 percent performance improvements over DBMS_SQL.
</quote>
Do you agree with this assertion?
Is the performance difference so significant so as to prompt the programmers to start re-writing already running code (which uses DBMS_SQL) to make use of the native dynamic sql features?
To me, it seems to suggest that DBMS_SQL should be used ONLY and ONLY in such extreme cases where Native Dynamic SQL can not perform what you are trying to acomplish.
What is your viewpoint?
Thanks,
October 03, 2003 - 3:41 pm UTC
for a single statment -- executed once, never to be executed again
yes.
for loading 100,000 rows into a table whose name you didn't know until run time?
no.
If you are going to execute the same statement over and over with different inputs -- dbms_sql. You can keep the statement open and parse it ONCE per session instead of ONCE per execution (as NDS would have you do)
If you are going to execute a statment once in your session, NDS is dandy.
But then again, if you just do it once, well -- 30%-60% of an already pretty small number is not very much so I probably would not rip code apart just for that.
Thank You
Kamal Kishore, October 05, 2003 - 11:07 pm UTC
Thanks Tom.
As always, great insight and to the point response.
Thanks,
DBMS_SQL Problem in executing Dynamic Sql
Shah Nawaz, March 28, 2008 - 5:08 am UTC
Hi TOM as i understand -->TheOracleMaster,
I have a table where table name and column name are stored
e.g.
------------------------------------------------------------------------------
T_name col1 col2 col3 col4 Col5............
-----------------------------------------------------------------------------
LOG_PR_DT PR_CODE Audit_trail_id Qty
LOG_PR_DT_LOC PR_CODE Audit_trail_id Qty loc_id Loc_name
LOG_PR_DT_DIS PR_CODE Audit_trail_id Qty Dis_id Status
.........................
.................................
I am writing a procedure which will read this table and
create a select list and where clause from above table based on
T_name,and corresponding columns then compare the last row
and second last row in the passed table and find out the
changes in above columns....
In following example
vDynamicCursor selects the last row
vDynamicCursor_1 selects the second last row
Then i need to compare them and store the result in Audit_Trail_Dt
Part of procedure-->
CREATE OR REPLACE PROCEDURE DYNAMIC_AUDIT_TRAIL_NEW
(
pTable_Name Varchar2,
pDocument_Code Varchar2,
pAudit_Trail_id Number
) is
declaration
:::::::::::::::::::
:::::::::::::::;;
Begin
Open vDynamicCursor For vQuery_New; --read the latest row
Loop
Fetch vDynamicCursor into pRec;
Open vDynamicCursor_1 For vQuery_Old; --read second last row
Loop
Fetch vDynamicCursor_1 into vRec;
vCompare_List:= 'Begin If pRec.PR_CODE <> vRec.PR_CODE Then Insert into Audit_Trail_Dt Values (pRec.Audit_Trail_Id,pRec.Audit_Trail_Id,'||''''||'LOG_PR_DT.PR_CODE'||''''||',vRec.PR_CODE,pRec.PR_CODE); End If; End;';
oc := dbms_sql.open_cursor;
dbms_sql.parse(oc, vCompare_List, dbms_sql.native);
ec := dbms_sql.execute(oc);
dbms_sql.close_cursor(oc);
Exit When vDynamicCursor_1%NotFound;
End Loop;
Exit When vDynamicCursor%NotFound;
End Loop;
End;
I am getting following error-->>
ORA-06550: line 1, column 119:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "ADMIN1.DYNAMIC_AUDIT_TRAIL_NEW", line 433
ORA-06512: at line 2
I tried the execute immediate but still facing issues.
Please help.
March 31, 2008 - 7:26 am UTC
...
In following example
vDynamicCursor selects the last row
vDynamicCursor_1 selects the second last row
Then i need to compare them and store the result in Audit_Trail_Dt
.....
oh no, you are going to invent the worlds slowest, most inefficient audit trail process ever? Is that your goal?
there is no such thing as "a last row", or "a second to last row"
Instead of posting code that doesn't work, please post a specification of what you really need to do.
and we will almost certainly tell you to abandon dynamic sql all together (I've no idea why you would use dynamic sql when static sql obviously will work) and abandon this generic code totally - you don't need it, you don't want it, you want to do things straight forward and simply.
Audit Trail
Shahnawaz, April 01, 2008 - 4:45 am UTC
Hi TOM (The Oracle Master),
Thanks for your help,
Let me explain my requirement in detail,
Sorry for writing so much, i understand you do not appreciate long updates but no choice,
Please spent some time to let me know if it is possible??
I have a table where table name and column name are stored
e.g.
---------------------------------------------------------------------------------
T_name col1 col2 col3 col4 Col5........
---------------------------------------------------------------------------------
LOG_PR_DT Pr_line_no Unit_Price Qty uom
LOG_PR_DT_LOC Pr_line_no Loc_id Qty Line_value status
LOG_PR_DT_DIS Pr_line_no Loc_id Dis_id Status
.........................
.................................
If table_name is passed to me, i can create a query from above table like parameters passed to me is table_name and pAudit_trail_id
like table_name = 'LOG_PR_DT' and pAudit_trail_id = 17382 (contains more than one line)
vQuery_New-->
Select Distinct a.PR_LINE_NO,a.UNIT_PRICE, a.QTY, a.UOM
From LOG_PR_DT a
Where a.PR_CODE = 'DEV002EPR08000382'
and a.Audit_Trail_Id = 17382
and a.rowid in (Select Max(b.rowid)
from LOG_PR_DT b
Where a.PR_CODE = b.PR_CODE
and a.PR_LINE_NO = b.PR_LINE_NO
and b.Audit_Trail_Id = 17382)
Order By 1
Then i need to search for "second last" audit_trail_id and
compare it with above records
second last Audit_trail_id = 17379
vQuery_Old-->
Select Distinct a.PR_LINE_NO,a.UNIT_PRICE, a.QTY, a.UOM
From LOG_PR_DT a
Where a.PR_CODE = 'DEV002EPR08000382'
and a.Audit_Trail_Id = 17379
and a.rowid in (Select Max(b.rowid)
from LOG_PR_DT b
Where a.PR_CODE = b.PR_CODE
and a.PR_LINE_NO = b.PR_LINE_NO
and b.Audit_Trail_Id = 17379)
Order By 1
Then i need to compare it based on line by line, (currently i am getting a 3*3 cross product as each cursor is of 3 records in this case). if any changes found then store the value in a table in format like table_name>audit_trail_id>Changed_Column>Pr_Line_No old_value new_value
---------------------------------------------------------------------------------
Begin --Dynamic Cursor
vQuery_New := vColumn_List||vWhere_Clause_1||vOrder_By_Clause;
vQuery_Old := vColumn_List||vWhere_Clause_Old||vOrder_By_Clause;
oc_n := dbms_sql.open_cursor; --Open last rows (contains 3 lines i.e Pr_line_no =1 , 2 and 3)
dbms_sql.parse(oc_n, vQuery_New, dbms_sql.native);
dbms_sql.describe_columns(oc_n, n_colCnt, n_descTbl );
oc := dbms_sql.open_cursor; --Open second last rows (contains 3 lines i.e Pr_line_no =1 , 2 and 3)
dbms_sql.parse(oc, vQuery_Old, dbms_sql.native);
/* I want to pass pr_line_no, loc_id from "oc_n" to "oc"
so that i have one to one record, currently i am getting
cross product of 3*3 lines from each cursor*/
dbms_sql.describe_columns(oc, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt
loop
l_idx( l_descTbl(i).col_name ) := i; --Define column
dbms_sql.define_column( oc, i, l_columnValue, 4000 );
l_status := dbms_sql.execute(oc);
ec_n := dbms_sql.execute(oc_n);
if ( dbms_sql.fetch_rows(oc_n) > 0 ) --Open last row cursor
then
loop
exit when ( dbms_sql.fetch_rows(oc) <= 0 ); --open second last row cursor
for j in 1 .. l_colCnt
loop
If i = j Then
dbms_sql.column_value(oc, j, l_columnValue );
for m in 1 .. n_colCnt
loop
If i = m Then
If i = m and m=j and j=n then
dbms_sql.column_value(oc_n, m, n_columnValue );
If l_descTbl(i).col_name = l_descTbl(n).col_name
and l_columnValue <> n_columnValue then
Insert into Audit_Trail_Dt
Values(pAudit_Trail_id,pAudit_Trail_id,pTable_Name||'.'||l_descTbl(n).col_name,l_columnValue,n_columnValue);
End If;--If vRec.Item_Description <> pRec.Item_Description Then
End if; --i = m and m=j then
End If; -- If i = m Then
end loop;-- for m in 1 .. n_colCnt loop
End If; -- If i = j Then
end loop; --for i in 1 .. l_colCnt loop
end loop; --exit when ( dbms_sql.fetch_rows(oc) <= 0 );
End if; --if ( dbms_sql.fetch_rows(oc_n) > 0 )
end loop; --for i in 1 .. l_colCnt
end loop; --for n in 1 .. n_colCnt
dbms_sql.close_cursor(oc);
End; --Dynamic Cursor
--End Dynamic Cursor ===============================================
In above logic there is no performance problem although i am doing excess iterations of loops.
I am facing challenges in
1. passing parameters from cursor "oc" to "oc_n" if i could pass the parameters to inner cursor "oc_n" i can compare the values line by line, as currently it is comparing oc.pr_line_no 1 with oc_n.pr_line_no 1 , oc_n.pr_line_no 2 and oc_n.pr_line_no 3.
Please note that i can not hard code pr_line_no as the procedure will run for other tables also which has some other column but do not have pr_line_no.
2. do not have any idea what order to follow when using procedures of dbms_sql
Please, Please, Please help...
Thanks and regards
Shahnawaz
April 01, 2008 - 9:34 am UTC
I do not like this approach at all - sorry.
this is never going to scale
this is never going to perform
this will be rife with errors
you are free to spend your time writing gobs of code - I'm not. DBMS_SQL is well documented and there are literally hundreds of examples on this site alone - let alone on the internet as a whole.
but I recommend a complete redesign of this audit requirement from the ground up and a static implementation.
Result to compare
Shahnawaz, April 01, 2008 - 4:54 am UTC
Hi TOM,
Following is the data-->>
Select Distinct a.Audit_Trail_Id,a.PR_CODE,a.PR_LINE_NO,a.UNIT_PRICE, a.QTY, a.UOM
From LOG_PR_DT a
Where a.PR_CODE = 'DEV002EPR08000382'
and a.Audit_Trail_Id = 17382
and a.rowid in (Select Max(b.rowid)
from LOG_PR_DT b
Where a.PR_CODE = b.PR_CODE
and a.PR_LINE_NO = b.PR_LINE_NO
and b.Audit_Trail_Id = 17382)
union all
Select Distinct a.Audit_Trail_Id,a.PR_CODE,a.PR_LINE_NO,a.UNIT_PRICE, a.QTY, a.UOM
From LOG_PR_DT a
Where a.PR_CODE = 'DEV002EPR08000382'
and a.Audit_Trail_Id = 17379
and a.rowid in (Select Max(b.rowid)
from LOG_PR_DT b
Where a.PR_CODE = b.PR_CODE
and a.PR_LINE_NO = b.PR_LINE_NO
and b.Audit_Trail_Id = 17379)
Order By 1
Result
AUDIT_TRAIL_ID PR_CODE PR_LINE_NO UNIT_PRICE QTY UOM
17379 DEV002EPR08000382 1 15 2 EA
17379 DEV002EPR08000382 2 2 5 EA
17379 DEV002EPR08000382 3 3 4 PC
17382 DEV002EPR08000382 1 12 2 EA
17382 DEV002EPR08000382 2 2 3 EA
17382 DEV002EPR08000382 3 3 4 EA
Result
Shahnawaz, April 01, 2008 - 5:16 am UTC
Result would be like this
LOG_PR_DT.UNIT_PRICE.Line1 15 >1 2
LOG_PR_DT.UOM.Line3 PC > EA
LOG_PR_DT.QTY.Line2 5 > 3
In short,
Reading table_name and columns from a table or (data_dictionary) and comparing multiple but unique last and second_last records of the column combinations, and storing the column_name, old_value and new_value.
Is it achievable??
DBMS_SQL ROWCOUNT
Shah Nawaz, April 08, 2008 - 6:01 am UTC
Hi TOM,
Thanks for your expert comments.
I will review my strategy but i Need your urgent help on the following(Please note that i can not use execute immediate as i need to iterate with following example)
Please Note i have a query
Output of query:-
Select Distinct a.PR_LINE_NO, a.LOCATION_LINE_NO From LOG_PR_DT_LOCATION a Where a.audit_trail_id = 17382
PR_LINE_NO,LOCATION_LINE_NO
1,1
1,2
2,1
2,2
3,1
3,2
3,3
3,4
8 rows selected.
When I run this query in a dynamic cursor, cursor noi_c, it exit after two rows whereas it should exit after 8 rows.
Can you tel me where i am wrong????
Part Of procedure:--
t_colCnt number := 0;
t_columnValue varchar2(4000);
t_descTbl dbms_sql.desc_tab;
No_Of_It_Query:='Select Distinct a.PR_LINE_NO, a.LOCATION_LINE_NO From LOG_PR_DT_LOCATION a Where a.audit_trail_id = 17382';
noi_c := dbms_sql.open_cursor;
dbms_sql.parse(noi_c, No_Of_It_Query, dbms_sql.native);
dbms_sql.describe_columns(noi_c, t_colCnt, t_descTbl );
for no_of_col in 1 .. t_colCnt
loop
l_idx(t_descTbl(no_of_col).col_name ) := no_of_col;
dbms_sql.define_column( noi_c, no_of_col, t_columnValue, 4000 );
noi_status := dbms_sql.execute_and_fetch(noi_c);
Dbms_Output.Put_Line('noi_status '||noi_status);
dbms_sql.column_value(noi_c,no_of_col, t_columnValue );
Dbms_Output.Put_Line('Row '||no_of_col||' '||t_descTbl(no_of_col).col_name ||' - '||t_columnValue);
end loop; --for no_of_col in 1 .. t_colCnt
Dbms_Output.Put_Line('Exiting Procedure');
dbms_sql.close_cursor(noi_c);
End;
Output of Procedure-->>
noi_status 1
Row 1 PR_LINE_NO - 1
noi_status 1
Row 2 LOCATION_LINE_NO - 1
Exiting Procedure
When I run this query in a dynamic cursor, cursor noi_c, it exit after two rows whereas it should
exit after 8 rows.
Can you tel me where i am wrong????
April 09, 2008 - 2:06 pm UTC
for no_of_col in 1 .. t_colCnt
???? why are you fetching instead of that loop.
search this
you have gone wrong from just about the very beginning.
you
a) open
b) parse
c) define
d) THEN FETCH AND LOOP and fetch until no more data
search this site for dump_csv for an example
Hi Tom
Shah Nawaz, April 15, 2008 - 2:26 am UTC
Hi TOM,
You have been very helpful until now, This is a very good site and i learned a lot, while completing my Dynamic Procedure which i asked you in my earlier post "Audit Trail April 1, 2008 - 4am US/Eastern"
I am happy to learn a lot about DBMS_SQL through this site only.
I manage to write a code which i thought impossible initially. It takes the table name and column names from a table and compare them and store the result in a table. and my approach remains same as i posted initially.
Thanks again for all your help.
Regards
Shahnawaz
Dynamic SQL and dblinks
Shivdeep Modi, June 02, 2008 - 7:57 am UTC
Hi,
I need to setup a dynamic PL/SQL which will connect remotely using database links
and get some data.
I've simplified the dynamic sql for brevity.
NCLDBA AS NORMAL ON ncldb > desc TSREPORT_DBLIST
Name Null? Type
----------------------------------------- -------- ------------
DBNAME NOT NULL VARCHAR2(10)
NCLDBA AS NORMAL ON ncldb >
create or replace procedure timepass as
dummy_sql varchar2(2000);
dynamic_cursor number;
rows_processed number;
begin
dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
dummy_sql:='select user from dual@:dblink';
--Trying to parse once.
DBMS_SQL.PARSE(dynamic_cursor, dummy_sql,dbms_sql.native);
for db in ( select dbname from TSREPORT_DBLIST)
loop
DBMS_SQL.BIND_VARIABLE(dynamic_cursor, ':dblink', 'EB00');
rows_processed := DBMS_SQL.EXECUTE(dynamic_cursor);
end loop;
DBMS_SQL.close_cursor(dynamic_cursor);
end;
/
The procedure gets created but on running I get the error:
*
ERROR at line 1:
ORA-01729: database link name expected
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "NCLDBA.TIMEPASS", line 10
ORA-06512: at line 1
If I formulate the dynamic sql using: select user from dual@db.dbname, I am sure it would work
Using this we will get different sqls for all the values of the database link(:dblink) hence in principle,
the sqls cannot be shared.
I could probably use session_cached_cursors to improve performance.
What is the besy way forward?
Regards,
Shivdeep
June 02, 2008 - 11:35 am UTC
you cannot bind identifiers
you cannot "select * from :bind" which is in effect what you are trying to do.
session cached cursors likely will have no impact on this at all - because I would hope you would not be running this sql more than once in a session anyway - if your application relies on remote data constantly and continuously - you have done it wrong.
so, you'll be using 'select ...from t@' || dblink
Dynamic SQL and dblinks
Shivdeep Modi, June 03, 2008 - 6:32 am UTC
Hi,
You are right, indentifiers cannot be bound.
I am using table@dblink to make it work.
I am doing a scheduled data fetching activity every 1000 hours.
Regards,
Shivdeep
June 03, 2008 - 11:40 am UTC
so, 1000 hours, don't worry about sharing that sql, odds that it would be in the shared pool 1000 hours later are....
near 0% I would think
varying column list
vaibhav, June 10, 2008 - 6:08 am UTC
Hi...
Please help me out here.
GUI has a drop down select from where tthe user selects START_DATE and END_DATE.
I have to count the no. of errors occurred on those days.
EG;
If from_date = 10th June and to_date = 13th June, then i need to have o/p like this:
ERROR_CODE 10th 11th 12th 13th total
err1 5 0 5 0 10
err5 2 2 2 2 6
err3 10 10 10 0 30
This indicates that my SQL query would ahve dynamic columns. I have written the following proc
to get the above output but i think there could be a better way to write it.
PROCEDURE sps_daily_errors(p_start_date IN STRING,
p_end_date IN STRING,
o_cur_error_count OUT SYS_REFCURSOR)
AS
v_sql VARCHAR2(4000);
num_of_days NUMBER;
interval NUMBER(10) := 0;
BEGIN
num_of_days := trunc(to_number(substr((TO_TIMESTAMP(p_end_date, 'DD/MM/YYYY HH24:MI')-TO_TIMESTAMP(p_start_date, 'DD/MM/YYYY HH24:MI')),1,instr(TO_TIMESTAMP(p_end_date, 'DD/MM/YYYY HH24:MI')-TO_TIMESTAMP(p_start_date, 'DD/MM/YYYY HH24:MI'),' '))));
v_sql := ' SELECT me.error_code, '||CHR(10)||
' COUNT (CASE WHEN me.error_timestamp BETWEEN TO_TIMESTAMP('''||p_start_date||''', ''DD/MM/YYYY HH24:MI'') '||
' AND TRUNC(TO_TIMESTAMP('''||p_start_date||''', ''DD/MM/YYYY HH24:MI'') + 1 - (1/24*60*60)) THEN 1 ELSE NULL END) day1, '||CHR(10);
FOR i in 1..(num_of_days-1)
LOOP
v_sql := v_sql || ' COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('''||p_start_date||''', ''DD/MM/YYYY HH24:MI'') + ' || (interval + 1) ||'))'||
' THEN 1 ELSE NULL END) day1, '||CHR(10);
interval := interval + 1;
END LOOP;
v_sql := v_sql || ' COUNT (CASE WHEN TRUNC(me.error_timestamp) BETWEEN TRUNC(TO_TIMESTAMP('''||p_end_date||''', ''DD/MM/YYYY HH24:MI'')) '||
' AND TO_TIMESTAMP('''||p_end_date||''', ''DD/MM/YYYY HH24:MI'') THEN 1 ELSE NULL END) day1, '||CHR(10);
v_sql := SUBSTR(v_sql, 1, LENGTH(v_sql)-2);
v_sql := v_sql ||CHR(10)|| ' SUM (COUNT(1)) OVER (PARTITION BY error_code ORDER BY error_code) total_count '||CHR(10)||
' FROM mhs_errors me'||CHR(10)||
' WHERE AND me.error_timestamp BETWEEN TO_TIMESTAMP('''||p_start_date||''', ''DD/MM/YYYY HH24:MI'') AND TO_TIMESTAMP('''||p_end_date||''', ''DD/MM/YYYY HH24:MI'') '||CHR(10)||
' GROUP BY me.error_code ';
OPEN o_cur_error_count FOR v_sql USING p_service_id;
END sps_daily_errors;
The worst part is i cannot even use bind variables since i dont kow how many times the loop would be executed.
Also, since i dont know the number of columns the query would return, i cannot even test it. all i did was use PUT_LINE
to display what sql string was getting generated.
declare
a sys_refcursor;
x varchar2(100);
y number;
begin
sps_daily_weekly_errors('09/06/2008 20:00', '09/06/2008 21:00', a);
end;
I get the follwoing sql string as the o/p:
SELECT me.error_code,
COUNT (CASE WHEN me.error_timestamp BETWEEN TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') AND TRUNC(TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 1 - (1/24*60*60)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 1)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 2)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 3)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 4)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) = TRUNC((TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') + 5)) THEN 1 ELSE NULL END) day1,
COUNT (CASE WHEN TRUNC(me.error_timestamp) BETWEEN TRUNC(TO_TIMESTAMP('15/06/2008 21:00', 'DD/MM/YYYY HH24:MI')) AND TO_TIMESTAMP('15/06/2008 21:00', 'DD/MM/YYYY HH24:MI') THEN 1 ELSE NULL END) day1,
SUM (COUNT(1)) OVER (PARTITION BY error_code ORDER BY error_code) total_count
FROM mhs_errors me
WHERE me.error_timestamp BETWEEN TO_TIMESTAMP('09/06/2008 20:00', 'DD/MM/YYYY HH24:MI') AND TO_TIMESTAMP('15/06/2008 21:00', 'DD/MM/YYYY HH24:MI')
GROUP BY me.error_code
How do i implement my functionality here?
Is there a way to achieve the above result where in the columns are dynamic?
I searched your site but coudnt find anything
Please help me out.......
June 10, 2008 - 8:29 am UTC
Here are two approaches, the first is bind as bind friendly (each date range would create a new sql) as the second (here there would be only as many sql's as number of days between ranges - that is, every 5 day range - regardless of start/end dates would use the same sql)
ops$tkyte%ORA10GR2> create or replace view error_data
2 as
3 select object_type error_code, created dt
4 from all_objects
5 /
View created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function get_report( p_sdate in date, p_edate in date ) return sys_refcursor
2 as
3 l_sql long := 'select error_code ';
4 l_col long := ', count( decode( trunc(dt), to_date( ''$X'', ''yyyymmdd'' ), 1 ) ) "$X" ';
5 l_cur sys_refcursor;
6 begin
7 for i in 1 .. p_edate - p_sdate + 1
8 loop
9 l_sql := l_sql || replace( l_col, '$X', to_char(p_sdate+i-1,'yyyymmdd') );
10 end loop;
11 l_sql := l_sql ||
12 ' from error_data where dt >= :sdate and dt < :edate+1 ' ||
13 ' group by error_code';
14
15 dbms_output.put_line( l_sql );
16 open l_cur for l_sql using p_sdate, p_edate;
17 return l_cur;
18 end;
19 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec :x := get_report( trunc(sysdate-5), trunc(sysdate) )
select error_code , count( decode( trunc(dt), to_date( '20080605', 'yyyymmdd' ), 1 ) ) "20080605" , count( decode( trunc(dt), to_date( '20080606', 'yyyymmdd' ), 1 ) ) "20080606" , count( decode( trunc(dt), to_date( '20080607', 'yyyymmdd' ), 1 ) ) "20080607" , count( decode( trunc(dt), to_date( '20080608', 'yyyymmdd' ), 1 ) ) "20080608" , count( decode( trunc(dt), to_date( '20080609', 'yyyymmdd' ), 1 ) ) "20080609" , count( decode( trunc(dt), to_date( '20080610', 'yyyymmdd' ), 1 ) ) "20080610" from error_data where dt >= :sdate and dt < :edate+1 group by error_code
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print x
ERROR_CODE 20080605 20080606 20080607 20080608 20080609 20080610
------------------- ---------- ---------- ---------- ---------- ---------- ----------
PACKAGE BODY 0 0 0 0 2 0
PACKAGE 0 0 0 0 2 0
FUNCTION 0 0 0 0 0 1
VIEW 0 0 0 0 0 1
TABLE 0 0 0 0 4 0
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace context my_ctx using get_report
2 /
Context created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function get_report( p_sdate in date, p_edate in date ) return sys_refcursor
2 as
3 l_sql long := 'select error_code, to_char( :sdate1, ''fmDDth Mon "thru "'' ) || to_char(:edate1, ''fmDDth Mon'' ) range ';
4 l_col long := ', count( decode( trunc(dt), to_date( sys_context( ''my_ctx'', ''DAY_$X'' ), ''yyyymmdd'' ), 1 ) ) "Day $X" ';
5 l_cur sys_refcursor;
6 begin
7 for i in 1 .. p_edate - p_sdate + 1
8 loop
9 dbms_session.set_context( 'my_ctx', 'day_' || i, to_char( p_sdate+i-1, 'yyyymmdd' ) );
10 l_sql := l_sql || replace( l_col, '$X', i );
11 end loop;
12 l_sql := l_sql ||
13 ' from error_data where dt >= :sdate2 and dt < :edate2+1 ' ||
14 ' group by error_code';
15
16 dbms_output.put_line( l_sql );
17 open l_cur for l_sql using p_sdate, p_edate, p_sdate, p_edate;
18 return l_cur;
19 end;
20 /
Function created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec :x := get_report( trunc(sysdate-5), trunc(sysdate) )
select error_code, to_char( :sdate1, 'fmDDth Mon "thru "' ) || to_char(:edate1, 'fmDDth Mon' ) range , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_1' ), 'yyyymmdd' ), 1 ) ) "Day 1" , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_2' ), 'yyyymmdd' ), 1 ) ) "Day 2" , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_3' ), 'yyyymmdd' ), 1 ) ) "Day 3" , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_4' ), 'yyyymmdd' ), 1 ) ) "Day 4" , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_5' ), 'yyyymmdd' ), 1 ) ) "Day 5" , count( decode( trunc(dt), to_date( sys_context( 'my_ctx', 'DAY_6' ), 'yyyymmdd' ), 1 ) ) "Day 6" from error_data where dt >= :sdate2 and dt < :edate2+1 group by error_code
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print x
ERROR_CODE RANGE Day 1 Day 2 Day 3 Day 4 Day 5 Day 6
------------------- ---------------------- ---------- ---------- ---------- ---------- ---------- ----------
PACKAGE BODY 5TH Jun thru 10TH Jun 0 0 0 0 2 0
PACKAGE 5TH Jun thru 10TH Jun 0 0 0 0 2 0
FUNCTION 5TH Jun thru 10TH Jun 0 0 0 0 0 1
VIEW 5TH Jun thru 10TH Jun 0 0 0 0 0 1
TABLE 5TH Jun thru 10TH Jun 0 0 0 0 4 0
Simply Mind Blowing
Vaibhav, June 11, 2008 - 12:38 am UTC
Hi Tom
Thanks for that quick reply...
The solution is just too goooooood...
Coudn't ask for anything better...
Simply mindblowing
Thanks a TON!!!
Vaibhav, June 17, 2008 - 2:11 pm UTC
Hi tom...
Vaibhav is back again with a doubt...
In the above procedure, i didnt know the number of days for which i would be needing the error count. But now i have a proc wherein i kno how many columns would be there in the output but this time the problem is how do i generate the column name...
below is the query...
SELECT me.error_code,
COUNT (CASE when me.error_timestamp >= (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 3/24) AND me.error_timestamp < (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 2/24)
THEN 1 ELSE NULL END) "interval1",
COUNT (CASE when me.error_timestamp >= (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 2/24) AND me.error_timestamp < (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 1/24)
THEN 1 ELSE NULL END) "interval2",
COUNT (CASE when me.error_timestamp >= (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 1/24) AND me.error_timestamp < (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI')
THEN 1 ELSE NULL END) "interval3",
SUM (COUNT(1)) OVER (PARTITION BY error_code ORDER BY error_code) total_count
FROM mhs_errors me
WHERE me.error_timestamp >= TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI:SS.FF') - 3/24 AND me.error_timestamp < TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI:SS.FF')
GROUP BY me.error_code
this query gives me the count of errors in the past 3 hours from p_error_date...
the o/p is...
error_code interval1 interval2 interval3 total_count
err1 10 8 2 20
err1 10 10 10 30
err1 10 10 20 40
say the user has passed p_error_date = '2008-06-18 12:30'
the o/p that i want is...
error_code 09:30-10:30 10:30-11:30 11:30-12:30 total_count
err1 10 8 2 20
err1 10 10 10 30
err1 10 10 20 40
My query is a static query...so will i have to write a dynamic one to get the
above column names using your $X$ technique
June 17, 2008 - 3:04 pm UTC
you would need to dynamically generate that, back to square one. column names must be known at parse time, you want unique column names, hence you need to generate the query.
or
p_range1 := TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD
HH24:MI') - 3/24) || '-' || (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD HH24:MI') - 2/24) ;
p_range2 := ...
p_range3 := ...
select 1 order_col, 'error_code', p_range1, p_range2, p_range3, 'total count'
union all
select
SELECT 2 me.error_code,
to_char(COUNT (CASE when me.error_timestamp >= (TO_TIMESTAMP(p_error_date, 'YYYY-MM-DD
HH24:MI') - 3/24) AND me.error_timestamp < (TO_TIMESTAMP(p_error_date,
'YYYY-MM-DD HH24:MI') - 2/24)
THEN 1 ELSE NULL END)) "interval1",
.....
GROUP BY me.error_code
order by 1, 2;
and just have the first row be the "column heading"
Vaibhav, June 18, 2008 - 1:31 am UTC
your second method worked great...
tooooo Smartttttttttttttt
Thanks a ton
Vaibhav, June 19, 2008 - 12:41 pm UTC
Hi Tom,
Continuing the above question,
what if i want the vertical total as well... i mean the following o/p
error_code int1 int2 int3 total
err1 10 8 2 20
err1 10 10 10 30
err1 10 10 20 40
total 30 28 32 70
It is not a requirement as the java guys are handling it on their side...
i just wanted to know if its possible on our database end...
i scratched my head a lot over it...
i know we can use "compute sum..." in sql to generate report but what if i want that as o/p of the cursor itself
is there any analytic function that can help me here...i went through some functions that get me running total but they are not useful in my case i guess
also, i thought i can write individual queries that get me the error count irrespective of the code for all the intervals separately and then again use union all to append it to the o/p as the last row...
like,
select count(1)
from mhs_errors
where error_timestamp between (1st interval)
get the error count for all the intervals this way and then use union all
but it would definitely be a performance issue since i will have to run those queries again...
can you give me some inputs on this...
this is not a requirement as i already mentioned but just the curiosity to know if its possible ?
June 19, 2008 - 1:56 pm UTC
group by rollup does that.
scott%ORA9IR2> select deptno, sum(sal) from emp group by rollup(deptno);
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
vaibhav, June 19, 2008 - 11:29 pm UTC
Bravo !!!