4 Replies Latest reply: Jun 4, 2010 11:26 PM by 776606 RSS

    How Do i get resultset from Oracle Procedure Into Oracle 10gForms

    776606
      How Do i get resultset from Oracle Procedure Into Oracle 10gForms
        • 1. Re: How Do i get resultset from Oracle Procedure Into Oracle 10gForms
          CraigB
          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.

          Craig...
          • 2. Re: How Do i get resultset from Oracle Procedure Into Oracle 10gForms
            776606
            i have write following package and procedure in oracle server
            oracle procedure
            ------------------------
            CREATE OR REPLACE Package types
            AS
            type Cursortype is ref cursor ;
            end ;

            CREATE OR REPLACE PROCEDURE RTN_RECORDSET(
            p_str in varchar ,
            p_ResultSet OUT TYPES.cursorType )
            as
            begin
            open p_resultset for
            p_str ;
            end RTN_RECORDSET ;


            i want this record set in forms Module
            --------------------------------------------
            DECLARE
            dept_curs TYPES.cursorType;
            begin
            RTN_RECORDSET('select deptno , dname , loc from dept', dept_curs) ;
            if not dept_curs%isopen then
            LOOP
            FETCH dept_curs INTO :TXTDEPTNO, :TXTDNAME, :TXTLOC ;
            EXIT WHEN dept_curs%NOTFOUND OR dept_curs%ROWCOUNT = 0;
            NEXT_RECORD;
            END LOOP;
            else
            message('procedure not return resultset ') ;
            end if ;
            CLOSE dept_curs;
            FIRST_RECORD;
            exception
            when no_data_found then
            message('NO DATA Found DURING THIS PERIOD') ;
            end ;
            • 3. Re: How Do i get resultset from Oracle Procedure Into Oracle 10gForms
              CraigB
              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,
              Craig B-)

              If a response is helpful or correct, please mark it accordingly.
              • 4. Re: How Do i get resultset from Oracle Procedure Into Oracle 10gForms
                776606
                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.

                Braja