Re: different results in sql and plsql execution

• From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
• To: <mdemenko@xxxxxxxxx>
• Date: Tue, 1 Aug 2006 13:25:53 +0200

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@ora10gr2> set echo on
SYS@ora10gr2> set serveroutput on
DECLARE
SYS@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@ora10gr2> commit;

Commit complete.

SYS@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@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@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@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@ora10gr2> set serveroutput on
SYS@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@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

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

• Follow-Ups:
  • Re: different results in sql and plsql execution
    • From: Maxim Demenko

• References:
  • different results in sql and plsql execution
    • From: Maxim Demenko

Other related posts:

• » different results in sql and plsql execution
• » Re: different results in sql and plsql execution
• » Re: different results in sql and plsql execution
• » Re: different results in sql and plsql execution

1. Home
2. oracle-l
3. Archive
4. 08-2006

---

• » Previous by Date
• » Next by Date
• » Related Posts
• » View list details
• » Manage your subscription

---