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.


: 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 > begin :I := 100 ; end ;



      function sum1( i IN number ) return NUMBER IS

            s number := 0;

            j number ;


            for j IN 1 .. i loop

                  s := s + j ;

            end loop;

            return s ;

      end ;


      :i := sum1( :i );






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.




                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


                                                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.