Learn Database Concepts

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue. For Example:  If you want to query all records of employees for a particular department you will use: SELECT * FROM HR.EMPLOYEES WHERE dept = 10; EXPDP UTILITY CMD : $ expdp HR/XXXXX@ORCL directory=DPUMP dumpfile=TEST_EMP.dmp logfile=TEST_EMP.log include="HR.EMPLOYEES:\" in (where dept =10)\"" NORMAL EXPORT  UTILITY  CMD : $ exp HR/XXXXX@ORCL file=/u01/app/oracle/dpump/TEST_EMP.dmp log=/u01/app/oracle/dpump/TEST_EMP.log tables=HR.EMPLOYEES query='"where dept =10"'

Retrieve data from column as a row. Lets use employee table from Scott as a an example : select * from scott.employee; EMPLOYEE_ID FIRST_NAME    LAST_NAME      DEPT_NO     SALARY ----------- -------------------- -------------------- ---------- ----------           1 Dan                  Morgan                       10     100000           2 Helen                Lofstrom                     20     100000           3 Akiko                Toyota                       20      50000           4 Jackie             ...

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression, whose value is used to select one of several alternatives. Syntax: CASE selector     WHEN 'value1' THEN S1;     WHEN 'value2' THEN S2;     WHEN 'value3' THEN S3;     ...     ELSE Sn;  -- default case END CASE; Sample code: DECLARE   grade varchar2(1) :='&grade'; BEGIN   CASE grade   WHEN 'A' THEN     dbms_output.put_line ('your grade is A as your score is above 70');   WHEN 'B' THEN     dbms_output.put_line ('your grade is B as your score is above 60 and below 70');       WHEN 'C' THEN     dbms_output.put_line ('your grade is C as your score is above 50 and below 60');       WHEN 'D' THEN     dbms_output.put_line ('y...

SQL> create table TEST_PARTITION (c1 number) partition by range (c1)     ( partition p100 values less than (100),       partition p200 values less than (200),       partition p300 values less than (300),    partition pmax values less than (maxvalue)); Table created. SQL> select high_value from dba_tab_partitions where table_name = 'TEST'; HIGH_VALUE -------------------------------------------------------------------------------- 100 200 300 MAXVALUE SQL> alter table test add partition p40 values less than (400); alter table test add partition p400 values less than (400)                                * ERROR at line 1: ORA-14074: partition bound must collate higher than that of the last partition SQL> alter table test split partition pmax at (400) into (partition p400, partition pmax); Table altered. SQL> select high_value from dba_tab_partitio...

Basic loop  Basic loop structure encloses sequence of statements in between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop. Syntax: The syntax of a basic loop in PL/SQL programming language is: LOOP   Sequence of statements; END LOOP; Sample code: DECLARE   i NUMBER :=1;   BEGIN   LOOP     dbms_output.put_line('i value is'||i);     i   := i+1;     IF i > 10 THEN       EXIT;     END IF;   END LOOP;   --end loop;   dbms_output.put_line(' this is end of loop'); END; Sample output : i value is1 i value is2 i value is3 i value is4 i value is5 i value is6 i value is7 i value is8 i value is9 i value is10  this is end of loop WHILE LOOP A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true. Syntax: WHILE condition L...

IF - THEN it is the simplest form of IF control statement, frequently used in decision making and changing the control flow of the program execution. The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing. Syntax: Syntax for IF-THEN statement is: IF condition true THEN {do this}; END IF; sample code declare i number :=&i; j number := &j; k number :=&k; begin if i <=15 then dbms_output.put_line ('value of i is ' ||i); end if; if j >= 20 and j <= 50 then dbms_output.put_line ('value of i is ' ||j); end if; if k >= 50 and j <= 70 then dbms_output.put_line ('value of i is ' ||j); end if; end; IF-THEN-ELSE A sequence of IF-THEN statements can be followed by an optional sequence of ELSE statements, which execute when the condition is FALSE. Syntax for the IF-...

This is sample  code 1: declare   test_message varchar2(30):='Hello World';   message varchar2(20);  begin  message:='This is Arvind';  DBMS_OUTPUT.PUT_LINE(test_message || ' ' || message);  END;  / This is sample  code 2:  declare   num1 number:='10';   num2 number:='3';   num number;   num3 number;  begin   num:= num1+ num2;   DBMS_OUTPUT.PUT_LINE('This is num1: '||num1  || ', This is num2: ' || num2);   DBMS_OUTPUT.PUT_LINE('This is the ouput num1+num2: ' || num);  num3:=num+37;  DBMS_OUTPUT.PUT_LINE('This is the ouput num3: ' || num3);  END;  / Sample code 3: declare A1 number := &A1; B1 number := &B1; C1 number; ERR exception; begin C1 :=A1 + B1; if C1 <= 5 then raise ERR; else DBMS_OUTPUT.PUT_LINE (' Value of C1 is '||C1||'' ); end if; EXCEPTION   WHEN ERR THEN  DBMS_OUTPUT.PUT_LINE (' Value of C1 is less than 5' );  end;