Incosistency / problems in Oracle 7 Rdbms

From: Syed Mansoor Ahmed <sunedu_at_khi.compol.com>
Date: 1997/10/26
Message-ID: <34540611.2BE6@khi.compol.com>

Dear Sir.

        Would you like to explain the following problems.

USING:

	ORACLE RDBMS	  7.1.6.2.0	
        PL/SQL		  2.1.6.2.0	
        UNDER AIX Version	4.0

I have created a Function named it GET_TIMER Script is as follows:

CREATE OR REPLACE FUNCTION GET_TIMER RETURN NUMBER IS  TM NUMBER(10) :=0;
BEGIN

	SELECT HSEC INTO TM
	FROM SYS.V_TIMER;
	RETURN TM;

END; No.1:

Later I used this function in a select statement ordinarily i.e

        SELECT GET_TIMER FROM SCOTT.EMP;         I used EMP table in order to execute function for a number of times.

        It worked as I expected. But when I tried with the following statement:         

SQL>       SELECT HSECS,GET_TIMER	
           FROM SYS.V_TIMER,SCOTT.EMP	
           WHERE HSECS = GET_TIMER
-------------------------------------- R E S U L T S
------------------------------

SQL> / no rows selected

SQL> / no rows selected

SQL> / no rows selected

SQL> /
ENAME HSECS GET_TIME
---------- --------- ---------

SMITH	     1691906   1691907
ALLEN	     1691906   1691907
WARD	     1691906   1691908
JONES	     1691906   1691908
MARTIN	     1691906   1691909
BLAKE	     1691906   1691909
CLARK	     1691906   1691909
SCOTT	     1691906   1691910
KING	     1691906   1691910
TURNER	     1691906   1691910
ADAMS	     1691906   1691911
JAMES	     1691906   1691911
FORD	     1691906   1691911
MILLER	     1691906   1691912

16 rows selected.

SQL>/ ENAME HSECS GET_TIME
---------- --------- ---------

SMITH	     1692109   1692110
ALLEN	     1692109   1692110
WARD	     1692109   1692111
JONES	     1692109   1692111
MARTIN	     1692109   1692111
BLAKE	     1692109   1692112
CLARK	     1692109   1692112
SCOTT	     1692109   1692112
KING	     1692109   1692113
TURNER	     1692109   1692113
ADAMS	     1692109   1692113
JAMES	     1692109   1692115
FORD	     1692109   1692115
MILLER	     1692109   1692115

16 rows selected.         

The results were conflicting, i.e Projection is violating restriction. I have executed
the above statement number of times each and every time it retrived the same.
On the other hand the same statement retrived another confusing result,i.e
somtimes on the execution it retrived all 14 conflictig rows and somtimes no-rows-selected,
nothing in between. I wonder why somtimes it retrives all 14 rows and somtimes
no-rows-selected? Would you please justify the results.

 Why Projection is conflicting with Restriction?.

 No.2:

  I tried another statement:

   SELECT GET_TIMER, COUNT(*)
   FROM SCOTT.EMP
   GROUP BY GET_TIMER;

• R E S U L T S

  ---

  GET_TIME COUNT(*)
  --------- ----------
  1695773 2 1695773 3 1695775 3 1695775 2 1695775 3 1695777 1 1695778 2

7 rows selected.

SQL>/  GET_TIME COUNT(*)
--------- ----------

  1695878	   1
  1695878 	   2
  1695878          3
  1695879          2
  1695879          3
  1695880          3
  1695880	   1
  1695881	   1

8 rows selected.

SQL>/  GET_TIME COUNT(*)
--------- ----------

  1695973	   3
  1695973 	   4
  1695974          3
  1695974          3
  1695974          3

SQL>/  GET_TIME COUNT(*)
--------- ----------

  1696408	   3
  1696409 	   2
  1696409          4
  1696409          3
  1696410          3
  1696410          1

6 rows selected.    

 Then the results are again surprising. The result are violating The GROUP BY rule
  i.e.The column you group its value must not be repeated.

No. 3

  I tried another statement:       

 select ename,get_time
 from scott.emp order by 1 desc

-------------------------------------- R E S U L T S
------------------------------

SQL>/ ENAME GET_TIME
---------- ----------

WARD	      1299633
TURNER	      1299635
SMITH	      1299632
SCOTT	      1299634
MILLER	      1299637
MARTIN	      1299634
KING	      1299635
JONES	      1299633
JAMES	      1299636
FORD	      1299636
CLARK	      1299634
BLAKE	      1299634
ALLEN	      1299633
ADAMS	      1299635

17 rows selected.

SQL>/ ENAME GET_TIME
---------- ----------

WARD	      1299729
TURNER	      1299732
SMITH	      1299728
SCOTT	      1299731
MILLER	      1299733
MARTIN	      1299730
KING	      1299731
JONES	      1299730
JAMES	      1299732
FORD	      1299733
CLARK	      1299731
BLAKE	      1299730
ALLEN	      1299729
ADAMS	      1299732

17 rows selected.

The results are again conflicting i.e unable to maintain required order.why?
Would you like to Clearify and Justify the results.

No.4:  

  Next statement I am tried for the test is:           

SQL> select distinct get_timer
 1 from scott.emp

-------------------------------------- R E S U L T S
------------------------------

SQL>/   GET_TIME

---

   1273084
   1273085
   1273086
   1273087
   1273088
   1273089

6 rows selected.

SQL>/   GET_TIME

---

   1273260
   1273261
   1273262
   1273263
   1273264
   1273265

6 rows selected.

SQL>/   GET_TIME

---

   1273523
   1273524
   1273525
   1273526
   1273527
   1273530
   1273531

7 rows selected.

The results are perfectly all right.

  Then I tried this statement:

     SELECT DISTINCT GET_TIMER	
      FROM SCOTT.EMP ORDER BY	
      GET_TIMER DESC;

The results are again correct.
Why? would you like to explain above said problems breifly and clearly. Received on Sun Oct 26 1997 - 00:00:00 CDT