A function that has a timestamp as a parameter and returns a timestamp
Create a table:
SQL> edit
Wrote file afiedt.buf
1* create table ts (id integer, tstamp timestamp)
SQL> /
Table created.
Insert a row:
SQL> edit
Wrote file afiedt.buf
1* insert into ts (id, tstamp) values (1, systimestamp)
SQL> /
1 row created.
Select the row and display it:
SQL> select * from ts;
ID TSTAMP
---------- ---------------------------------------------
1 09-APR-10 02.49.36.940000 PM
Create the function:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION myFuncTestDate(inDate IN DATE) RETURN TIMESTAMP IS
2 outDate TIMESTAMP;
3 BEGIN
4 select tstamp INTO outDate from ts where id = 1;
5 RETURN outDate;
6* END;
SQL> /
Function created.
Call the function using an anonymous block:
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 outDate TIMESTAMP;
3 BEGIN
4 outDate := myFuncTestDate (SYSDATE);
5 DBMS_OUTPUT.PUT_LINE('outDate = ' || TO_CHAR (outDate, 'DD-Mon-YYYY hh:mi:ss.ff AM'));
6* END;
SQL> /
The results of executing the above code are:
outDate = 09-Apr-2010 02:49:36.940000000 PM
PL/SQL procedure successfully completed.
Call the function from ST:
"Setup the connection"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
"Variable names"
funcName := 'myFuncTestDate'.
returnValueName := 'out_date'.
paramRowName := 'in_date'.
"Variable types"
inOutParams := AbtCompoundType new
addField: (AbtOracle10TimeStampField new
name: returnValueName);
addField: (AbtOracle10TimeStampField new
name: paramRowName).
"A dictionary to hold values"
aDict := Dictionary new.
aDict
at: paramRowName put: AbtTimestamp now;
at: returnValueName put: AbtTimestamp now.
"Functioon spec"
functionSpec := AbtFunctionSpec new
functionName: funcName;
useSqlCall: true;
parameters: inOutParams.
"Invoke the function"
resultSet := connection invokeFunctionSpec: functionSpec withInputDictionary: aDict.
resultSet
The result of inspecting the above script is:
In the above function, nothing was done with parameter ‘inDate’ but I can change the function to return the passed parameter as follows:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION myFuncTestDate(inDate IN DATE) RETURN TIMESTAMP IS
2 outDate TIMESTAMP;
3 BEGIN
4 select tstamp INTO outDate from ts where id = 1;
5 RETURN inDate;
6* END;
SQL> /
Function created.
Executing the same ST code above and inspecting it returns:
Both values are the same except for the fractional seconds because the timestamp was passed into a date parameter and passed back as a timestamp.
Last modified date: 06/01/2018