[This topic is migrated from our old forums. The original author name has been removed]
Hi Hans, Roger,
I am creating a new monitor and I think I make some obvious error for which I seem to be a bit blind:
select sysdate, host_name, gb_written, gb_written - $$gb_written$$ delta
, $$gb_written$$ previous
from
(
select round(sum(bytes_written)/1024/1024/1024) gb_written
from V$ASM_DISK_IOSTAT
)
, v$instance
AFAIK, this should give a delta column, with as value gb_written minus the previous value of gb_written, referred to as $$gb_written$$. Can you open my eyes?
thanks,
Ronald.
Hi Ronald,
With "monitor" do you mean this is going to run in the DbVisualizer "Monitor" feature? If that is the intention then variables are not supported in Monitor window.
If it is another problem please tell what is going wrong and what you except should happen. As far as I can see the use of variables when executing it in the SQL Commander looks good.
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: monitor - previous values
Roger,
see pages 130, 131 of the manual about the rowcount example.
This used to work in
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Ronald,
What error do you get when you run it?
Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: monitor - previous values
I don't get an error, I only keep getting (NULL) for the delta and previous values.
R.
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.] [Attachment has been removed.]
Re: monitor - previous values
Ronald,
I tried by simplifying your SQL (I use the default variable identifiers ${...}$ but that shouldn't affect the result):
SELECT
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENTS.DEPARTMENT_NAME,
salary,
salary - ${salary}$ delta,
${salary}$ previous
FROM
(
SELECT
salary
FROM
HR.EMPLOYEES
)
,
HR.DEPARTMENTS;
Attached is a screenshot with the result.
Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: monitor - previous values
I see, in the sqlcommander grid it works. It used to work in the montoring grid. Is that functionality removed from the monitoring grid? The example on page 130, 131 suggests that it should work. In that case I obviously made some kind of an error .....
R.
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Ronald,
Does the query return a single row on each execution? That is the basic requirement for re-using data from previous executions. If it does then you should be able to refer the previous round by column name.
Best Regards
Roger
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: monitor - previous values
Roger,
back again ...
see and try it out.
create table computers (z varchar2(1));
sql in sqlcommander
SELECT
'${DbVis-Date}$ ${DbVis-Time}$' AS PollTime,
sysdate,
COUNT(*) AS RowCount,
COUNT(*) - ${RowCount||COUNT(*)}$ AS RowCountChange
FROM
Computers;
result:
16:08:22 [DEBUG pool-1-thread-23 B.?] DefaultEditor-0: T4CStatement.execute("SELECT
'${DbVis-Date}$ ${DbVis-Time}$' AS PollTime,
sysdate,
COUNT(*) AS RowCount,
COUNT(*) - ${RowCount||COUNT(*)}$ AS RowCountChange
FROM
Computers")
16:08:22 [DEBUG pool-1-thread-23 B.?] DefaultEditor-0: EXCEPTION -> java.lang.NullPointerException: null
java.lang.NullPointerException
at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:876)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:825)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.onseven.dbvis.a.B.B.?(Z:1522)
at com.onseven.dbvis.a.B.F$A.call(Z:2838)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
run this loop in sqlcommander
--/
begin
for i in 1..100
loop
insert into computers value ('z');
commit;
dbms_lock.sleep(3);
end loop;
end;
/
and run the same query but now as a monitor:
SELECT
'${DbVis-Date}$ ${DbVis-Time}$' AS PollTime,
sysdate,
COUNT(*) AS RowCount,
COUNT(*) - ${RowCount||COUNT(*)}$ AS RowCountChange
FROM
Computers;
result in the debuglog:
16:09:45 [DEBUG pool-1-thread-19 B.?] RootConnection: T4CStatement.execute("select '${DbVis-Date}$ ${DbVis-Time}$' as PollTime,
count(*) as RowCount,
count(*) - ${RowCount||count(*)}$ as RowCountChange
from Computers")
16:09:45 [DEBUG pool-1-thread-19 B.?] RootConnection: EXCEPTION -> java.lang.NullPointerException: null
java.lang.NullPointerException
at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:876)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:825)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1726)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1696)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.onseven.dbvis.a.B.B.?(Z:1522)
at com.onseven.dbvis.a.B.F$A.call(Z:2838)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:637)
I still think there is a little problem ...
When I comment out the variables the query works, in sqlcommander.
best regards,
Ronald.
Hans Bergsten
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Hi Ronald,
It seems like you do not have "${" and "$}" defined as the variable delimiters in Tool Properties, the SQL Editor/Variables category. You need to either change the settings or the variable references in the SQL statements to match your settings.
Best Regards,
Hans
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: monitor - previous values
Thanks Hans,
yes, you are right, I still used the old defaults $$ and $$ to enclose the variables. I fixed that and this took away the java errors. Still I think it is not working as advertised.
select '${DbVis-Date}$ ${DbVis-Time}$' as PollTime,
count(*) as RowCount,
count(*) - ${RowCount||count(*)}$ as RowCountChange,
${RowCount}$ as previous
from Computers
in a monitor,
this should show the PollTime, current RowCount, The RowCountChange and the previous rowcount. The references to the previous value is not working. It's been a while since I last used it but I am pretty sure that the example on
used to work.
Currently it does not work. Can that be fixed or is it removed on purpose or do I - again - make an error?
regards,
Ronald.
Hans Bergsten
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Hi Ronald,
Yeah, something's not right here. We will look into it. Thanks for reporting it.
Best Regards,
Hans
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Hi Ronald,
The problem has been identified and a fix will be included in the next version.
Best Regards
Roger
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: monitor - previous values
Ronald,
This is now fixed in the 7.0.5 version.
Regards
Roger
anonymous