OracleByAjay

  1 Shell Program to Swap Two Numbers   2 Shell Program to Add Two Numbers 3 Shell Program to Find the Biggest of Two Numbers 4 Shell program to Find the Biggest of Three Numbers 5 Shell Program to Find the Area and Circumference of a Circle 6 Shell Program to Find the whether the given Number is Positive, Negative and Zero 7 Shell Program to Find the whether the given Number is Even or Odd 8 Shell Program to Find the whether the given Year is Leap Year or Not 9 Shell Program to Convert Fahrenheit to Celsius and Celsius to Fahrenheit 10 Shell Program to Find the Quotient and Remainder of two Numbers 11 Shell Program to check whether a number is divisible by 5 12 Shell Program to find the sum and average of four integers 13 Shell Program to compute simple interest and compound interest 14 Shell Program to calculate the sum of digits(without using loop) 15 Shell Program to Print the first “N” Odd   Number 16 Shell Program to Print the first “N”...

  conn / as sysdba drop user u1; create user u1 identified by u1; grant connect, resource, create any view to u1; connect u1/u1; CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,         ENAME VARCHAR2(10),         JOB VARCHAR2(9),         MGR NUMBER(4),         HIREDATE DATE,         SAL NUMBER(7, 2),         COMM NUMBER(7, 2),         DEPTNO NUMBER(2)); INSERT INTO EMP VALUES         (7369, 'SMITH',  'CLERK',     7902,         TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20); INSERT INTO EMP VALUES         (7499, 'ALLEN',  'SALESMAN',  7698,         TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30); INSERT INTO EMP VALUES         (7521, 'WARD',   'SALESMAN',  7698,       ...

 external tables External tables allow Oracle to query data that is stored outside the database in flat files.  The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations.  Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel.  They should not be used for frequently queried tables. --  create two file Countries1.txt and Countries2.txt in the folder   This is to be done on the file system E:\data . conn / as sysdba CREATE OR REPLACE DIRECTORY ext_tab_data AS 'E:\data';  CREATE TABLE countries_ext (       country_code      VARCHAR2(5),       country_name      VARCHAR2(50),       country_language...

  multi-table inserts in oracle     Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source dataset. Until the introduction of this feature, only SQL*Loader had a similar capability. This article provides an overview of multi-table inserts and how they are used. syntax overview There are two types of multi-table insert as follows: INSERT FIRST; and INSERT ALL. Multi-table inserts are an extension to INSERT..SELECT. Syntax is of the following form: INSERT ALL | FIRST    [ WHEN condition THEN ] INTO target [ VALUES ]    [ WHEN condition THEN ] INTO target [ VALUES ]    ...    [ ELSE ] INTO target [ VALUES ] SELECT ... FROM    source_query; We define multiple INTO targets between the INSERT ALL/FIRST and the SELECT. The inserts can be conditional or unconditio...