Anonymous PL/SQL Blocks
This readme is intended as a reference document for those trying to lookup and/or find out how to execute an anonymous PL/SQL block. These examples were done with ‘Oracle Database 10g Enterprise Edition Release 10.2.0.1.0’, ‘Oracle Database 11 (version 11.1.0.6.0)’, and VA Smalltalk 8.0.1. The following is shown/discussed:
•Passing data to a pl/sql block and inserting it into a table
•Retrieving data from a pl/sql block using a select statement
•Retrieve the system time from a pl/sql block
It should be noted that data is passed in and out of blocks using parameter markers (variable names beginning with a colon). One thing that makes blocks particularly useful is ST does not recognize PL/SQL user defined types. With PL/SQL blocks, the user can pass data in or out of a user defined data type.
The following assumptions are made:
•The reader has Oracle installed and has the proper permissions. For instance log in as ‘System’ and do the following ‘grant’ (check with your administrator to see which grants you need):
GRANT create session, alter session, create table,
create view, create procedure, create trigger, create library,
create tablespace, alter tablespace, drop tablespace,
execute any procedure, unlimited tablespace, create public synonym,
drop public synonym, create sequence, create type
to <username>;
•The user has the Native Oracle 10 feature loaded into their Smalltalk image.
•The user has created an access set and is able to log into Oracle from the Smalltalk image.
•The user has a good working knowledge of Smalltalk and knows how to execute and inspect ST scripts.
Last modified date: 02/27/2021