CHAPTER 9. Execution Environments

PL/SQL User s Guide and Reference V2.3

9.1 SQL*Plus Environment

You can use PL/SQL in different development tools. This chapter introduces SQL*PLUS features related to PL/SQL.

1. SQL> SAVE file_name [ REPLACE ]

Save current PL/SQL program in specified file and replace the conent if REPLACE is specified.

2. SQL > RUN or SQL > /

Run the block entered. A block can ended with .

3. SQL> GET file_name -- retrieve script from file.

4. SQL > @file_name -- run script

5. VARIABLE variable_name DATATYPE -- define a SQL*PLUS variable

Define a variable which can be passed to PL/SQL programs. If similar name exists in PL/SQL, PL/SQL name has higher precedence. To refer a SQL*PLUS variable in PL/SQL, prefixing the variable with colon :.

6. SQL > PRINT vaiable_name -- display the contents of SQL*PLUS variable.

7. SQL > BEGIN

: I := 100 ; -- set SQL*PLUS bind variable to 100.

END .

8. SQL > SET AUTOPRINT ON -- print out the variables automatically if contents of variable changes.

9. SQL>EXECUTE stored_proc ( actual_parameter_list )

10. SQL > BEGIN -- execute stored procedure from anonymous PL/SQL block.

EXECUTE stored_proc ( actual_parameter_list )

END ;

11. SQL> EXECUTE proc@db_links( :i ) ;

Execute a remote procedure through a database link called db_links, and pass SQL*PLUS variable to the procedure proc.

Example: How to use SQL*PLUS vriable in PL/SQL Program.

SQL > VARIABLE I number ;

SQL > SET AUTPRINT ON

SQL > begin :I := 100 ; end ;

SQL > EDIT

declare

function sum1( i IN number ) return NUMBER IS

s number := 0;

j number ;

begin

for j IN 1 .. i loop

s := s + j ;

end loop;

return s ;

end ;

begin

:i := sum1( :i );

end;

SQL> RUN

9.2 Pro*C Environment

An PL/SQL block can be embedded in anywhere that SQL statement can be embedded in Pro*C program. To embedding a PL/SQL block in Pro*C, use the following syntax:

EXEC SQL EXECUTE

BEGIN -- begin PL/SQL block

END ;

END-EXEC ;

Host variables are used make communication between host code and PL/SQL blocks.

The indicator variables can be used tell the column results in PL/SQL block.

If PL/SQL refer a Oracle table which doesn t exist, you can use DECLARE TABLE statement in PL/SQL block in Pro*C so that the precompiler will use the table declare in the declare statement. Even if the table exists in Oracle database dictionary, precompiler will use table definition defined in DECLARE statement.

The SQLCHECK = SEMANTICS option:

To let precompiler to check syntax and semantics for your SQL statements in PL/SQL block, you have include SQLCHECK=SEMANTICS option when you precompile your embedded C program. Actually, the optional become a must when PL/SQL is used in Pro*C code.

A PL/SQL block is treated as a single SQL statement in Pro*C. You can as use to enter a string which is PL/SQL block program and store in host string variable. Then use method 1, method 2 and method 4 to execute a PL/SQL block .

To a stored procedures in Pro*C, use the following syntax:

EXEC SQL EXECUTE

BEGIN

Stored_proc ( :host_varible1, host_varible_2,  ) ;

END ;

END-EXEC ;

9.3 Oracle Call Interface

OCI processes SQL and PL/SQL block in the similar way to Pro*C with one exception. Inside PL/SQL block, you must use the OBNDRA, OBINDPS, or OBNDRV call, not ODEFIN or ODEFINPS, to bind all placeholders in a SQL or PL/SQL statement. This holds for both imput and output placeholders.

In PL/SQL, all queries must have an INTO clause containing placeholders (host or PL/SQL varibles) that correspond to items in the select list.

Call to stored procedure from OCI environment is same as call from Pro*C.