This content has been marked as final. Show 5 replies
Actually I am trying to convert some DB2 UDF's to oracle. Interestingly DB2 UDFs return tables and "joins" on these functions.
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.
adinewbie wrote: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? ;-)
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.
Actually I am trying to convert some DB2 UDF's to oracleOuch. 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.
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.
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