Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select on multivalue xml column

User_GD3XQMar 2 2021 — edited Mar 2 2021

Hi all,
this is my first question on the oracle community forum, so thanks in advance for you help .
i have a database with some xml tables where there is few multivalues columns with different number set of values on each one, some times we could have just one value (which is the minimum) and some times more than one (2, 3 4 ...).
i need to select all multivalues available on this column for each id
for example if the ID XYZ contains 2 values on column 6 the query will return those two values, if its 3 ...
example of query that i am using now to extract values one by one :
select ID, extractValue(xmlrecord,'/row/c10[position()=1]') from MY_TABLE where recid = 'MY_REC_ID;

my data is stored like bellow example inside the xml column
<row id='MY_RECORD_ID'><c3>0</c3><c4>F</c4><c6>MY_DATA1</c6><c6 m='2'>MY_DATA_2</c6><c7></c7><c7 m='2'>OTHER_DATA</c7><c8>D</c8><c8 m='2'>D</c8><c9 m='2'></c9><c10 m='2'></c10><c11>OTHER_DATA3</c11><c11 m='2'></c11></row>

thank you for your help

Comments

L. Fernigrini

Oracle 18c XE is certified on Windows 7 Pro:
Installation Guide (0 Bytes)
image.pngSo the DB is OK, then you need to confirm if APEX 21.1 is certified on 18c XE:
Oracle Application Express - Downloads (0 Bytes)image.pngSo you need to review the logs and maybe post more details, but APEX 21.1 should work on XE 18c

Bill Carlisle

Thank you! This is the error I got.. I have tried many different ways.
C:\oraclexe\apex_21.1_en\apex>sqlplus / as sysdba
SQL> alter session set container=xepdb1;
Session altered.
SQL> @apex_rest_config.sql
PL/SQL procedure successfully completed.
About to run script[apex_rest_config_cdb.sql]
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Performing installation in multitenant container database in the background.
The installation progress is spooled into apex_rest_config_cdb*.log files.
Please wait...
catcon::set_log_file_base_path: ALL catcon-related output will be written to [C:\oraclexe\apex_21.1_en\apex\apex_rest_config_cdb_catcon_3572.lst]
catcon::set_log_file_base_path: catcon: See [C:\oraclexe\apex_21.1_en\apex\apex_rest_config_cdb*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [C:\oraclexe\apex_21.1_en\apex\apex_rest_config_cdb_*.lst] files for spool files, if any
catcon::catconExec_int: 'Enter:
GetConsoleMode failed, LastError=|6| at C:/Oracle/product/18.0.0/dbhomeXE/perl/site/lib/Term/ReadKey.pm line 334.
Installation completed. Log files for each container can be found in:
apex_rest_config_cdb*.log
You can quickly scan for ORA errors or compilation errors by using a utility
like grep:
grep ORA- *.log
grep PLS- *.log
SQL>

jariola

Have you already installed APEX?
Script you run should be run on as post-install task.
You find instructions to install APEX from below link
https://docs.oracle.com/en/database/oracle/application-express/21.1/htmig/downloading-installing-apex.html#GUID-7E432C6D-CECC-4977-B183-3C654380F7BF

Bill Carlisle

1.) cd c:\oraclexe\apex_21.1_en\apex
2.) sqlplus sys@localhost:1521/XEPDB1 as sysdba
3.) SQL> alter session set container=xepdb1;
Session altered.
4.) Should only see PDB not CDB$ROOT

        SQL> select CON_ID, name from v$pdbs;
	  CON_ID	NAME
	----------	--------------------
		 3	XEPDB1

5.) Create Tablespace specific for APEX Install.

         SQL> CREATE TABLESPACE XX_APEX_TS DATAFILE
	 2 'C:\ORACLE\PRODUCT\18.0.0\ORADATA\XE\XEPDB1\APEX_02.dbf'
	 3 SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING
	 4 EXTENT MANAGEMENT LOCAL
	 5 SEGMENT SPACE MANAGEMENT AUTO;
	Tablespace created.

6.) VERIFIED IN CORRECT CON_ID

           SQL> select * from v$tablespace order by 7,1;
	    TS# NAME              INC BIG FLA ENC   CON_ID
	---------- ------------------------------ --- --- --- --- ----------
		 0 SYSTEM             YES NO YES       3
		 1 SYSAUX               YES NO YES       3
		 2 UNDOTBS1         YES NO YES       3
		 3 TEMP                    NO NO YES       3
		 5 USERS                 YES NO YES       3
		 6 XX_APEX_TS     YES NO YES       3
	6 rows selected.

7.) INSTALLED APEX

	SQL> @apexins.sql XX_APEX_TS XX_APEX_TS TEMP /i/
	...set_appun.sql
	PL/SQL procedure successfully completed.
	...set_ufrom_and_upgrade.sql
	PL/SQL procedure successfully completed.
	PL/SQL procedure successfully completed.
	Performing installation in multitenant container database in the background.
	The installation progress is spooled into apexins_cdb*.log files.
	Please wait...
	......
	. Application Express (APEX) Installation.
	...set_appun.sql
	... Checking prerequisites (MANUAL)
	.
	. SYSDBA Privilege
	.  pass - Connection with SYSDBA privilege.
	. Database rolling upgrade
	.  pass - No rolling upgrade.
	. DB components
	.  pass - CATPROC: version=18.0.0.0.0
	.  pass - XDB: version=18.0.0.0.0
	. XDB
	.  pass - is valid
	. PL/SQL Web Toolkit
	.  pass - version 11.2.0.0.1
	. Tablespaces
	.  pass - found XX_APEX_TS
	.  pass - found XX_APEX_TS
	.  pass - found TEMP
	. PHASES (1,2,3)...
	PL/SQL procedure successfully completed.
	... Prerequisite checks passed.
	....
	....(more)
	....
	# Actions in Phase 3:
	#
	  ok 1 - BEGIN                            |  0.00
	  ok 2 - Computing Pub Syn Dependents                 |  0.00
	  ok 3 - Upgrade Hot Metadata and Switch Schemas           |  0.00
	  ok 4 - Removing Jobs                        |  0.00
	  ok 5 - Creating Public Synonyms                   |  0.03
	  ok 6 - Granting Public Synonyms                   |  0.08
	  ok 7 - Granting to FLOWS_FILES                   |  0.00
	  ok 8 - Creating FLOWS_FILES grants and synonyms           |  0.00
	  ok 9 - Creating Jobs                        |  0.00
	  ok 10 - Creating Dev Jobs                      |  0.00
	  ok 11 - Installing FLOWS_FILES Objects               |  0.02
	  ok 12 - Installing APEX$SESSION Context               |  0.00
	  ok 13 - Recompiling APEX_210100                   |  0.02
	  ok 14 - Installing APEX REST Config                 |  0.00
	  ok 15 - Set Loaded/Upgraded in Registry               |  1.03
	  ok 16 - Removing Unused SYS Objects                 |  0.00
	  ok 17 - Validating Installation                   |  0.07
	ok 3 - 17 actions passed, 0 actions failed               |  1.25
	Thank you for installing Oracle Application Express 21.1.0
	Oracle Application Express is installed in the APEX_210100 schema.
	The structure of the link to the Application Express administration services is
	as follows:
	http://host:port/ords/apex_admin
	The structure of the link to the Application Express development interface is as
	 follows:
	http://host:port/ords
	timing for: Phase 3 (Switch)
	Elapsed: 00:01:15.13
	timing for: Complete Installation
	Elapsed: 00:08:32.48
	PL/SQL procedure successfully completed.

8.) VERIFY INSTALL     

	COL comp_name FOR A26
	COL VERSION FOR A13
	COL STATUS FOR A7
	SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

	COMP_NAME                      VERSION         STATUS
	--------------------------     -------------   ----------
	Oracle Application Express     21.1.0          VALID

	SQL> SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
	STATUS
	--------------------------------------------
	VALID
Bill Carlisle

In 6.2.1 Installing Application Express
It says:
When Application Express installs, it creates the following database accounts:
APEX_210100 - This account owns the Application Express schema and metadata.
FLOWS_FILES - This account owns the Application Express uploaded files.
APEX_PUBLIC_USER - This minimally privileged account is used for Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.
If you configured RESTful Web services, then these additional accounts will be created:
APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
If you are upgrading from a previous release, then FLOWS_FILES already exists and APEX_PUBLIC_USER is created if it does not already exist.
@@@ then in 6.2.5 Configuring RESTful Services

@apex_rest_config.sql

When Prompted, enter a password for the APEX_LISTENER and APEX_REST_PUBLIC_USER accounts.
When configuring RESTful Services in Oracle Application Express, it creates two new database accounts.
APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
APEX_REST_PUBLIC_USER - The account used when calling RESTful Services definitions stored in Oracle Application Express.

@@ In My APEX Install it says:
ok 14 - Installing APEX REST Config                 |  0.00
@@ Should have installed APEX_LISTENER and APEX_REST_PUBLIC_USER users ??
Checked users:

SQL> col USERNAME for a30
SQL>  set pagesize 200
SQL>  select USERNAME, USER_ID from all_users;

USERNAME             USER_ID
------------------------------ ----------
SYS                   0
AUDSYS                 8
SYSTEM                 9
SYSBACKUP           2147483617
SYSDG             2147483618
SYSKM             2147483619
SYSRAC             2147483620
OUTLN                 13
XS$NULL            2147483638
GSMADMIN_INTERNAL           21
GSMUSER                22
DIP                  23
REMOTE_SCHEDULER_AGENT         34
DBSFWUSER               35
ORACLE_OCM               39
SYS$UMF                46
DBSNMP                 53
APPQOSSYS               54
GSMCATUSER               58
GGSYS                 59
XDB                  61
ANONYMOUS               62
WMSYS                 71
DVSYS               1279990
OJVMSYS                80
CTXSYS                 82
ORDSYS                 84
ORDDATA                85
ORDPLUGINS               86
SI_INFORMTN_SCHEMA           87
MDSYS                 88
OLAPSYS                91
MDDATA                 94
LBACSYS                96
DVF                  98
APEX_PUBLIC_USER           105
PDBADMIN               101
HR                  102
APEX_210100              103
FLOWS_FILES              104

40 rows selected.

Bill Carlisle

I looked at all the logs and saw nothing besides the one I already posted.
I saw 1-2 other posting of the same exact error but no resolution.
In tracing it into the Perl code I saw there were many bugs regarding this in the comments of the code.
Can I escalate to the Oracle team?? How about it guys!
Thank you,
Bill Carlisle

Bill Carlisle

bump

1 - 7

Post Details

Added on Mar 2 2021
0 comments
456 views