Re: different results in sql and plsql execution

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@(protected)
SYS@(protected)
DECLARE
SYS@(protected);
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@(protected);

Commit complete.

SYS@(protected))
2 FROM Resource_View
3 WHERE Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
4 AND Existsnode(Res, '/Resource[@(protected)
5 AND Depth(1) = 1
6 /

PATH(1)
--------------------------------------------------------------------------------
css
Docs
javascript

SYS@(protected)
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[@(protected)
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[@(protected)
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@(protected)
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@(protected))
FROM Resource_View
2 WHERE Under_Path(Res, '/public/UserFiles/Image/', 1) = 1
3 4 AND Existsnode(Res, '/Resource[@(protected)
5 AND Depth(1) = 1
6 /

PATH(1)
--------------------------------------------------------------------------------
css
Docs
javascript

SYS@(protected)
SYS@(protected)
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[@(protected)
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[@(protected)
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@(protected)>

======================================================================================

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[@(protected)
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[@(protected)
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[@(protected)
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[@(protected)
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[@(protected)
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

Re: different results in sql and plsql execution

cichomitiko gmail

2006-08-01