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:

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

Send mail with attachments…

 

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:

Example:

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:

Example:

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:

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…

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

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…

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:

Example:

 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:

Update Statement:

Delete Statement:

Merge Statement:

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:

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…..

Introduction to PL/SQL (Lexical Units & Operator)

Lexical Units: Lexical units include letters, numerals, special characters, tabs, spaces, returns and symbol that building a PL/SQL block. Lexical unit can classified as following…

  • Identifier
  • Delimiters
  • Literals
  • Comments

Identifier: Identifier are the names given to PL/SQL objects.
Quoted Identifier: Quoted identifier makes identifier case sensitive, include character such as space and use reserved words. example-

“begin date” DATE;
“end adte” DATE;

Delimiters: Delimiters are symbols that have special meaning. I know you have already learned about that the semicolon (;) is used to terminate a SQL & PL/SQL statement. Therefore, (;) is the best example of a delimiters. A list of delimiters are following-

Symbol Meaning
+ Addition Operator
Subtraction/Negation Operator
* Multipluication Operator
/ Division Operator
= Equalty Operator
@ Remote access indicator
; Statement Terminator
<> Inequality Operator
!= Inequality Operator
|| Concatention Operator
Single line Comment ondicator
/* Beginning Comments Indicator
*/ Ending Comment Indicator
:= Assignment Operator

Literals: Any value that is assigned to a variable is a literal. Any character, numeral, boolean or date value that is not an identifier is a literal. Literal can classified as …
1. Character Literal : Such as – Asgor, 124c, 11-jan-2014
2. Numeric Literal : Such as – 51478, 45.457
3. Boolean Literal : TRUE, FALSE and NULL is boolean literals.

Comments: It is good programming practice to explain what a piece of a code is trying to achieve. When you include the explanation in a PL/SQL block, the compiler cannot interpret these instruction. Comments are given by the …

– Two hyphen (–) are used to single line comment.
– The beginning and ending comments delimiters (/* and */) are used to comments multiple lines.

continue….

Introduction to PL/SQL (Composite Data Type)

Composite data type: Composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables or you can use composite components wherever you can use composite variables of the same type. There are two types of composite data type…

  • 1. PL/SQL records.
  • 2. PL/SQL collection.
    • INDEX by Table
    • Nested Table
    • VARRAY

1. PL/SQL records: Use PL/SQL records when you want to store values of different data types but only one occurrence at a time. PL/SQL record must contain one or more components (called fields) of any scalar, RECORD or Index by table data type.

Creating PL/SQL records:

Example:

Using %ROWTYPE in composite data type: The %ROWTYPE attribute is useful when the number and data types of the underlying database columns is unknown and  retrieving a row with the SELECT * from statement.

Example: