How to make automated task in Oracle

কোন একটা প্রোগ্রাম/কাজ একটা নির্দিষ্ট সময় পর পর স্বয়ংক্রিয় ভাবে চালাতে DBMS_SCHEDULER প্যাকেজ ব্যাবহার করা হয়। Continue reading How to make automated task in Oracle

Enable network service (ACL) and Send mail from APEX

Enabling Network Service: By default, the ability to interact with network services is disabled in Oracle Database 11g. Therefore, you must use the BMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040200 database user:

DECLARE
  ACL_PATH VARCHAR2(4000);
BEGIN
    -- Look for the ACL currently assigned to '*' and give APEX_040200
    -- the "connect" privilege if APEX_040200
    -- does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
    WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH,'APEX_040200','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040200', TRUE, 'connect');
  END IF;
  EXCEPTION
    -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Send Mail: Oracle application express send mail using APEX_MAIL package. example…

APEX_MAIL.SEND(
        p_to        => '[email protected]',
        p_from      => '[email protected]',
        p_subj      => 'APEX_MAIL with attachment',
        p_body      => 'Please review the attachment.',
        p_body_html => '<b>Please</b> review the attachment');
/

Send mail with attachments…

DECLARE
    l_id Number;
    x varchar2(100);
    y BLOB;
    z varchar2(100);
BEGIN
    l_id := APEX_MAIL.SEND(
        p_to        => '[email protected]',
        p_from      => '[email protected]',
        p_subj      => 'APEX_MAIL with attachment',
        p_body      => 'Please review the attachment.',
        p_body_html => '<b>Please</b> review the attachment');

    SELECT FILENAME, IMAGE, MIMETYPE into x, y, z
        FROM CUSTOMER
        WHERE CUSTOMER_ID = 100;

        APEX_MAIL.ADD_ATTACHMENT(
            p_mail_id    => l_id,
            p_attachment => y,   -- BLOB column name
            p_filename   => x,   -- File Name 
            p_mime_type  => z);  -- MIME column name
END;
/

 

Introduction to PL/SQL (Control Structure)

Control Structure: This chapter shows you how to structure the flow of control through a PL/SQL program. There are three types of Control structure available in PL/SQL, These are Conditional constructs with the IF statements, CASE expression and LOOP control structure.

IF Statements: In Oracle, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition evaluates to FALSE. It allows PL/SQL to perform actions selectively based on conditions.

Syntax:

IF condition THEN
  statements;
[ELSIF condition THEN
  statements;]
[ELSE
  statements;]
END IF;

Example:

DECLARE
  age number := 40;
BEGIN
  IF age < 11
     THEN DBMS_OUTPUT.PUT_LINE ('I am a child ');
    ELSIF age < 20 
     THEN DBMS_OUTPUT.PUT_LINE ('I am young ');
    ELSE 
     THEN DBMS_OUTPUT.PUT_LINE ('I always young');
  END IF;
END;

Note: ELSIF and ELSE are optional in an IF statements. You can have any number of ELSIF keyword but only one ELSE keyword in your IF statements. END IF makes the end of an IF statements and must be terminated by a semicolon.

CASE Expression: A CASE expression a results based on one or more alternatives. To return the result, the CASE expression uses a selector.  The selector is followed by one or more WHEN clause that are checked sequentially. If the value of the selector equals the value of WHEN clause expression, that WHEN clause is executed & that results is returned.

Syntax:

CASE selector
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ....
  WHEN expressionN THEN resultN
  [ELSE resultN+1]
END;

Example:

DECLARE
  grade VARCHAR2(1) := UPPER('&Grade');
  appraisal VARCHAR2(20);
BEGIN
  appraisal := 
    CASE
        WHEN grade = 'A' THEN 'Excellent'
        WHEN grade = 'B' THEN 'Good'
        WHEN grade IN ('C', 'D') THEN 'Fair'
        ELSE 'No such relusts'
    END;
  DBMS_OUTPUT.PUT_LINE ('Grade: '|| grade ||' appraisal '||appraisal);
END;

CASE Expression & CASE Statement: A case expression evaluates the condition and returns a value. On the other hand, a CASE statements evaluates the condition and perform an actions. A CASE statements can be complete PL/SQL block. CASE statements end with END CASE; but CASE expression end with END.

Example of CASE Statements:

DECLARE
 did NUMBER;
 dname VARCHAR2(20);
 emp NUMBER;
 mid NUMBER := 108;
BEGIN
  CASE mid
   WHEN 108 THEN
     SELECT department_id, department_name
     INTO did, dname FROM departments
     WHERE manager_id = 108;
   SELECT count(*) INTO emp FROM employees
    WHERE department_id = did;
  WHEN 200 THEN
  .........
  END CASE;
  DBMS_OUTPUT.PUT_LINE ('You are working in the ' || dname ||
  ' department. There are '|| emp || ' employees in this department');
END;

LOOP Statements: Loops are mainly used to execute statements repeatedly until an exit condition are reached. It is mandatory to have an exit condition in a loop, otherwise the loop is infinite. There are three loop types…

  • BASIC Loop
  • FOR Loop
  • WHILE Loop

Basic Loop: A basic loop allows execution of its statements at least once, even if the condition has been met upon entering the loop. Example are…

DECLARE
  star VARCHAR2(20);
BEGIN
  LOOP
  DBMS_OUTPUT.PUT_LINE (star);
    star := star||'*';
  EXIT WHEN LENGTH(star) > 5;
  END LOOP;
END;

WHILE Loop: Use the WHILE loop to repeat statements while a condition is TRUE. Example are…

DECLARE
  star VARCHAR2(20);
BEGIN
  star := '*';
  WHILE LENGTH(star) < 6 LOOP
  DBMS_OUTPUT.PUT_LINE (star);
    star := star||'*';
  END LOOP;
END;

FOR Loop:  Use FOR loop to shortcut the test for the number of iteration. Do not declare the counter, it is declared implicitly by lower_bound ..upper_bound syntax. Example are…

DECLARE
  star VARCHAR2(20);
BEGIN
  FOR i IN 0..5 LOOP
  DBMS_OUTPUT.PUT_LINE (star);
    star := star||'*';
  END LOOP;
END;

continue…..

Introduction to PL/SQL (SQL Statements)

SQL Statements in PL/SQL: PL/SQL supports data manipulation language (DML) and transaction control language (COMMIT, ROLLBACK, SAVEPOINT). You can use DML commands to modify the data in a database table. PL/SQL dose not directly support DDL statements, such as – CREATE TABLE, ALTER TABLE, DROP TABLE. These statements are dynamic SQL statements. Dynamic statements are built as character string at run time and can contain placeholders and parameters. Therefore, you can use dynamic SQL to execute your DDL statements in PL/SQL. Use the EXECUTE IMMEDIATE statement, which takes the SQL statements as an argument to execute your DDL statements.

SELECT statement: Retrieve data from the database with SELECT statement. In SELECT statement INTO clause is required, and query must return only one row.

Syntax:

SELECT select list ....
INTO {variable_name [, variable_name] ...
     | record_name}
FROM table
 [WHERE condition];

Example:

SET SERVEROUTPUT ON
DECLARE
  sum_sal NUMBER(10,2);
  deptno NUMBER NOT NULL := 60;
BEGIN
  SELECT SUM(salary) -- group function
  INTO sum_sal FROM employees
  WHERE department_id = deptno;
 DBMS_OUTPUT.PUT_LINE ('The sum of the salary is '||sum_sal);
END;
/

 If your requirement is to retrieve multiple rows and operate on the data. You can make use of explicit cursor. I discus about later.

Insert Statement:

BEGIN
  INSERT INTO employees
    (employee_id, first_name, last_name, email, salary)
   VALUES (employees_seq.NEXTVAL, 'Ali', 'Asgor', '[email protected]', 4200);
END;
/

Update Statement:

DECLARE
  sal emp.salary%TYPE := 8000;
BEGIN
  UPDATE employees
  SET salary = sal
  WHERE job_id = 'ST_CLERK';
END;
/

Delete Statement:

DECLARE
  dept emp_department_id%TYPE := 10;
BEGIN
  DELETE FROM employees
  WHERE department_id = dept;
END;
/

Merge Statement:

DECLARE
  empno emp.employee_id%TYPE := 100;
BEGIN
  MERGE INTO copy_emp c
    USING emp e
    ON (e.employee_id = c.employee_id)
  WHEN MATCHED THEN
   UPDATE SET
    c.first_name = e.first_name,
    c.last_name  = e.last_name,
    .........
    c.manager_id = e.manager_id
  WHEN NOT MATCHED THEN
   INSERT VALUES (e.employee_id, e.first_name, ...... e.manager_id);
END;
/

SQL Cursor: A cursor is a pointer to the private memory area allocated by the oracle server. There are two type of cursor one is Implicit that’s create and manage internally by the oracle server, another is Explicit cursor that’s explicitly declared by the programer.

SQL Cursor attribute:

Attribute Description
SQL%FOUND Boolean Attribute that evaluates to TRUE if the most resent SQL statement returned at least one row.
SQL%NOTFOUND Boolean Attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.
SQL%ROWCOUNT An integer value that represents the number or rows affected by the most recent SQL statement

Example:

VARIABLE rows_deleted VARCHAR2(30);
DECLARE
  empno emp.employee_id%TYPE := 105;
BEGIN
  DELETE FROM employees
  WHERE employee_id = empno;
 :rows_deleted := (SQL%ROWCOUNT || 'rows deleted');
END;
/
PRINT rows_deleted

SQL functions: Most of the function are valid in PL/SQL expression. But the following functions are not available in procedural statements.

  • DECODE
  • Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV and VARIANCE. Group functions apply to groups of a rows in a table and therefore are available only in SQL statements in a PL/SQL block.

continue…..