PL/SQL Oracle

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a Read More

Binary Search

One of the most interesting algorithms of research is binary search algorithm. This is the fastest algorithm (algorithm of binary trees apart) to search a data inside a group of elements. In fact it runs in a worst log2(x) comparisions before to find (or not) the data. The only prerequisite is : the set of elements where to search the data must be sorted. Below an example of this algorithm. int binary_search( int arr[], int tot_el, int data ) { /* Declaration and initialization of variables */ int el = 0; int me = 0; int count = 0; /* Read More

Record data type

In Pl Sql exists a lot of data types : varchar2, number, float, double, boolean, etc, but it may happen that you want to use a structure that contains several types of data, we call it Record type. It’s a simply a new data type. Record type is formed by a group of one or more columns with own name and data type. To create Record type have to use the keyword TYPE so to instruct the compiler about this new data type. There are two ways to create it , the first is at “database level” which can be Read More

Goal Search Algorithm

Description: Goal search algorithm or search algorithm of the limit of a monotonically increasing sequence bounded above. 1) Purpose: Research of limit of a monotonically increasing sequence bounded above. 2) Example: Suppose to want to create a 10 meters high tower with a tolerance of 10 millimeters, having a given number of bricks, each of different height, stacked one over another. To make the tower we will be obligated to use bricks in Last In First Out order. Suppose to have this stack of bricks named XdataTable: 908 906 832 454 464 522 45 229 5057 433 930 214 1 Read More

Hide Pl/Sql source code

To hide PlSql source code you can use both: dynamic obfuscation the wrap utility You might have to wrap some of yours PlSql objects just to provide the security of your code. Once wrapped, your code can’t be displayed from the static data dictionary views [ *_SOURCE ]. You can wrap the following types of PlSql objects Package specification Package body Type specification Type body Function Procedure However there are some utilities that you can use to read wrapped code, so it is not considered an high-security method to protect your code. Some limitations are wrapped files are not downward Read More

Execution flow of an sql statements

  The execution flow can be divided into four stage. Not all Sql Statements will use all the stages: Parse:  at the beginning the statement must be parsed, this mean check the syntax and associate it with the cursor. It also verifies if the privilege are correct and if the objects referenced really exists Bind:         this stage is execute only when the statement contain input data to be supplied at runtime. For each placeholder, values must be supplied to complete the SQL statement Execute:    at this point the server execute the statement, This is the Read More