This content has been marked as final. Show 4 replies
How do you plan to use the result set in Forms? Are you basing a block on the result set or returning the result set to a trigger? Either way, your database procedure has to pass a Ref Cursor or a PL/SQL Table of Records to Forms. Tell me how you plan to use the result set and I'll give you code example.
i have write following package and procedure in oracle server
CREATE OR REPLACE Package types
type Cursortype is ref cursor ;
CREATE OR REPLACE PROCEDURE RTN_RECORDSET(
p_str in varchar ,
p_ResultSet OUT TYPES.cursorType )
open p_resultset for
end RTN_RECORDSET ;
i want this record set in forms Module
RTN_RECORDSET('select deptno , dname , loc from dept', dept_curs) ;
if not dept_curs%isopen then
FETCH dept_curs INTO :TXTDEPTNO, :TXTDNAME, :TXTLOC ;
EXIT WHEN dept_curs%NOTFOUND OR dept_curs%ROWCOUNT = 0;
message('procedure not return resultset ') ;
end if ;
when no_data_found then
message('NO DATA Found DURING THIS PERIOD') ;
Since you are just going to display the data from the RefCursor in your Form perhaps you should consider basing your data block on the procedure. If you have access to Oracle Support (Metalink/My Oracle Support) check out Document ID: 66887.1. If you don't have an Oracle Support account, take a look at Oracle Forms 10g release 2: Demos, Tips and Techniques document. It has a generic version of Oracle's document that describes the process.
Hope this helps,
If a response is helpful or correct, please mark it accordingly.
Basically I want to work with Non Database Block. so Pl. Kindly send me related to that example.
My Requirement is - I Want a cursor in oracle form which is dynamically build based on user input.
ex- 'select * from ' || <table name>
here table name is not fixed it depend on user input financial year at the time of logging.
So I want a ref cursor return type in my procedure available at form level.
Hope You I understand my requirement.