Database Guide : Appendixes : Anonymous PL/SQL Blocks : Retrieve the system time from a pl/sql block
Retrieve the system time from a pl/sql block
Here is a simple block which retrieves the Oracle systimestamp into an ST timestamp:
 
"Log on and get the connection"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
 
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
 
"PL/SQL Block"
wStream := WriteStream on: String new.
wStream
nextPutAll: 'DECLARE '; cr;
nextPutAll: 'ts TIMESTAMP; '; cr;
nextPutAll: 'BEGIN '; cr;
nextPutAll: 'ts := SYSTIMESTAMP; '; cr;
nextPutAll: ':outTS := ts; '; cr;
nextPutAll: 'END; '.
 
"Param Marker name in the PL/SQL block."
timeStampName := 'outTS'.
 
"Setup the data field"
inOutParams := AbtCompoundType new
addField: (AbtOracle10TimeStampField new
name: timeStampName).
"Add a timestamp to hold the value returned"
aDict := Dictionary new.
aDict
at: timeStampName
put: (AbtTimestamp now
date: Date today;
yourself).
 
"Create the anonymous block spec"
anonymousBlockSpec := AbtAnonymousBlockSpec new
plSqlString: wStream contents;
useSqlCall: true;
parameters: inOutParams.
 
"Execute the block"
resultSet := connection invokeAnonymousBlockSpec: anonymousBlockSpec withInputDictionary: aDict.
 
resultSet
 
The variable ‘aDict’ has the timestamp:
C:\Users\documentation\Documents\vastePublisher\stable\VAS Documentation Word\images\388d-iii.jpg
Last modified date: 06/01/2018