Retrieve the system time from a pl/sql block

Database Guide : Appendixes : Anonymous PL/SQL Blocks : 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