lv_sql := 'Select EmpNo, DeptName From (
Select EmpNo, DeptName from Emp a, Dept B
where a.DeptId = b.DeptID) EmpDept';

SET_BLOCK_PROPERTY('header',QUERY_DATA_SOURCE_name, lv_sql);
GO_BLOCK('header');
clear_block;
execute_query;

PROCEDURE dynamic_cursor ( PC$Clause  IN Varchar2 )
IS
  cursor_number EXEC_SQL.CursType; 
 
  LN$Empno       Number; 
  LC$Name        Varchar2(30); 
  LN$count       Number; 
  LC$sql_order   Varchar2(256);
BEGIN
  
	cursor_number := Exec_SQL.Open_cursor; 
	LC$sql_order := 'Select empno,ename From EMP ' || PC$clause  ;
	EXEC_SQL.PARSE(cursor_number, LC$sql_order); 
	EXEC_SQL.DEFINE_COLUMN(cursor_number,1,LN$Empno); 
	EXEC_SQL.DEFINE_COLUMN(cursor_number,2,LC$Name,30); 
	LN$count := EXEC_SQL.EXECUTE(cursor_number); 
	While EXEC_SQL.FETCH_ROWS(cursor_number) > 0 Loop 
	  
	  EXEC_SQL.COLUMN_VALUE(cursor_number,1,LN$Empno); 
	  EXEC_SQL.COLUMN_VALUE(cursor_number,2,LC$Name); 
	  message('Fetched: '||LC$Name||' Empno='||to_char(LN$Empno));
	  
	End Loop ;
	EXEC_SQL.CLOSE_CURSOR(cursor_number); 
  

EXCEPTION 
  When EXEC_SQL.INVALID_CONNECTION Then
     message('Unexpected Invalid Connection error from EXEC_SQL'); 

  When EXEC_SQL.PACKAGE_ERROR Then
     message('Unexpected error from EXEC_SQL: '||to_char(EXEC_SQL.LAST_ERROR_CODE)|| EXEC_SQL.LAST_ERROR_MESG); 

  If EXEC_SQL.IS_OPEN(cursor_number) Then
    EXEC_SQL.CLOSE_CURSOR(cursor_number); 
    message('Exception - Cleaned up Cursor'); 
  End if; 

END;

and offcourse ----calling procedure---------------

Dynamic_Cursor( :BL.IN_CLAUSE );

declare
 type empdtlrec is record (empno  number(4),
                           ename  varchar2(20),
                           deptno  number(2));
 empdtl empdtlrec;
begin
 execute immediate 'select empno, ename, deptno ' ||
                   'from emp where empno = 7934'
   into empdtl;
end;

     declare
           lv_sql varchar2(1000);
     begin
     pause;
     lv_sql:=
         '(select empno, to_char(name) name from emp)';
           set_block_property
          ('block_name',
           QUERY_DATA_SOURCE_NAME,
           lv_sql);

----when-new-form-instance--

     go_block('block_name');
     execute_query;
          end;