Home » SQL & PL/SQL » SQL & PL/SQL » Pl Sql - Ref Cursorsor (oracle 9i)
Pl Sql - Ref Cursorsor [message #354383] Sat, 18 October 2008 05:34 Go to next message
sivaram2006
Messages: 7
Registered: October 2008
Location: chennai
Junior Member
Hi all,

I had a query regarding Pl Sql.

In a stored procedure, I created a table dyanmically using Execute Immediate.
I modified the data in that table as per requirement.
Now, in order to fetch that data to the front end, I use a Ref Cursor,

When I try to fetch the data from Ref Cursor, it says "Table Doesnot exist".
Since that table will be created only at the run time, it is showing that error.
In this case how can i achieve this..

Pls see the following code..

EXECUTE IMMEDIATE 'CREATE TABLE TEMP_SPR_DYNA(CALLID NUMBER(10), LOG VARCHAR2(400)';

---- did some modification on that table data

OPEN RC1 FOR
SELECT * FROM TEMP_SPR_DYNA;

it says that THE TABLE "TEMP_SPR_DYNA" DOES NOT EXIST.

on the other hand if I use

OPEN RC1 FOR
EXECUTE IMMEDIATE 'SELECT * FROM TEMP_SPR_DYNA';

IT IS SAYING SELECT STATEMENT IS MISSING..

Help me with some solution...

Thanks a lot..
Re: Pl Sql - Ref Cursorsor [message #354385 is a reply to message #354383] Sat, 18 October 2008 05:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all: why do you think you need to create a table at runtime? This is VERY BAD in Oracle.
Remember: Oracle is NOT SQLServer/Sybase.

The fact that your table does not exist at compile-time forces you to twist and turn in your code.
In short: your "execute immediate" should not be used in defining the ref cursor.

But the main error is creating the table. The query you use for filling the table can also be used in the calling program, or be returned as a ref cursor.
Re: Pl Sql - Ref Cursorsor [message #354389 is a reply to message #354385] Sat, 18 October 2008 06:09 Go to previous messageGo to next message
sivaram2006
Messages: 7
Registered: October 2008
Location: chennai
Junior Member
First of all.. thanks for your reply..

The main reson for creating a table is that,

The Stored Proc is already existing one and I am modifying it. My requirement is that, the existing Ref Cursor will return a set of rows.

Among those set of rows, I should use some 5 conditions and based on those conditions some times,

I need to remove that row from that set of rows or I need to modify some columns(those columns is also based on some conditions) in that rows.

---- For all these purpose, I created a table, copied the data of refcursor to that table and performing all operations and fetching that back...
-- Another Reson for this is that, the number of columns that the exisitng ref cursor returns is also variable (from 2 to Max 30 Columns)

Is this Idea wrong.. Pls give some idea how to approach this..

Thanks a lot...

Re: Pl Sql - Ref Cursorsor [message #354394 is a reply to message #354389] Sat, 18 October 2008 06:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you really really really can't do without intermediate storage of the data, you should use a (global temporary?) table that you create once and reuse.
That way, you can use static sql, because at compile time of your procedure, the object is known.

Still, don't be afraid to use joins and complicated conditions in a SQL-statement. Again, Oracle is not SQLServer/Sybase. (I take it you or your procedure come from there?)
Re: Pl Sql - Ref Cursorsor [message #354414 is a reply to message #354383] Sat, 18 October 2008 12:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have worked with many small groups where developers have latched onto the idea of creating tables dynamically as part of a coding solution. I see this most often in .NET circles and other MS toolset develoment shops. As a database guy at heart, I never liked this practice at all. It is very inefficient to say the least and presents many negative issues which run the gammet of reduced performance, increased maintenance costs, more difficult debugging, and incompatability with other features of the oracle database. There issues generally only become evident over time which in turn just makes the problems they cause worse because none had an opportunity to plan for them.

Still, for someone entering such a shop as above, there is no way for you to undo what was done so you have to go with the flow for a while.

To that end, I suggest you read up on the OPEN statement in plsql. In partcular, who said you had to hard-code the sql statement. Use a variable to house the sql as in

SQL> set serveroutput on
SQL> declare
  2     v_sql varchar2(32000);
  3     c1 sys_refcursor;
  4     v_c varchar2(1);
  5  begin
  6     v_sql := 'select * from dual';
  7     open c1 for v_sql;
  8     fetch c1 into v_c;
  9     close c1;
 10     dbms_output.put_line('v_c='||v_c);
 11  end;
 12  /
v_c=X

PL/SQL procedure successfully completed.


This solution example above itself demostrates the stupidity of the pratice of dynamically creating tables.

Normal forms in database design teach us that bad design results in what are called "update anamolies". Indeed,there is a practical rule we can rely on: whenever a change in one row of some table, requires additional changes to one or more rows in the same or another table, a normal form has been violated, because you are dealing with an update anomoly.

There is a simliar theory for coding as well though most people do not know of it. We see above that the need to go from static parsing of sql (hard coded select), to dynamic parsing of sql (text string gets the sql that is parsed at runtime), is mandatory based on the practice of dynamically creating tables. Your choice of application design has forced your hand at coding time, to use what are generally considered poor practice, in other areas besides just the one you thought you were making a decision for. Things are always connected and there is always a ramification for every desgin choice.

Oh well, it is what it is. I suppose there may be some situations where such a solution is actually elegantly simple. But I have never seen this practice used in any way other than an abuse of laziness.

Good luck, Kevin
Re: Pl Sql - Ref Cursorsor [message #354446 is a reply to message #354414] Sun, 19 October 2008 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The reason you saw this in many .NET and MS shops, is the same as what I brought up several times: Oracle is not SQLServer/Sybase.

Not sure about the most recent versions of the two, but I know that some versions ago, those RDBMS-es could not handle complex joins or complex filtering very well. On the other hand however, DDL was quite cheap.
Creating temporary tables at runtime to store intermediate results were common practice, and was NOT seen as bad, simply because it beat the crap out of doing it all in a single SQL-statement!

We just need to convince the developers that are used to work with SQLServer and Sybase, and switched to Oracle, that in Oracle it _is_ bad practice.
Re: Pl Sql - Ref Cursorsor [message #354461 is a reply to message #354383] Sun, 19 October 2008 07:10 Go to previous messageGo to next message
phani1980@gmail.com
Messages: 3
Registered: October 2008
Junior Member
Well, I can suggest you create table 'DYNAMICALLY' after opening the Ref-Cursor ( if table already exits then do not recreate, with this you can avoid the error message of creating the table or object which is already existing).

I suggest you go through the following URL for the PL/SQL Advance Concepts.

http://alloracletech.blogspot.com/2008/08/plsql-advance.html

Re: Pl Sql - Ref Cursorsor [message #354466 is a reply to message #354461] Sun, 19 October 2008 09:04 Go to previous message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 19 October 2008 16:03
Useless link to a blog, just spam.

Regards
Michel


Same thing than at http://www.orafaq.com/forum/mv/msg/126470/354462/102589/#msg_354462

Your post just smell spam for your blog.

Regards
Michel
Previous Topic: SQL taking more time when selecting column which has length of Varchar2(4000)
Next Topic: How to use the Loop In a Trigger
Goto Forum:
  


Current Time: Thu Dec 08 06:16:44 CST 2016

Total time taken to generate the page: 0.09142 seconds