Good example/document for Correlated subquery


You can check the following page for Correlated Subqueries

Especially update query

https://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fsqlp%2Frbafyexsub4.htm

basic syntax

UPDATE TABLE_NAME X
SET FIELD=(SELECT Y.ANYFIELD
FROM ANOTHERTABLE Y
WHERE X.ID= Y.FID)
WHERE X.ID IN (SELECT FID
FROM ANOTHERTABLE)

UPDATE EMPLOYEE X
SET NEWSALARY=( SELECT Y.NEWSALARY
FROM SALARIES Y
WHERE X.EMPID = Y.EMPID)
WHERE X.EMPID IN ( SELECT EMPID
FROM SALARIES)

How to get an Oracle table as a XML Document using XMLType


Here is the table creation script;

CREATE TABLE EMPLOYEE
(
ID               NUMBER,
EMPLOYEENAME     VARCHAR2(20 BYTE),
EMPLOYEESURNAME  VARCHAR2(20 BYTE),
GENDER           VARCHAR2(20 BYTE),
SALARY           VARCHAR2(5 BYTE),
EMPLOYEEID       VARCHAR2(20 BYTE)
)

And lets enter some data inside it;

INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 1, 'tuna' , 'tore' ,
'M',  '1000', 1)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 2, 'linus' , 'torvalds' ,
'M',  '1000', 2)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 3, 'bill' , 'gates' ,
'M',  '1000', 3)

By using XMLType Oracle build-in function we can convert the whole table and get the data inside as a XML document in the following example;

SELECT
XMLTYPE
(
CURSOR
(
SELECT * FROM EMPLOYEE
)
)
FROM
DUAL;

Here is the result;

<?xml version="1.0"?>
<ROWSET>
<ROW>
<ID>1</ID>
<EMPLOYEENAME>tuna</EMPLOYEENAME>
<EMPLOYEESURNAME>tore</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>1</EMPLOYEEID>
</ROW>
<ROW>
<ID>2</ID>
<EMPLOYEENAME>linus</EMPLOYEENAME>
<EMPLOYEESURNAME>torvalds</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>2</EMPLOYEEID>
</ROW>
<ROW>
<ID>3</ID>
<EMPLOYEENAME>bill</EMPLOYEENAME>
<EMPLOYEESURNAME>gates</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>3</EMPLOYEEID>
</ROW>
</ROWSET>

How to configure Oracle Express Edition 10g’s port number, password and start options during boot on Linux OS


If you want to change the port number for Oracle Express Edition DB on Linux OS apply
the following command

[root@localhost ~]# sudo /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
————————————————-
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press <Enter> to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8080

Specify a port that will be used for the database listener [1521]:1521

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:N

Starting Oracle Net Listener…Done
Configuring Database…Done
Starting Oracle Database 10g Express Edition Instance…Done
Installation Completed Successfully.
To access the Database Home Page go to “http://127.0.0.1:8080/apex&#8221;

How to test a column’s value numeric or not in Oracle table (ISNUMERIC equivalent for Oracle DB)


Use the following example SQL example for testing a column’s value is numeric or not using Regular Expression in Oracle

WITH TEST_TABLE
AS
(
SELECT * FROM (
SELECT ‘aa9’ AS TESTCOLUMN –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘abc’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘bcd12’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘001’ –> NUMERIC IS TRUE
FROM DUAL UNION
SELECT ‘123’ –> NUMBERIC IS TRUE
FROM DUAL UNION
SELECT ‘999’ –> NUMERIC IS TRUE
FROM DUAL
)
TABLEDUAL
ORDER BY TESTCOLUMN
)
SELECT  TEST_TABLE.TESTCOLUMN,
CASE WHEN regexp_like(TEST_TABLE.TESTCOLUMN, ‘^-?[[:digit:],.]*$’THEN
   ‘NUMERIC IS TRUE’
ELSE
‘NUMERIC IS FALSE’
END AS NUMERIC_OR_NOT
FROM TEST_TABLE;

Output for the SQL;

TESTCOLUMN NUMERIC_OR_NOT
aa9 NUMERIC IS FALSE
abc NUMERIC IS FALSE
bcd12 NUMERIC IS FALSE
001 NUMERIC IS TRUE
123 NUMERIC IS TRUE
999 NUMERIC IS TRUE