Oracle select all functions, procedures, packages

To select all functions, procedures, packages 

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE')

Oracle SQL Course

Oracle SQL course all levels 900 L.e

email me on yasser.hassan@yandex.com

best wishes

Oracle Developer Course

Oracle Developer Course from beginner till professional level start from 100$ for level

email me yasser.hassan@yandex.com

Exception Handling

Exception Handling.

General Syntax for coding the exception section

 DECLARE

   Declaration section 

 BEGIN 

   Exception section 

 EXCEPTION 

 WHEN ex_name1 THEN 

    -Error handling statements 

 WHEN ex_name2 THEN 

    -Error handling statements 

 WHEN Others THEN 

   -Error handling statements 

END; 

General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks like this.

 DELCARE

   Declaration section 

 BEGIN

    DECLARE

      Declaration section 

    BEGIN 

      Execution section 

    EXCEPTION 

      Exception section 

    END; 

 EXCEPTION

   Exception section 

 END; 

In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.

3) Types of Exception.

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.

Exception Name	Reason	Error Number
CURSOR_ALREADY_OPEN	When you open a cursor that is already open.	ORA-06511
INVALID_CURSOR	When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.	ORA-01001
NO_DATA_FOUND	When a SELECT...INTO clause does not return any row from a table.	ORA-01403
TOO_MANY_ROWS	When you SELECT or fetch more than one row into a record or variable.	ORA-01422
ZERO_DIVIDE	When you attempt to divide a number by zero.	ORA-01476

For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to handle the exception as given below.

BEGIN 

  Execution section

EXCEPTION 

WHEN NO_DATA_FOUND THEN 

 dbms_output.put_line ('A SELECT...INTO did not return any row.'); 

 END; 

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE 

   exception_name EXCEPTION; 

   PRAGMA 

   EXCEPTION_INIT (exception_name, Err_code); 

BEGIN 

Execution section

EXCEPTION

  WHEN exception_name THEN

     handle the exception

END;

For Example: Lets consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.

 DECLARE 

  Child_rec_exception EXCEPTION; 

  PRAGMA 

   EXCEPTION_INIT (Child_rec_exception, -2292); 

BEGIN 

  Delete FROM product where product_id= 104; 

EXCEPTION 

   WHEN Child_rec_exception 

   THEN Dbms_output.put_line('Child records are present for this product_id.'); 

END; 

/ 

c) User-defined Exceptions

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

For Example: Lets consider the product table and order_items table from sql joins to explain user-defined exception.
Lets create a business rule that if the total no of units of any particular product sold is more than 20, then it is a huge quantity and a special discount should be provided.

DECLARE 

  huge_quantity EXCEPTION; 

  CURSOR product_quantity is 

  SELECT p.product_name as name, sum(o.total_units) as units

  FROM order_tems o, product p

  WHERE o.product_id = p.product_id; 

  quantity order_tems.total_units%type; 

  up_limit CONSTANT order_tems.total_units%type := 20; 

  message VARCHAR2(50); 

BEGIN 

  FOR product_rec in product_quantity LOOP 

    quantity := product_rec.units;

     IF quantity > up_limit THEN 

      message := 'The number of units of product ' || product_rec.name ||  

                 ' is more than 20. Special discounts should be provided. 

   Rest of the records are skipped. '

     RAISE huge_quantity; 

     ELSIF quantity < up_limit THEN 

      v_message:= 'The number of unit is below the discount limit.'; 

     END IF; 

     dbms_output.put_line (message); 

  END LOOP; 

 EXCEPTION 

   WHEN huge_quantity THEN 

     dbms_output.put_line (message); 

 END; 

/ 

RAISE_APPLICATION_ERROR ( )

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message); 

• The Error number must be between -20000 and -20999 
• The Error_message is the message you want to display when the error occurs.

Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.

Using the above example we can display a error message using RAISE_APPLICATION_ERROR.

DECLARE

  huge_quantity EXCEPTION; 

  CURSOR product_quantity is 

  SELECT p.product_name as name, sum(o.total_units) as units

  FROM order_tems o, product p

  WHERE o.product_id = p.product_id; 

  quantity order_tems.total_units%type; 

  up_limit CONSTANT order_tems.total_units%type := 20; 

  message VARCHAR2(50); 

BEGIN 

  FOR product_rec in product_quantity LOOP 

    quantity := product_rec.units;

     IF quantity > up_limit THEN 

        RAISE huge_quantity; 

     ELSIF quantity < up_limit THEN 

      v_message:= 'The number of unit is below the discount limit.'; 

     END IF; 

     Dbms_output.put_line (message); 

  END LOOP; 

 EXCEPTION 

   WHEN huge_quantity THEN 

 raise_application_error(-2100, 'The number of unit is above the discount limit.');

 END; 

/ 

Oracle Object Inheritance

Inheritance for PL/SQL Objects:

PL/SQL allows creating object from existing base objects. To implement inheritance, the base objects should be declared as NOT FINAL. The default is FINAL.
The following programs illustrate inheritance in PL/SQL Objects. Let us create another object named TableTop, which is inheriting from the Rectangle object. Creating the base rectangle object:

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 NOT FINAL member procedure display) NOT FINAL
/

Creating the base type body:

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;

   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
END;
/

Creating the child object tabletop:

CREATE OR REPLACE TYPE tabletop UNDER rectangle
(  
   material varchar2(20);
   OVERRIDING member procedure display
)
/

Creating the type body for the child object tabletop:

CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
   dbms_output.put_line('Length: '|| length);
   dbms_output.put_line('Width: '|| width);
   dbms_output.put_line('Material: '|| material);
END display;
/

Using the tabletop object and its member functions:

DECLARE
   t1 tabletop;
   t2 tabletop;
BEGIN
   t1:= tabletop(20, 10, 'Wood');
   t2 := tabletop(50, 30, 'Steel');
   t1.display;
   t2.display;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel

PL/SQL procedure successfully completed.

Oracle object Oriented programming

Oracle Objects type body

--Creating the type body:

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;

   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;

   MAP MEMBER FUNCTION measure return number IS
   BEGIN
      return (sqrt(length*length + width*width));
   END measure;
END;
/

-- for get the result of comparison

DECLARE
   r1 rectangle;
   r2 rectangle;
   r3 rectangle;
   inc_factor number := 5;
BEGIN
   r1 := rectangle(3, 4);
   r2 := rectangle(5, 7);
   r3 := r1.enlarge(inc_factor);
   r3.display;

   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

Oracle objects Using Map method

Using Map method

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 member procedure display,
 map member function measure return number
);
/

Learn Oracle with Yasser: Oracle Courses

Learn Oracle with Yasser: Oracle Courses: Oracle Developer Courses private or in Group, offline or online start from 300$ email me on Yasser.hassan@yandex.com best wishes ...

Oracle Courses

Oracle Developer Courses

private or in Group, offline or online
start from 300$

email me on
Yasser.hassan@yandex.com


best wishes
Yasser

Oracle Object Member Methods

Member Methods

Member methods are used for manipulating the attributes of the object. You provide the declaration of a member method while declaring the object type. The object body defines the code for the member methods. The object body is created using the CREATE TYPE BODY statement.
Constructors are functions that return a new object as its value. Every object has a system defined constructor method. The name of the constructor is same as the object type. For example:

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'

Oracle Objects

Function return from Object

create or replace function yobjtest(wh in varchar2,ws in varchar2,wc in varchar2)
return varchar2 is

   residence address;
BEGIN
   residence := address(wh, ws,wc, 'Rajasthan','201301');
   dbms_output.put_line('House No: '|| residence.house_no);
   dbms_output.put_line('Street: '|| residence.street);
   dbms_output.put_line('City: '|| residence.city);
   dbms_output.put_line('State: '|| residence.state);
   dbms_output.put_line('Pincode: '|| residence.pincode);
   return residence.house_no||' '||residence.street;
END;

Oracle forms 6i Scanner

to use scanner inside your form, u can go through this link

http://www.orcl-toolbox.com/scanner/scanner.zip


Yasser

Oracle Objects

Creating an plsql Object

CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
 street varchar2(30),
 city varchar2(20),
 state varchar2(10),
 pincode varchar2(10)
);
/

Oracle REGEXP

dear readers here is sample of using the function REGEXP 

select email from <table-name> 
where REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')

/

Yasser 

Oracle Report 6i generated to Excel

Examples from Metalink:

               1.

Use the following given method to print report which can be used to open in MS Excel.

1. The SELECT statement should produce a string of data separated by commas. For example:

SELECT deptno||','||dname||','||l
oc the_string FROM dept;

2. The report must be created as a Character mode report and the output filename must have a .csv extension.
So set the following System Parameters under the Data Model node in the Object Navigator.

System Parameter Name Default Value
---------------------- -------------------

MODE Character
DESTYPE File
DESNAME x.csv
DESFORMAT dflt

( In the preivewer adjust the width of the fields properly by running report into previewer )

3. Now run the report - it will create the output file x.csv.

The file x.csv can be opened in MS Excel. There will be three columns of data.
----------------------------------------------------------------------------------

               2.

In Reports 6 and 6i you can use parameter DELIMITER

Delimited Output

Delimited output enables you to specify a delimiter, a character, or string of characters, to separate the data in your report output.  Using this feature, you can create report output that is easily imported into spreadsheets, such as Microsoft Excel.

You can specify the delimiter in either of the following ways:

1.  On the command line, use the DELIMITER parameter with DESFORMAT.
2.  In the Delimited Output dialog box use, the Delimiter option.

Oracle open Excel and minimize it

DECLARE
AppID PLS_INTEGER;
BEGIN
AppID := DDE.App_Begin('c:\excel\excel.exe emp.xls',
DDE.App_Mode_Minimized);
END;

Oracle Collection Methods

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

S.N.	Method Name & Purpose
1	EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNT Returns the number of elements that a collection currently contains.
3	LIMIT Checks the Maximum Size of a Collection.
4	FIRST Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5	LAST Returns the last (largest) index numbers in a collection that uses integer subscripts.
6	PRIOR(n) Returns the index number that precedes index n in a collection.
7	NEXT(n) Returns the index number that succeeds index n.
8	EXTEND Appends one null element to a collection.
9	EXTEND(n) Appends n null elements to a collection.
10	EXTEND(n,i) Appends n copies of the ith element to a collection.
11	TRIM Removes one element from the end of a collection.
12	TRIM(n) Removes n elements from the end of a collection.
13	DELETE Removes all elements from a collection, setting COUNT to 0.
14	DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15	DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Exceptions

The following table provides the collection exceptions and when they are raised:

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

NLS_LANG

Commonly Used Values for NLS_LANG

The following table lists commonly used NLS_LANG values for various languages:

Language	NLS_LANG Value
Arabic	ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
Brazilian Portuguese	BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1
Bulgarian	BULGARIAN_BULGARIA.CL8MSWIN1251
Canadian French	CANADIAN FRENCH_CANADA.WE8ISO8859P1
Catalan	CATALAN_CATALONIA.WE8ISO8859P1
Croatian	CROATIAN_CROATIA.EE8MSWIN1250
Czech	CZECH_CZECH REPUBLIC.EE8MSWIN1250
Danish	DANISH_DENMARK.WE8ISO8859P1
Dutch	DUTCH_THE NETHERLANDS.WE8ISO8859P1
Egyptian	ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
English	AMERICAN_AMERICA.WE8ISO8859P1
English (United Kingdom)	ENGLISH_UNITED KINGDOM.WE8ISO8859P1
Estonian	ESTONIAN_ESTONIA.BLT8MSWIN1257
Finnish	FINNISH_FINLAND.WE8ISO8859P1
French	FRENCH_FRANCE.WE8ISO8859P1
German	GERMAN_GERMANY.WE8ISO8859P1
Greek	GREEK_GREECE.EL8MSWIN1253
Hebrew	HEBREW_ISRAEL.IW8MSWIN1255
Hungarian	HUNGARIAN_HUNGARY.EE8MSWIN1250
Icelandic	ICELANDIC_ICELAND.WE8ISO8859P1
Indonesian	INDONESIAN_INDONESIA.WE8ISO8859P1
Italian	ITALIAN_ITALY.WE8ISO8859P1
Japanese	JAPANESE_JAPAN.JA16SJIS
Korean	KOREAN_KOREA.KO16KSC5601
Latin American Spanish	LATIN AMERICAN SPANISH_AMERICA.WE8ISO8859P1
Latvian	LATVIAN_LATVIA.BLT8MSWIN1257
Lithuanian	LITHUANIAN_LITHUANIA.BLT8MSWIN1257
Mexican Spanish	MEXICAN SPANISH_MEXICO.WE8ISO8859P1
Norwegian	NORWEGIAN_NORWAY.WE8ISO8859P1
Polish	POLISH_POLAND.EE8MSWIN1250
Portuguese	PORTUGUESE_PORTUGAL.WE8ISO8859P1
Romanian	ROMANIAN_ROMANIA.EE8MSWIN1250
Russian	RUSSIAN_CIS.CL8MSWIN1251
Simplified Chinese	SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Slovak	SLOVAK_SLOVAKIA.EE8MSWIN1250
Spanish	SPANISH_SPAIN.WE8ISO8859P1
Swedish	SWEDISH_SWEDEN.WE8ISO8859P1
Thai	THAI_THAILAND.TH8TISASCII
Traditional Chinese	TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5
Turkish	TURKISH_TURKEY.WE8ISO8859P9
Ukrainian	UKRAINIAN_UKRAINE.CL8MSWIN1251
Vietnamese	VIETNAMESE_VIETNAM.VN8VN3
Others	AMERICAN_AMERICA.US7ASCII

BULK COLLECT

create or replace function test_bulk
return varchar2 is

  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
 
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description
  BULK COLLECT INTO t_id, t_description FROM test1;
return t_description;
END;
/

Oracle procedure send email

CREATE OR REPLACE PROCEDURE SP_SENDMAIL
(
mail_server in varchar2,
sender in varchar2,
recipient in varchar2,
subject in varchar2,
message in varchar2)
is
c utl_smtp.connection;
mesg varchar2 (4000);
crlf varchar2(2) := chr(13) || chr(10);
mlength number(4);
mdone number(4) := 0;
mpos number(3) :=1;
linewidth number(2) := 60;

begin

mlength:=length(message);

c := utl_smtp.open_connection(m
ail_server, 25);

utl_smtp.helo(c, mail_server);

utl_smtp.mail(c, sender);

utl_smtp.rcpt(c,recipient);

mesg := 'Date: ' || to_char(sysdate, 'dd Mon yy hh24:mi:ss');
mesg := mesg || crlf;
mesg := mesg || 'From: ' || sender || '<' ||
sender || '>';
mesg := mesg || crlf;
mesg := mesg || 'To: ' || recipient || '<' ||
recipient || '>';
mesg := mesg || crlf;
mesg := mesg || 'Subject: ' || subject;
mesg := mesg || crlf;
mesg := mesg || '' || crlf;

while mdone < mlength and mpos < mlength
loop

if (mlength - mdone) < linewidth then
mesg := mesg || substr(message, mpos, mlength - mdone) || crlf;

mdone := mdone + (mlength - mdone);
mpos := mlength;
else
mesg := mesg || substr(message, mpos, 60) || crlf;
mpos := mpos + 60;
end if;

end loop;


utl_smtp.data(c, mesg);
utl_smtp.quit(c);

exception
when utl_smtp.transient_error or
utl_smtp.permanent_error then
utl_smtp.quit(c);

raise_application_error(-20000, 'Failed to send mail due to following
' || sqlerrm);
end sp_sendmail;

Oracle pl/sql FORALL , BULK COLLECT

Using the FORALL construct to bulk bind the inserts is reduced to reduce executed time

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
    
  FORALL i IN t_id.first .. t_id.last
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
    
  FORALL i IN t_id.first .. t_id.last
    DELETE test1
    WHERE  id = t_id(i);
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.05

------

Using the BULK COLLECT INTO construct reduces this time to approximately 0.01 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description 
  BULK COLLECT INTO t_id, t_description FROM test1;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01 

Oracle Multidimensional Collections

Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.

DECLARE
  TYPE t_row IS RECORD (
    id  NUMBER,
    description VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
    :result := :result||' '||l_tab(l_tab.last).id||'    -    '||l_tab(l_tab.last).description||chr(10);
  END LOOP;
END; 

MEMBER Condition

The MEMBER condition allows you to test if an element is member of a collection.
 

DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? ');
  IF 3 MEMBER OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;

Oracle collection MULTISET INTERSECT

MULTISET INTERSECT returns the elements that are present in both sets.
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;
 
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;

/
6
7
8
9
10

PL/SQL procedure successfully completed.

Oracle Collection MULTISET Conditions

MULTISET Conditions

Oracle provides MULTISET conditions against collections, including the following.
MULTISET UNION joins the two collections together, doing the equivalent of a UNION ALL between the two sets.


DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION l_tab2;
 
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;

Oracle Collection Methods

Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type.

• EXISTS(n) - Returns TRUE if the specified element exists.
• COUNT - Returns the number of elements in the collection.
• LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
• FIRST - Returns the index of the first element in the collection.
• LAST - Returns the index of the last element in the collection.
• PRIOR(n) - Returns the index of the element prior to the specified element.
• NEXT(n) - Returns the index of the next element after the specified element.
• EXTEND - Appends a single null element to the collection.
• EXTEND(n) - Appends n null elements to the collection.
• EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
• TRIM - Removes a single element from the end of the collection.
• TRIM(n) - Removes n elements from the end of the collection.
• DELETE - Removes all elements from the collection.
• DELETE(n) - Removes element n from the collection.
• DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

Oracle collection error for assign variable of table to another

DECLARE
  TYPE table_type_1 IS TABLE OF NUMBER(10);
  TYPE table_type_2 IS TABLE OF NUMBER(10);
  v_tab_1  table_type_1;
  v_tab_2  table_type_2;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type_1(1, 2);

  -- Assignment causes compilation error.
  v_tab_2 := v_tab_1;
END;
/

  v_tab_2 := v_tab_1;
             *
ERROR at line 11:
ORA-06550: line 11, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored


SQL>

Oracle Collection Assignments and Equality Tests

Assignments and Equality Tests

Assignments can only be made between collections of the same type. Not types of similar structures, or with the same name in different packages, but literally the same type.

The following example shows a successful assignment between two collections of the same type.

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);

  -- Assignment works.
  v_tab_2 := v_tab_1;
END;
/

PL/SQL procedure successfully completed.

Oracle Varray Collections

Varray Collections

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

Oracle Number default precision and scale

NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.



Yasser
 

Oracle Nested Table Collections

Nested Table Collections

Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

Index-By Tables (Associative Arrays)

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

Oracle Browse files

FUNCTION GET_FILE_NAME
  (directory_name   VARCHAR2,
   file_name        VARCHAR2,
   file_filter      VARCHAR2,
   message          VARCHAR2,
   dialog_type      NUMBER, 
   select_file      BOOLEAN;  
Built-in Type  unrestricted function
Returns  VARCHAR2
Enter Query Mode  yes

Parameters

directory_name    Specifies the name of the directory containing the file you want to open.  The default value is NULL.  If directory_name is NULL, subsequent invocations of the dialog may open the last directory visited.
file_name    Specifies the name of the file you want to open.  The default value is NULL.
file_filter    Specifies that only particular files be shown.  The default value is NULL.  File filters take on different forms, and currently are ignored on the motif and character mode platforms.  On Windows, they take the form of “Write Files (*.WRI)|*.WRI|” defaulting to “All Files (*.*)|*.*|” if NULL.  On the Macintosh the attribute currently accepts a string such as “Text.”

message    Specifies the type of file that is being selected.  The default value is NULL.
dialog_type    Specifies the intended dialog to OPEN_FILE or SAVE_FILE.  The default value is OPEN_FILE.
select_file    Specifies whether the user is selecting files or directories.  The default value is TRUE.  If dialog_type is set to SAVE_FILE, select_file is internally set to TRUE.

/* 

** Built-in:  GET_FILE_NAME
** Example:   Can get an image of type TIFF.
*/ 
DECLARE
  filename VARCHAR2(256)
BEGIN
  filename := GET_FILE_NAME(File_Filter=> 'TIFF Files (*.tif)|*.tif|');
  READ_IMAGE_FILE(filename, 'TIFF', 'block5.imagefld);
END;

Oracle open microsoft word

declare
AppID PLS_INTEGER;
begin
AppID := DDE.App_Begin('C:\Program Files\Microsoft Office\Office14\WINWORD.EXE E:\tcp.rtf',DDE.APP_MODE_NORMAL);
end;

highest precedence among AND, NOT and OR

Which operator has the highest precedence among the following −
AND, NOT, OR?

is NOT


Yasser

Oracle CHR(10) replacement for enter

you can use CHR(10) for enter

Oracle Array

Creating a Varray Type

A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.
The basic syntax for creating a VRRAY type at the schema level is:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Where,

• varray_type_name is a valid attribute name,
• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

Maximum size of a varray can be changed using the ALTER TYPE statement.
For example,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/

Type created.

The basic syntax for creating a VRRAY type within a PL/SQL block is:

TYPE varray_type_name IS VARRAY(n) of <element_type>

For example:

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;

Example 1

The following program illustrates using varrays:

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Student: Kavita  Marks: 98
Student: Pritam  Marks: 97
Student: Ayan  Marks: 78
Student: Rishav  Marks: 87
Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

Please note:

In oracle environment, the starting index for varrays is always 1.
You can initialize the varray
 elements using the constructor method of the varray type, which has the
     same name as the varray.
Varrays are one-dimensional arrays.
A varray is automatically NULL
     when it is declared and must be initialized before its elements can be
     referenced. 


Example 2

Elements of a varray could also be a %ROWTYPE of any
database table or %TYPE of any database table field. The following example
illustrates the concept:

We will use the CUSTOMERS table stored in our database as:

Select * from
customers;




DECLARE

   CURSOR c_customers is

   SELECT name FROM customers;

   type c_list is varray (6) of customers.name%type;

   name_list c_list := c_list();

   counter integer :=0;

BEGIN

   FOR n IN c_customers LOOP

      counter := counter + 1;

      name_list.extend;

      name_list(counter)  := n.name;

      dbms_output.put_line('Customer('||counter
         ||'):'||name_list(counter));

   END LOOP;

END;

/

When the above code is executed at SQL prompt, it produces
the following result:

Customer(1): Ramesh 

Customer(2): Khilan 

Customer(3):
kaushik    

Customer(4): Chaitali 

Customer(5): Hardik 

Customer(6): Komal




PL/SQL procedure
successfully completed.