5 Replies Latest reply: Sep 14, 2010 3:23 AM by Billy~Verreynne RSS

    Create table on the basis of ref cursor.

    adinewbie
      Hi,

      Would like to know whether a temporary table can be created in a PL/SQl procedure on the basis of a ref cursor passed as an input parameter to it. Pseudo code for the same is below:

      Create Function Func_name(Some_var Varchar2)
      return sys_Refcursor
      is
      v_sys_refcursor sys_Refcursor;
      begin
      Open v_sys_refcursor for some_query;
      return v_sys_refcursor;
      end;

      Create procedure Proc_name(v_sys_refcursor IN sys_Refcursor)
      is
      v_Sql varchar2(250);
      begin
      v_Sql:="Create table statement."----> Table structure determined from ref cursor
      Execute immediate v_Sql;
      Some_Executeion();
      End;

      Regards,

      Adi
        • 1. Re
          523861
          can I ask why you want to do this?

          do you want to select on the results of the cursor? why not use a pipelined function?
          • 2. Re: Re
            adinewbie
            Hi,


            Actually I am trying to convert some DB2 UDF's to oracle. Interestingly DB2 UDFs return tables and "joins" on these functions.
            for ex:

            func1(some_var) INNER JOIN func2(some_var) on (some_col1=some_col2)
            where both func1 and func2 are returning tables.

            trying to replicate the same without hampering lengthy codes with these kind of joins I thought of replacing them with GTT's. So I had function's returning sys_Refcursors of some query but while trying to create corresponding GTT's I got stuck since it was like hard coding the table structure.

            Any other solutions to the problem I am facing are welcome. I am not quite sure even if I create a table function how would I put these functions to use in "join" conditions.


            Regards

            Adi
            • 3. Re: Create table on the basis of ref cursor.
              Billy~Verreynne
              adinewbie wrote:

              Would like to know whether a temporary table can be created in a PL/SQl procedure on the basis of a ref cursor passed as an input parameter to it.
              No. Not that there is not an ugly hack that can be used - but then ugly hacks are for ugly people. And you ain't one of them, are you? ;-)
              Actually I am trying to convert some DB2 UDF's to oracle
              Ouch. Not the best of ideas as Oracle makes a really horrible DB2. Oracle does Oracle.. well, rather impressively given the chance.

              And GTTs in the way you want to use them? Think for a moment was is the most expensive and slowest operation on a database? The one that has the biggest impact on performance? I/O.

              What does a GTT need to be created? I/O. What do you need to use such a GTT? I/O.

              Now if you cannot justify with sound technical reasons why this extra I/O is needed, why performance and scalability need to be negatively impacted, then this DB2 approach you want to force on Oracle has absolutely no merit at all.

              Solution? Well, why now use Oracle and not DB2? Surely part of this decision is that switching to the new product will deliver better performance, better results, more flexibility, and so on? How are you going to achieve that by still doing it the DB2 way?

              Back in the 80's I joined a dev team tasked to write a very large government system from Cobol/CICS to Natural/Adabas. The specs I was given was printouts of the Cobol source with the instructions of "+rewrite this in Natural+". When I asked what is the purpose of not refactoring the design and the code and improve it at the same time, I was told that as a junior programmer, I need to shuttup. So I did. The system was completed and user testing started. It was thrown out a few months later. About 2 calender years of development went into it. Luckily it only wasted a few months of my time working on what I know was going to be a failure.

              The underlying fundamental I learned that if something is worth doing in software engineering, it is worth doing well. Or not at all. And a DB2 approach to Oracle falls into the latter category - not worth doing at all as design and coding for failure is not a choice that one has to make.
              • 4. Re: Create table on the basis of ref cursor.
                adinewbie
                Hi,

                I completely agree with the mentioned points, though I am quite lost in the current situation. functions with returning tables and then left,right outer joins on these tables is pretty foreign to me. Need suggestion that how can we if not replicate and then solve this issue. Table functions are an option, but using them in join conditions with Select statements is going to be messy.



                Adi
                • 5. Re: Create table on the basis of ref cursor.
                  Billy~Verreynne
                  The standard approach in Oracle is to
                  a) use PL/SQL as interface layer
                  b) return ref cursors to the client

                  The reason for (a) is that in encapsulates the SQL and abstracts the SQL logic from the client. You can now modify, tune and change the SQL code without having to touch and recompile a single byte of client code. The client also does not need to know the table names, the data structures, the database model and so on. The PL/SQL code can also implement robust business logic and validation layer to protect the data integrity of the data in the database. It also can be used as a security layer.

                  Where a client needs to deal with a data set from the database, (b) is used to return a ref cursor to the client. Simple and brief explanation. All SQLs need to be "compiled" into cursors. A cursor is like a "binary program" that contains the execution plan. A ref cursor is a pointer to this "program" that is passed to a client. Via the ref cursor, the client can call the standard interfaces of the cursor program - such as fetching data from it.

                  What does not work ito performance and scalability is to use PL/SQL code to pull data from the SQL database into PL/SQL data structures (residing in expensive server memory). Use these PL/SQL data structures as "buffers" for returning "result sets" to the client.

                  So the basic concept that your clients need to support in their code is Oracle reference cursors - and this has been part of the client Oracle Call Interface for many many years. So there's no reason why a client today should not support a ref cursor.

                  I suggest reading:
                  <li>Oracle® Database Advanced Application Developer's Guide
                  <li>Oracle® Database Concepts