Using DUAL table in Oracle

DUAL is a special table found in some of the databases e.g Oracle, Informix and DB2 etc. MySQL, Microsoft SQL (MS SQL) does not have this table moreover MySQL allows DUAL to be specified as a table in queries that do not need data from any tables.In oracle, this DUAL table is having one row and one column by default. The owner of DUAL is sys, can be accessed by all user. The table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.This table was created by Charles Weiss of Oracle corporation to provide a table for joining in internal views. We mostly uses this to find the calculations or checking system variables etc. Examples 1. The following query displays the string value from the DUAL table SELECT ‘Test String’ FROM DUAL; Output: Test String ———– Test String 2. The following query displays the numeric value… Read more“Using DUAL table in Oracle”

How to copy one schema into another using SQL

Some times we don’t have the DBA access and need to copy data from one schema to another. You can perform that by executing migration SQL script in Oracle XE. Step 1. Generate migration script. SELECT table_name, (‘Select ”’||TABLE_NAME||”’ as tbl from dual;’ ||CHR(10)||’delete from <TARGET_SCHEMA>.’||TABLE_NAME||’;’||CHR(10)|| ‘insert into <TARGET_SCHEMA>.’||TABLE_NAME|| ‘ select * from <SOURCE_SCHEMA>.’||TABLE_NAME||’@<XE_CONNECTION>;’) as GENERATED_SQL FROM all_tables where Owner='<SOURCE_SCHEMA>’ Order By TABLE_NAME; Step 2. Run Below script by updating Source and Target connection details WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK connect system/password@xe — Where system is Oracle XE root user ID drop PUBLIC DATABASE LINK <XE_CONNECTION>; CREATE PUBLIC DATABASE LINK <XE_CONNECTION> CONNECT TO <SOURCE_USER_NAME> IDENTIFIED BY “PASSWORD” USING <SOURCE_CONNECTION_NAME>; set sqlprompt ”; — ———– Echo ON / autocommit OFF for the logged in session set autocommit off; set echo off; connect <TARGET_SCHEMA>/password@<XE_CONNECTION> set echo on; — Add SQL generated from Step 1 — Step 3. Execute the script. and commit… Read more“How to copy one schema into another using SQL”