PL/SQL: Dynamic SQL part-2

In this tutorial, you'll learn what is dynamic SQL and how to implement it..

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).[1] Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.

PL/SQL works analogously to the embedded procedural languages associated with other relational databases. For example, Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which emulates PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language,[2] which conforms to the ISO SQL’s SQL/PSM standard.

The designers of PL/SQL modeled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in several aspects. However, the structure of a PL/SQL package does not resemble the basic Object Pascal program structure as implemented by a Borland Delphi or Free Pascal unit. Programmers can define public and private global data-types, constants and static variables in a PL/SQL package.[3]

PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.

PL/SQL is fundamentally distinct from Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,[4] but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking. There are software tools available that claim to facilitate porting including Oracle Translation Scratch Editor,[5] CEITON MSSQL/Oracle Compiler [6] and SwisSQL.[7]

The StepSqlite product is a PL/SQL compiler for the popular small database SQLite.
PL/SQL Program Unit

A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database.

[kalirajanrajan]

excellent job you doing continue long term and spread your knowledge.

[subhranshu]

This is really helpful.Thanks Radhika.if possible please give tutorials on oracle predefined packages like dbms_sql, dbms_lock, dbms_alert, dbms_lobs.

[Moonlight]

very sweet voice....and good explanation :)

[fashion]

nice video..but please explain the diff uses of using clause as well with execute immediate command in your upcoming videos

[lakshminarayanaswamyyatham]

are there any performance issues using execute immediate?
if there, how to overcome ?

[ieeeramakrishna]

very good explanation.. i have one doubt ddl s are auto committed so that is effect on previous dml or not..

[nareshthandayoudhapany]

Very Good presentation...Is it possible to execute DQL statement in dynamic sql

[zeroandone]

Hi, Is it possible to add a new column in my students table using execute immediate? I have wrote PL/SQL block.

create or replace procedure add_column (xyz in varchar2, abc in varchar2) is
begin
EXECUTE IMMEDIATE 'alter table ' || 'xyz add abc varchar2(20)';
dbms_output.put_line('New column added');
end;


exec add_column ('students, 'time');
when I execute this procedure passing the arguments table name and column name I am getting below error.

Error starting at line : 8 in command -
BEGIN add_column ('students', 'time'); END;
Error report -
ORA-00942: table or view does not exist
ORA-06512: at "HR.ADD_COLUMN", line 4
ORA-06512: at line 1
00942. - "table or view does not exist"
*Cause:
*Action: