Export table data with filtered clause - Query based export (oracle expdp)

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 - Using LISTAGG function - Oracle

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               Stough                       20      40000
          5 Richard              Foote                        20      70000
          6 Joe                  Johnson                      20      30000
          7 Clark                Urling                       20      90000
          9 Richard              Foote                        20      70001
          8 Clark                Urling                       20      90001


Now I want to get the list of all users in a particular department. I want the output to be printed something like this :

I can achieve this by using the simple LISTAGG function provided by Oracle to get this results.In this below example I got ll the users ID,FNAME,LNAME in every dept.

DEPT_NO   EMP_DETAILS
 
        10   1:Dan-Morgan                                                                        
        20   2:Helen-Lofstrom,3:Akiko-Toyota,4:Jackie-Stough,5:Richard-Foote,6:Joe-                                                      Johnson,7:Clark-Urling,8:Clark-Urling,9:Richard-Foote
       


Code :

select DEPT_NO,LISTAGG(EMPLOYEE_ID ||':' ||FIRST_NAME||'-'||last_name,',')  WITHIN GROUP (order by EMPLOYEE_ID,first_name) "EMP DETAILS" from scott.employee group by dept_no;

usage - we can call listagg function and need to pass the columns that you want to retrieve data from and transform them into rows.

Listagg (column1||'-'||column2) make sure you pass a common column like the deptno thru which you want to group the data.

plsql - CASE STATEMENT

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 ('your grade is D as your score is above 40 and below 50');
  END CASE;
END;

ORA-14074: partition bound must collate higher than that of the last partition

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_partitions where table_name = 'TEST_PARTITION';

HIGH_VALUE
--------------------------------------------------------------------------------
100
200
300
MAXVALUE
400

Learn Plsql - Loops

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 LOOP

   sequence_of_statements

END LOOP;

Sample code

DECLARE

  i NUMBER :=1;

BEGIN

  while i < 10 LOOP

    dbms_output.put_line('i value is'||i);

    i   := i+1;

  END LOOP;

  --end loop;

  dbms_output.put_line(' this is end of loop');

END;

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

 FOR LOOP

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times.

Syntax:

FOR counter IN initial_value .. final_value LOOP

   sequence_of_statements;

END LOOP;

DECLARE

   i number(2);

BEGIN

   FOR i in 10 .. 15 LOOP

       dbms_output.put_line('value of i: ' || i);

  END LOOP;

END;

/

plsql - IF-THEN, IF-THEN-ELSE,IF-THEN-ELSIF STATEMENT

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-THEN-ELSE statement is:

IF condition THEN
   COND1;
ELSE
   COND2;
END IF;
Where, COND1 and COND2 are different sequence of statements. In the IF-THEN-ELSE statements, when the test condition is TRUE, the statement COND1 is executed and COND2 is skipped; when the test condition is FALSE, then COND1 is bypassed and statement COND2 is executed. For example:

IF color = red THEN
  dbms_output.put_line('You have chosen a red car')
ELSE
  dbms_output.put_line('Please choose a color for your car');
END IF;


Sample code

declare
i number :=&i;
j number := &j;
k number :=&k;
begin

if i <=15 then
dbms_output.put_line ('input value of i is with in limit of less than 15 :' ||i);
else
dbms_output.put_line ('input value of i is NOT with in limit of less than 15 :' ||i);
end if;
if j >= 20 and j <= 50 then
dbms_output.put_line ('input value of j is with in limit of (20 and 50) : ' ||j);
else
dbms_output.put_line ('input value of j is NOT with in limit of (20 and 50) :' ||j);
end if;
if k >= 50 and k <= 70 then
dbms_output.put_line ('input value of k is with in limit of (50 and 70) : ' ||k);
else
dbms_output.put_line ('input value of k is NOT with in limit of (50 and 70) : ' ||k);
end if;
end;



IF-THEN-ELSIF


The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you add additional conditions.

When using IF-THEN-ELSIF statements there are few points to keep in mind.

sample code 

declare
i number :=&i;

begin

if i <=20 then
dbms_output.put_line ('value of i is less than 21');
elsif i between 21 and  50 then
dbms_output.put_line ('value of i is bt 20 - 50');
elsif i between 51 and  70 then
dbms_output.put_line ('value of i is bt 50 - 70');
else
dbms_output.put_line ('value of i is greater than 71');
end if;
end;

Sample plsql code- for practice

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;