| Pl Sql - Ref Cursorsor [message #354383] |
Sat, 18 October 2008 05:34  |
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  |
Frank
Messages: 7901
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 |
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 |
Frank
Messages: 7901
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 |
 |
Kevin Meade
Messages: 2103
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 |
Frank
Messages: 7901
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
