Oracle Dynamic Sql

Dynamic SQL Moiz Lokhandwala Business Learning Center Business Learning Center

Satyam Confidential

1

Objectives  SQL Statement Processing  Binding  Problem With Static SQL  Native Dynamic SQL..The Real Hero  Cursor Overview  Oracle Supplied Package:- DBMS_SQL  The Unknown Business Learning Center

Satyam Confidential

2

Static SQL Statement Processing C o m p i l e T i m e

Business Learning Center

Satyam Confidential

3

Dynamic SQL Statement Processing

Business Learning Center

Satyam Confidential

4

Binding In PL\SQL • Oracle needs values for any variables listed in the statement for example:select empno,ename from emp where id=emp_id;

Oracle needs a value for emp_id. The process of obtaining these values is called binding variables.

• Compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time.

Business Learning Center

Satyam Confidential

5

Binding In PL\SQL • Static Binding  Binding at compile time, called static or early binding.  Increases efficiency

• Dynamic Binding  Binding at run time, called dynamic or late binding.  Increases flexibility

6

Business Learning Center

Satyam Confidential

Some Limitations For Example. CREATE PROCEDURE create_table AS BEGIN CREATE TABLE dept (deptno NUMBER(2), ...);

-- illegal Table is undefined until the procedure is executed at

run time.

… END; CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN DROP TABLE table_name; -- illegal Table name is unknown until the procedure is executed. ... END;

Business Learning Center

Satyam Confidential

7

Limitation • Static SQL

Can’t Use in PL\SQL Directly •

Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE.

•

Session control language (SCL) statements, such as ALTER SESSION and SET ROLE. Business Learning Center

Satyam Confidential

8

Overcoming Limitation • Dynamic SQL

Business Learning Center

Satyam Confidential

9

Static SQL • •

Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation.

•

Provides Benefits  SQL statements reference valid database objects.  Necessary privileges are in place to access the database objects.  Performance of static SQL is generally better than dynamic SQL.

•

Problem With Static SQL  Full text of the SQL statements may not known.  Program may accept user input.  Program may need to complete some processing work to determine the correct course of action

Business Learning Center

Satyam Confidential

10

One More Example • Table name is Unknown

Business Learning Center

Satyam Confidential

11

Techniques • EXECUTE IMMEDIATE • DYNAMIC CURSORS • DBMS_SQL

Business Learning Center

Satyam Confidential

12

Execute Immediate The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. EXECUTE IMMEDIATE dynamic_string [INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...] [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...]; Dynamic_string String expression that represents a SQL statement or PL/SQL block. Define_variable Stores a selected column value. Record is a user-defined or %ROWTYPE record that stores a selected row. Input bind_argument An expression whose value is passed to the dynamic SQL statement or PL/SQL block. Output bind_argument Variable that stores a value returned by the dynamic SQL statement or PL/SQL block.

Business Learning Center

Satyam Confidential

13

• Into:declare v_emp_sal emp.emp_sal%type; v_emp_name emp.emp_name%type; v_emp_id emp.emp_id%type; begin Select emp_name,sal into v_emp_name,v_sal from emp where id=v_emp_id; end;

• Record:begin create record v_emo_rec (id varchar2(10),name varchar2(20)); --catching data into record. select emp_id_emp_name into v_emp_rec from emp where id=10000; end; •

Returning …..into :declare v_emp_name emp.emp_name%type; begin update emp set sal=sal*0.1 where emp_id=1000 returning emp_name into v_emp_name; end;

Business Learning Center

Satyam Confidential

14

Understand through Example. DECLARE sql_stmt emp_id salary emp_rec

VARCHAR2(200); NUMBER(4) := 7566; NUMBER(7,2); emp%ROWTYPE;

begin sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; End;

• • • •

You can place all bind arguments in the USING clause by default mode is IN. The INTO clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. At run time, bind arguments replace corresponding placeholders in the dynamic string. So, every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause

Business Learning Center

Satyam Confidential

15

•

The Problem

Function returns column value for a given column name from specified table. ,which passed at runtime function F_Get_Emp_Col_Value(table_name,colmn_name,cond_col,cond_value) return varchar2 is v_out_tx varchar2(4000); v_sql_tx varchar2(32000); Begin v_sql_tx:=' select '||colmn_name|| ' from '||table_name|| ' where '||cond_col||'='||cond_value; -- Use

concatenation method

EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx; return v_out_tx; end;

•

Imagine running the example several times: f_get_Column_tx('emp','eName','empNo',7896) f_get_Column_tx('emp','eName','empNo',4564) f_get_Column_tx('emp','eName','empNo',4546)

•

Result: o SGA is occupied with exactly the same statements. select eName from emp where empNo=7896 select eName from emp where empNo=4564 select eName from emp where empNo=4546

o CPU time is spent re-parsing exactly the same statement. Business Learning Center

Satyam Confidential

16

The Solution • Use bind variables o Bind variables CAN only be used to supply values to be passed to SQL code. o Bind variables are substituted with real values AFTER parsing function F_Get_Emp_Col_Value(table_name,colmn_name,cond_col,cond_value) return varchar2 is v_out_tx varchar2(4000); v_sql_tx varchar2(32000); Begin

:bind1;

v_sql_tx:=' select '||colmn_name|| ' from '||table_name|| ' where '||cond_col||'='||

--Binding involves the use of placeholders and the USING clause EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx using cond_value; return v_out_tx; end;

•

You cannot bind in the names of schema elements (tables, columns, and so on) or chunks of the SQL statement (such as the WHERE clause). For those parts of your string, you must use concatenation.

Business Learning Center

Satyam Confidential

17

Things To Know • Dynamic SQL does not support any PL/SQL data types. o Define variables and bind arguments cannot be Booleans or index-by tables. Exception: You can use RECORD as an output of a dynamic query(in the INTO clause).

• Dynamic SQL does support all Built in and user-defined SQL data types. o Define variables and bind arguments can be collections, LOBs, instances of an object type, and refs.

Business Learning Center

Satyam Confidential

18

Dynamic SQL: The Real Hero •

Makes it possible to build and process complete SQL and PL/SQL statements as strings at runtime.

•

For example, a reporting application in a data warehouse environment might not know the exact table name until runtime. These tables might be named according to the starting month and year of the quarter, for example INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.

•

Within PL/SQL, you can execute any kind of SQL or PL/SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches.

•

Similar to DBMS_SQL, but easier to use.

•

Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise.

Business Learning Center

Satyam Confidential

19

Traditional Way •

An application that takes an event number and dispatches to a handler for the event. The name of the handler is in the form EVENT_HANDLER_event_num, where event_num is the number of the event. CREATE OR REPLACE PROCEDURE event_handler_1(param number) AS BEGIN -- process event RETURN; END; CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN -- process event RETURN; END; CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN -- process event RETURN; END; CREATE OR REPLACE PROCEDURE event_dispatcher (event number, param number) IS BEGIN IF (event = 1) THEN EVENT_HANDLER_1(param); ELSIF (event = 2) THEN EVENT_HANDLER_2(param); ELSIF (event = 3) THEN EVENT_HANDLER_3(param); END IF; END;

Business Learning Center

Satyam Confidential

20

Using Execute Immediate • Using native dynamic SQL, you can write a smaller, more flexible event dispatcher.

CREATE OR REPLACE PROCEDURE event_dispatcher (event NUMBER, param NUMBER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN EVENT_HANDLER_' || to_char(event) || '(:1) ; END; ' USING param; END;

Business Learning Center

Satyam Confidential

21

Scenario The database in this scenario is a company's human resources database (named hr) with the following data model: •

A master table Offices contains the list of all company locations.

Offices

Location USA INDIA KUWAIT

•

Multiple emp_location tables contain the employee information, where location is the name of city where the office is located. For example, a table named emp_houston contains employee information for the company's Houston office, while a table named emp_boston contains employee information for the company's Boston office.

EMPNO

ENAME

JOB

SAL

1

Sameer

20000

2

Kailash

SalesMan Executiv

DEPTN O 12

25000

10

e

Business Learning Center

Satyam Confidential

22

DDL Operation Using Native Dynamic SQL • •

The EXECUTE IMMEDIATE statement can perform DDL operations. Truly dynamic SQL occurs when you literally construct the SQL statement from runtime variable values. For example, the following procedure adds an office location:

CREATE OR REPLACE PROCEDURE add_location (loc VARCHAR2) IS BEGIN -- insert new location in master table INSERT INTO offices VALUES (loc); -- create an employee information table EXECUTE IMMEDIATE 'CREATE TABLE ' || 'emp_' || loc || '( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) )'; END; Business Learning Center

Satyam Confidential

23

Single-Row Query Using Native Dynamic SQL •

The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. You can specify bind variables in the USING clause and fetch the resulting row into the target specified in the INTO clause of the statement.

•

The following function retrieves the number of employees at a particular location performing a specified job:

CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2, job VARCHAR2) RETURN NUMBER IS query_str VARCHAR2(1000); num_of_employees NUMBER; BEGIN query_str := 'SELECT COUNT(*) FROM ' || ' emp_' || loc || ‘ WHERE job = :job_title '; EXECUTE IMMEDIATE query_str INTO num_of_employees USING job; RETURN num_of_employees; END;

Business Learning Center

Satyam Confidential

24

Dynamic Cursor Explicit Cursor:-

Table

Cursor C is Select id, name,job from emp where sal>1000

100 King AD_PRES

Active Set

101 Kochhar AD_VP 102 De Haan AD_VP . .

Cursor

.

139 Seo ST_CLERK 140 Patel ST_CLERK

No Yes

DECLARE •

Create a named SQL area

OPEN •

Business Learning Center

Identify the active set

FETCH • Load the current row into variables

Satyam Confidential

CLOSE

EMPY? •

Return to FETCH if rows are found

•

Test for existing rows

•

Release the active set

25

Dynamic Cursor REF Cursor:•

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. It’s a cursor variable.

•

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself.

•

In PL/SQL, a pointer has data type REF, where REF is short for REFERENCE Therefore, a cursor variable has data type REF CURSOR..

•

Cursor always refers to the same query work area, a Cursor variable can refer to different work areas.

•

A cursor is static, A cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. Define a REF CURSOR type: TYPE REF_TYPE IS REF CURSOR [return return type]

Business Learning Center

Satyam Confidential

26

Multiple-Row Query Using Native Dynamic SQL Dynamic Cursor •

The OPEN-FOR, FETCH, and CLOSE statements can perform dynamic multiple-row queries. For example, the following procedure lists all of the employees with a particular job at a specified location. CREATE OR REPLACE PROCEDURE list_employees(loc VARCHAR2, job VARCHAR2) IS TYPE cur_typ IS REF CURSOR; C cur_typ; query_str VARCHAR2(1000); emp_name VARCHAR2(20); emp_num NUMBER; BEGIN query_str := 'SELECT ename, empno FROM emp_' || loc || ' WHERE job = :job_title'; -- find employees who perform the specified job OPEN C FOR query_str USING job; LOOP FETCH C INTO emp_name, emp_num; EXIT WHEN C%NOTFOUND; -- process row here END LOOP; CLOSE C; END;

Business Learning Center

Satyam Confidential

27

Things to know •

The code can be passed as a variable/string.

•

The variable/string cannot exceed 32K.

•

PL/SQL - semicolon at the end; don’t return anything execute immediate 'begin p_test; end;';

•

SQL – no semicolon at the end; must return the result into the variable of corresponding type (if anything is returned): execute immediate 'select 1 from dual‘ into a;

Business Learning Center

Satyam Confidential

28

DBMS_SQL •

Predecessor of native dynamic SQL

•

The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically.

•

Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations.

•

The DBMS_SQL package has procedures to open a cursor, parse a cursor, supply binds, and so on.

Business Learning Center

Satyam Confidential

29

DBMS_SQL:- Execution Flow OPEN_CURSOR PARSE

BIND_VARIABLE BIND_ARRAY

DEFINE_COLUMN DEFINE_ARRAY EXECUTE_AND _FETCH COLUMN_VALUE VARIABLE_VALUE

CLOSE_CURSOR Business Learning Center

Satyam Confidential

30

Some Methods Syntax •

OPEN_CURSOR , CLOSE_CURSOR Function:Opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR. Syntax DBMS_SQL.OPEN_CURSOR RETURN INTEGER; Returns the cursor ID number of the new cursor

•

PARSE Procedure

BIND_VARIABLE

Syntax

Syntax

DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, INTEGER, name IN language_flag IN INTEGER) ;

Business Learning Center

Satyam Confidential

DBMS_SQL.BIND_VARIABLE ( c IN VARCHAR2, value IN )

31

Some Methods Syntax Cont.. •

EXECUTE_AND_FETCH Function Syntax DBMS_SQL.EXECUTE_AND_FETCH ( c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

•

The EXECUTE_AND_FETCH function returns the number of rows actually fetched.

•

VARIABLE_VALUE Procedure Syntax DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, value OUT );

Business Learning Center

Satyam Confidential

32

DBMS_SQL Example

Business Learning Center

Satyam Confidential

33

DBMS_SQL Example

Business Learning Center

Satyam Confidential

34

DBMS_SQL Example

Business Learning Center

Satyam Confidential

35

Native Dynamic SQL Vs DBMS_SQL Native Dynamic Sql

DBMS_SQL

Native dynamic SQL is integrated with SQL

The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically

Native dynamic SQL code is typically more compact and readable

Equivalent code is more lengthy and complex.

Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls.

The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead.

Native Dynamic SQL Supports User-Defined Types

The DBMS_SQL package does not support user-defined objects, collections, and REFs.

Native dynamic SQL is not supported in client-side Programs.

DBMS_SQL is Supported in Client-Side Programs

Native Dynamic Sql statements can’t be larger than 32KB.

DBMS_SQL Supports SQL Statements Larger than 32KB

Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation.

DBMS_SQL Lets You Reuse SQL Statements. The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.

Business Learning Center

Satyam Confidential

36

The Problem • It is difficult to build a system that actually meets the stated requirements. • It is very difficult to build a system that doesn’t require massive changes within a short period of time. • It is impossible to build a system that will not be obsolete sooner or later. Business Learning Center

Satyam Confidential

37

Unknowns • What elements are involved? – Example: The monthly summary table may not exist.

• What should be the search criteria? – Example: Always one type of Search condition require?

• How you should proceed? – Example: Is a hash join hint the best option?

• Whether or not you can proceed? – DDL is still prevented in PL/SQL, isn’t it?

Business Learning Center

Satyam Confidential

38

Where To Use Dynamic SQL •

Many types of applications need to use dynamic queries Example:o

Applications that allow users to input or choose query search or sorting criteria at runtime

o

Applications that allow users to input or choose optimizer hints at run time

o

Applications that query a database where the data definitions of tables are constantly changing

o

Applications that query a database where new tables are created often

Business Learning Center

Satyam Confidential

39

Questions & Open Discussions

Contact Information

Please provide your valuable feedback

Moiz Lokhandwala Email : moiz [email protected]. Phone:Business09393706292. Learning Center

Venkataramana Kolla Email : [email protected] Satyam Confidential

40

Business Learning Center

Satyam Confidential

41