Basic Oracle Procedure

To develop a basic procedure, there is a table with basic Oracle scalar types and corresponding internal platform types:

Oracle basic scalar type	Oracle type pseudonyms	Internal platform type
CHAR(n)	CHARACTER(n)	text
NCHAR(n)	NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n)	text
VARCHAR2(n)	VARCHAR(n) CHARACTER VARYING(n) CHAR VARYING(n)	text
NVARCHAR2(n)	NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n)	text
DATE	-	text
TIMESTAMP [(fractional_seconds)] WITH TIME ZONE	-	text
NUMBER[(p [, s])]	INTEGER INT SMALLINT DECIMAL[(p, s)] NUMERIC[(p,s)]	double
FLOAT[(p)]	REAL DOUBLE PRECISION	double
BLOB	-	bytea

Detailed description of types is available in Oracle documentation.

NOTE. 1. Transformation of Oracle types NUMBER and FLOAT into platform types may significantly reduce precision. This will be defined during testing borderline values.
2. Oracle has the INTEGER type that is a pseudonym for NUMBER(38). Therefore, this is an integer with precision up to 38 decimal places. The "bigint" internal platform type can store 19 decimal places that is why the INTEGER Oracle type must be transformed into the "double" internal type. However, such transformation significantly reduces precision.
3. If working with the NCHAR and NVARCHAR2 types requires specific Unicode languages (for example, Arabic), specify the nls_lang parameter on designing a database: UTF-8. If the nls_lang parameter that is different from UTF-8 is already used (for example, american_america.CL8MSWIN1251), the UNISTR function can be used that transforms the string encoding into Unicode. In this case it is required to send specific language character codes to input. Codes are given in the documentation.

Basic Table Type

Types, tables and procedures are created in the standard database language. A basic table type is declared using the syntax:

TYPE <type name_1> AS OBJECT (
<column name> <column type>
);
TYPE <type name_2> AS TABLE OF <type name_1>;

Example

Output table structure after procedure execution:

id (NUMBER(19)). Identifier.
name (NVARCHAR2(100)). Fruit name.

Create a fruits table:

create or replace type fruits_structFieldsRow as OBJECT (r_id number(19), r_name varchar2(100));
create or replace type fruits_structTable as table of fruits_structFieldsRow;

The procedure that receives the "id" input scalar parameter of the int type extracts a row from the fruits table:

create or replace package demo Is
Function fruits_get(id1 NUMBER) return fruits_structTable;
end demo;