1 2 3 4 Previous Next 49 Replies Latest reply: Sep 12, 2013 4:36 AM by Paul M. RSS

    Best way to send an procedure/function output to the application

    Haider Hussain

      Hello,

       

      What is the best way to send the output of a PL/SQL function / procedure to the application in Oracle 11g. I understand that it could be different for applications built in JAVA , .Net , SAP etc.

       

      Could you please give a brief overview on all possible application makes.

       

      Thanks,

      Haider

        • 1. Re: Best way to send an procedure/function output to the application
          Ishan

          Normally, an application requires output to be sent as a result set and we use REF Cursors  for it.

           

          PL/SQL 101 : Understanding Ref Cursors

           

          Thanks,

          Ishan

          • 2. Re: Best way to send an procedure/function output to the application
            Paul M.
            What is the best way to send the output of a PL/SQL function / procedure to the application in Oracle 11g.

            It also depends on the output of the PL/SQL function / procedure. Just an example :

            SQL> create or replace function see_version

              2  return varchar2

              3  is

              4     OUT1 varchar2(80);

              5  begin

              6     select banner into OUT1 from v$version

              7     where rownum=1;

              8     return OUT1;

              9  end;

            SQL> /

             

            Function created.

             

            SQL> exit

            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            $ DISP=`sqlplus -s / as sysdba << EOF

            > set pages 0

            > set feed off

            > select see_version from dual;

            > exit

            > EOF

            > `

            $ echo $DISP

            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

            $

            • 3. Re: Best way to send an procedure/function output to the application
              Oracle Maniac

              Hi Paul M ..Can we ensure using rownum<=1 that we will always get this data "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production " ?

              • 4. Re: Best way to send an procedure/function output to the application
                Paul M.

                Can we ensure using rownum<=1 that we will always get this data "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production " ?

                It's not relevant for the example, I could have used anything else.

                • 5. Re: Best way to send an procedure/function output to the application
                  Paul M.

                  Anyway, the answer is yes, v$version is derived from x$version :

                  SQL> desc x$version

                  Name                                      Null?    Type

                  ----------------------------------------- -------- ----------------------------

                  ADDR                                               RAW(8)

                  INDX                                               NUMBER

                  INST_ID                                            NUMBER

                  BANNER                                             VARCHAR2(80)

                   

                  SQL> select INDX,BANNER from x$version;

                   

                        INDX

                  ----------

                  BANNER

                  --------------------------------------------------------------------------------

                           0

                  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                   

                           1

                  PL/SQL Release 11.2.0.3.0 - Production

                   

                           2

                  CORE    11.2.0.3.0      Production

                   

                           3

                  TNS for Linux: Version 11.2.0.3.0 - Production

                   

                           4

                  NLSRTL Version 11.2.0.3.0 - Production

                   

                   

                  SQL>

                  • 6. Re: Best way to send an procedure/function output to the application
                    Mustafa KALAYCI

                    it depends to what your output is. if it is a standard built in data type like varchar2, number it must be a return value of a function. if your output is a result set (like a table data) you can use ref cursor structure but for my experience I do not like this method. it makes many trouble to like trying debug. also it become a dynamic cursor. I insert the data into a temp table and then select it. also you might want to keep the result for logs.

                    • 7. Re: Best way to send an procedure/function output to the application
                      rp0428
                      Hi Paul M ..Can we ensure using rownum<=1 that we will always get this data "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production " ?

                      NO - the ONLY way to ensure the order of a result set is to use ORDER BY.

                      • 8. Re: Best way to send an procedure/function output to the application
                        rp0428

                        Anyway, the answer is yes, v$version is derived from x$version :

                        ABSOLUTELY WRONG!

                         

                        The ONLY way to ensure the order of a result set is to use ORDER BY. Oracle 'may' return the row with INDX = 0 as the first row or it may not.

                         

                        Since V$VERSION does NOT have a column to order on you could only guarantee that result by  selecting from X$VERSION and using ORDER BY on the INDX column or use a WHERE clause for 'INDX = 0'.

                        • 9. Re: Best way to send an procedure/function output to the application
                          Billy~Verreynne

                          Is this also true of internal (and transient) data structures that exist in the SGA only - and also does not exceed a data block size and always have the same physical sequence/structure order when created in the SGA?

                          • 10. Re: Best way to send an procedure/function output to the application
                            BluShadow

                            MustafaKALAYCI wrote:

                             

                            it depends to what your output is. if it is a standard built in data type like varchar2, number it must be a return value of a function. if your output is a result set (like a table data) you can use ref cursor structure but for my experience I do not like this method. it makes many trouble to like trying debug. also it become a dynamic cursor. I insert the data into a temp table and then select it. also you might want to keep the result for logs.

                             

                            Ref cursors do not make trouble, they are the ideal solution for supplying query results back to a application layer.

                            You say you insert the data into a temp table and then select it?  Why?  That just results in more overhead, and still a need to supply a query back to the application... and how do you do that.... with a ref cursor.

                             

                            I suspect the reason you don't like it is simply because you are not using them correctly.

                            • 11. Re: Best way to send an procedure/function output to the application
                              Mustafa KALAYCI

                              hi BluShadow,

                               

                              no the reason is not using them wrong. maybe it is a prejudgement. our developers are using it already but for example when a procedure does not return the rows they want, debug become really painful.

                               

                              by the way, storing data is important in my systems as logs. that is why I store them. also am I wrong, please correct if I am, dynamic sql structure is slower than static sql and a ref cursor is working as dynamic sql. especially in my systems, developers do not use strong ref cursors ( there is no return type definition at ref cursor) I believe this would be slower than static sql right ?

                              • 12. Re: Best way to send an procedure/function output to the application
                                BluShadow

                                MustafaKALAYCI wrote:

                                 

                                hi BluShadow,

                                 

                                no the reason is not using them wrong. maybe it is a prejudgement. our developers are using it already but for example when a procedure does not return the rows they want, debug become really painful.

                                 

                                Debugging isn't difficult.

                                Queries should be written stand-alone first to ensure they do what is required and then when happy, put it in the code.

                                 

                                 

                                by the way, storing data is important in my systems as logs. that is why I store them.

                                 

                                So, you're duplicating data that is already existing on the database?  just because someone has queried it?

                                 

                                If you need to audit records "viewed by a user", then have the application write audit back to the database for each record they actually view, or just store the search parameters so you can know what records were retrieved.  Seems a lot of unnecessary effort to store all the record data each time.

                                 

                                 

                                 

                                also am I wrong, please correct if I am, dynamic sql structure is slower than static sql and a ref cursor is working as dynamic sql. especially in my systems, developers do not use strong ref cursors ( there is no return type definition at ref cursor) I believe this would be slower than static sql right ?

                                 

                                 

                                A query is a query is a query, whether you create it dynamically or statically.  Things that slow down queries are I/O, hard parsing of frequently used queries (often due to lack of bind variables) etc., but not really the fact that a query is constructed dynamically.  If a ref cursor is constructed with an unknown projection (and hence an unknown return type definition as you call it), then that's quite normal, as the application layer will typically determine the result structure itself.

                                 

                                It's still not clear how you are doing your queries, it still sounds like you are querying the data into a temporary table and then querying the data from that temporary table for the application.  Now that will slow the process down.

                                • 13. Re: Best way to send an procedure/function output to the application
                                  Mustafa KALAYCI

                                  BluShadow,

                                   

                                  my structure much more different than the thread owners. one of my client is a insurance company and as a group policy every generated report data must be kept. so I have to store them, secondly these are not just a querying some tables. there are many many calculations and result data is really different. My first rule is use less plsql more sql basically. I tried to get that data as just one sql but almost impossible. there are packages to get reports and they have average 5000 lines (except the calling other package and procedures). I did not design this system just consulting and developing.

                                   

                                  I should test dynamic cursor fetch and static cursor fetch times.

                                  • 14. Re: Best way to send an procedure/function output to the application
                                    BluShadow

                                    MustafaKALAYCI wrote:

                                     

                                    I should test dynamic cursor fetch and static cursor fetch times.

                                     

                                    Consider it like this...

                                     

                                    By the time the SQL process/engine get's the SQL and executes the query, it doesn't know (or even care) whether that SQL statement was constructed dynamically or statically.  So why would it run the query slower just because it was constructed dynamically?

                                    1 2 3 4 Previous Next