Tagged: pl sql

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

PL/SQL Oracle / SQL Server

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

Oracle Database

Oracle PL/SQL: implicit vs explicit cursors

PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an “implicit” cursor because you do not explicitly declare a cursor for the SQL statement. If you use an implicit cursor, Oracle performs the open, fetches, and close for you automatically; these actions are outside of your programmatic control. You can, however, obtain information about the most recently executed SQL statement by examining the values in the implicit SQL cursor attributes, as explained later in this chapter. Implicit cursor is simpler...

PL/SQL Oracle

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

PL/SQL Oracle

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

PL/SQL Oracle

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

PL/SQL Oracle

Create triggers on system events

System events are particular database states that can be used to fire a system trigger. Is possibile to create triggers for these events at DATABASE or SCHEMA level. When a triggering event occurs, the database will open an autonomous transaction scope, fire the trigger and commit any transaction imbedded in the trigger. The available system events are: AFTER STARTUP        Causes the DataBase to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA BEFORE SHUTDOWN      Causes the DataBase to fire the trigger whenever an instance of the...