Re: different results in sql and plsql execution

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 01 Aug 2006 14:32:42 +0200
Message-ID: <44CF49EA.6050001@google.com>

Radoulov, Dimitre schrieb:
> 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
>
>
>

Nevertheless, thanks a lot for trying.
To avoid any misinterpretation of my problem, here is the output from 10.2.0.2 on RHEL:

cms_at_OWBREP> @m
cms_at_OWBREP> set serveroutput on
cms_at_OWBREP> DECLARE

  2 result boolean;
  3 BEGIN

  4          IF DBMS_XDB.existsResource('/public/UserFiles') THEN
  5                  
DBMS_XDB.deleteResource('/public/UserFiles',DBMS_XDB.DELETE_RECURSIVE_FORCE);
  6          END IF;
  7                  result := DBMS_XDB.createFolder('/public/UserFiles');
  8                  result := 
DBMS_XDB.createFolder('/public/UserFiles/Image');
  9                  result := 
DBMS_XDB.createFolder('/public/UserFiles/Image/css');
 10                  result := 
DBMS_XDB.createFolder('/public/UserFiles/Image/Docs');
 11                  result := 

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

PL/SQL procedure successfully completed.

cms_at_OWBREP> COMMIT
  2 /

Commit complete.

cms_at_OWBREP> 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)

---

Docs
css
javascript

cms_at_OWBREP> 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: 0
<Folders></Folders>

PL/SQL procedure successfully completed.

cms_at_OWBREP>

Best regards

Maxim

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

Received on Tue Aug 01 2006 - 07:32:42 CDT