Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: different results in sql and plsql execution

Re: different results in sql and plsql execution

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Tue, 1 Aug 2006 13:25:53 +0200
Message-ID: <027101c6b55d$4140a760$1a03310a@ETNOTEAM6XUQ9V> 





Cannot reproduce it on Solaris 8,


Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit and 
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit.




SYS_at_ora10gr2> set echo on


SYS_at_ora10gr2> set serveroutput on


DECLARE



SYS_at_ora10gr2>   2  result boolean;


  3  BEGIN



  4          IF DBMS_XDB.existsResource('/public/UserFiles') THEN
  5


  6 


DBMS_XDB.deleteResource('/public/UserFiles',DBMS_XDB.DELETE_RECURSIVE_FORCE);

  7          END IF;
  8                  result := DBMS_XDB.createFolder('/public/UserFiles');
  9                  result := 
DBMS_XDB.createFolder('/public/UserFiles/Image');
 10                  result :=
 11  DBMS_XDB.createFolder('/public/UserFiles/Image/css');
 12                  result :=
 13  DBMS_XDB.createFolder('/public/UserFiles/Image/Docs');
 14                  result :=




 15  DBMS_XDB.createFolder('/public/UserFiles/Image/javascript');
 16  END;



 17  /




PL/SQL procedure successfully completed.



SYS_at_ora10gr2> commit;



Commit complete.



SYS_at_ora10gr2> SELECT path(1)


  2  FROM   Resource_View


  3  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  4  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
  5  AND    Depth(1) = 1


  6  /



PATH(1)





css


Docs


javascript



SYS_at_ora10gr2> DECLARE


  2    l_Child_Xml    Xmltype;


  3    l_Count        NUMBER;


  4  BEGIN



  5    SELECT Xmlelement("Folders",

  6                      Xmlagg(Xmlelement("Folder",
  7                                        Xmlattributes(Path(1) AS 




"name"))))



  8    INTO   l_Child_Xml


  9    FROM   Resource_View


 10    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 11    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 12    AND    Depth(1) = 1;


 13    SELECT COUNT(*)



 14    INTO   l_Count


 15    FROM   Resource_View


 16    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 17    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 18    AND    Depth(1) = 1;


 19    Dbms_Output.Put_Line('Count: ' || l_Count);
 20    Dbms_Output.Put_Line(l_child_xml.getStringVal);
 21  END;



 22  /


Count: 3


<Folders><Folder name="Docs"></Folder><Folder name="css"></Folder><Folder
name="javascript"></Folder></Folders>



PL/SQL procedure successfully completed.



SYS_at_ora10gr2> startup force


ORACLE instance started.



Total System Global Area  629145600 bytes

Fixed Size                  2031040 bytes
Variable Size             301990464 bytes
Database Buffers          318767104 bytes
Redo Buffers                6356992 bytes


Database mounted.


Database opened.


SYS_at_ora10gr2> SELECT path(1)


FROM   Resource_View


  2  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  3    4  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
  5  AND    Depth(1) = 1


  6  /



PATH(1)





css


Docs


javascript



SYS_at_ora10gr2> set serveroutput on


SYS_at_ora10gr2> DECLARE


  2    l_Child_Xml    Xmltype;


  3    l_Count        NUMBER;


  4  BEGIN



  5    SELECT Xmlelement("Folders",

  6                      Xmlagg(Xmlelement("Folder",
  7                                        Xmlattributes(Path(1) AS 




"name"))))



  8    INTO   l_Child_Xml


  9    FROM   Resource_View


 10    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 11    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 12    AND    Depth(1) = 1;


 13    SELECT COUNT(*)



 14    INTO   l_Count


 15    FROM   Resource_View


 16    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 17    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 18    AND    Depth(1) = 1;


 19    Dbms_Output.Put_Line('Count: ' || l_Count);
 20    Dbms_Output.Put_Line(l_child_xml.getStringVal);
 21  END;



 22  /


Count: 3


<Folders><Folder name="Docs"></Folder><Folder name="css"></Folder><Folder
name="javascript"></Folder></Folders>



PL/SQL procedure successfully completed.



SYS_at_ora10gr2>







SQL> select * from v$version;



BANNER





Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production


CORE    9.2.0.7.0       Production


TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production



SQL> set echo on


set serveroutput on


DECLARE



result boolean;


BEGIN


                result := DBMS_XDB.createFolder('/public/UserFiles');
                result := DBMS_XDB.createFolder('/public/UserFiles/Image');
                result :=
SQL> DBMS_XDB.createFolder('/public/UserFiles/Image/css');
                result :=
DBMS_XDB.createFolder('/public/UserFiles/Image/Docs');
                result :=




DBMS_XDB.createFolder('/public/UserFiles/Image/javascript');
END;



/SQL>   2    3    4    5    6    7    8    9   10   11   12   13


PL/SQL procedure successfully completed.



SQL> commit;



Commit complete.



SQL> SELECT path(1)


  2  FROM   Resource_View


  3  WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
  4  AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
  5  AND    Depth(1) = 1


  6  /



PATH(1)





css


Docs


javascript



SQL> DECLARE



  2    l_Child_Xml    Xmltype;


  3    l_Count        NUMBER;


  4  BEGIN



  5    SELECT Xmlelement("Folders",

  6                      Xmlagg(Xmlelement("Folder",
  7                                        Xmlattributes(Path(1) AS 




"name"))))



  8    INTO   l_Child_Xml


  9    FROM   Resource_View


 10    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 11    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 12    AND    Depth(1) = 1;


 13    SELECT COUNT(*)



 14    INTO   l_Count


 15    FROM   Resource_View


 16    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 17    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 18    AND    Depth(1) = 1;


 19    Dbms_Output.Put_Line('Count: ' || l_Count);
 20    Dbms_Output.Put_Line(l_child_xml.getStringVal);
 21  END;



 22  /



Count: 3


<Folders><Folder name="Docs"></Folder><Folder name="css"></Folder><Folder
name="javascript"></Folder></Folders>



PL/SQL procedure successfully completed.



SQL> SQL> startup force


ORACLE instance started.



Total System Global Area  706185456 bytes

Fixed Size                   731376 bytes
Variable Size             436207616 bytes
Database Buffers          268435456 bytes
Redo Buffers                 811008 bytes


Database mounted.


Database opened.


SQL> set serveroutput on


SQL> DECLARE



  2    l_Child_Xml    Xmltype;


  3    l_Count        NUMBER;


  4  BEGIN



  5    SELECT Xmlelement("Folders",

  6                      Xmlagg(Xmlelement("Folder",
  7                                        Xmlattributes(Path(1) AS 




"name"))))



  8    INTO   l_Child_Xml


  9    FROM   Resource_View


 10    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 11    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 12    AND    Depth(1) = 1;


 13    SELECT COUNT(*)



 14    INTO   l_Count


 15    FROM   Resource_View


 16    WHERE  Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
 17    AND    Existsnode(Res, '/Resource[@Container="true"]') = 1
 18    AND    Depth(1) = 1;


 19    Dbms_Output.Put_Line('Count: ' || l_Count);
 20    Dbms_Output.Put_Line(l_child_xml.getStringVal);
 21  END;



 22  /


Count: 3


<Folders><Folder name="Docs"></Folder><Folder name="css"></Folder><Folder
name="javascript"></Folder></Folders>



PL/SQL procedure successfully completed.




Regards


Dimitre



--
http://www.freelists.org/webpage/oracle-l


Received on Tue Aug 01 2006 - 06:25:53 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US