SQL>
SQL> CREATE OR REPLACE FUNCTION DynamicSysdate
2 RETURN DATE AS
3
4 v_SQLStatement VARCHAR2(100) := 'SELECT SYSDATE FROM dual';
5 v_CursorID INTEGER;
6 v_ReturnVal DATE;
7 v_Dummy INTEGER;
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 DBMS_SQL.PARSE(v_CursorID, v_SQLStatement, DBMS_SQL.NATIVE);
11 DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_ReturnVal);
12 v_Dummy := DBMS_SQL.EXECUTE_AND_FETCH(v_CursorID);
13 DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_ReturnVal);
14 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
15 RETURN v_ReturnVal;
16 EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
19 RAISE;
20 END DynamicSysdate;
21 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> SELECT DynamicSysdate FROM dual;
DYNAMICSYSDATE
---------------------
Jun 19, 2008 20:39:02
1 row selected.
SQL>
SQL>