Calling a function which uses dynamic SQL from within an SQL statement.

  
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>

Related examples in the same category

1.	First DBMS_SQL Example
2.	Pass a query statement to a stored procedure
3.	Use dbms_sql.open_cursor create a cursor
4.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.	Use dbms_sql.bind_array to bind array to a cursor
6.	Use dbms_sql.parse to bind a select statement to a cursor
7.	Call dbms_sql.describe_columns2 to get info for a column
8.	Non-Query DML and DDL Statements
9.	Use DBMS_SQL with the RETURNING clause.
10.	DBMS_SQL.NATIVE;
11.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.	Dynamic sql statement with variable binding
13.	Dynamic select statement and row fetch
14.	Execute PL/SQL block
15.	DBMS_SQL package: dynamic SQL
16.	Pass where clause to a stored procedure
17.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
18.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
19.	drop the supplied table using dynamic SQL.
20.	DBMS_SQL.BIND_VARIABLE_CHAR
21.	illustrate the importance of setting out_value_size.
22.	Illustrates the interaction of roles and dynamic SQL.
23.	illustrates a DDL statement which is built dynamically from the procedure parameters.
24.	Use dbms_sql to process query, cursor and value
25.	Use dbms_sql.bind_variable to bind variable
26.	dbms_sql.varchar2_table type variable

Calling a function which uses dynamic SQL from within an SQL statement. : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category