| touble with a cursor [message #2264] |
Tue, 02 July 2002 15:41  |
NiGger
Messages: 6
Registered: July 2002
|
Junior Member |
|
|
Hi...
Im trying to retrieve data from a delphi aplication and for that i'd write an procedure like this(obviously resumed):
CREATE OR REPLACE PROCEDURE schema.name_procedure
(
p_param1 OUT schema.table.culumn1%TYPE,
p_param2 OUT schema.table.culumn2%TYPE
)
IS
CURSOR c_name_cursor IS
SELECT column1,column2
FROM schema.table
ORDER BY column2;
BEGIN
OPEN c_name_cursor;
FOR reg IN c_name_cursor LOOP
p_param1 :=reg.column1;
p_param2 :=reg.column2;
END LOOP;
CLOSE c_name_cursor;
END;
/
basicly when a call to the out params with delphi my aplication give me two errors , ORA-06515 and
ORA-06502; i' read about that error and i dont think that the real problem is the description for that, ah and it compile with no errors in my TOAD....
can anybody help me?
Thanks
|
|
|
|
| Re: trouble with a cursor [message #2266 is a reply to message #2264] |
Tue, 02 July 2002 16:00  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Couple of thoughts:
1) How many rows does that cursor return? You have an ORDER BY clause on it which implies multiple rows. But, you are looping through the cursor and then returning the "last" row values as your OUT parameters. If this is really what you want, this is about the most inefficient way to get it.
Do you really want to return values for a single row, or for all rows in your cursor?
2) You are using a CURSOR FOR loop - with that type of loop you do not explicitly open and close the cursor. That is the beauty of the CURSOR FOR loop - that is all handled automatically for you.
|
|
|
|
| Re: trouble with a cursor [message #2268 is a reply to message #2264] |
Tue, 02 July 2002 16:56 |
NiGger
Messages: 6
Registered: July 2002
|
Junior Member |
|
|
First than anything , a lot of thaks for your answer
the error ''cursor already open'' dissapears when i'd delete the lines of open and close cursor , it was a very big mistake of me, cause i already read obout it in QUEST PL/SQL Knowledge EXPERT , but the other error
still there.ORA-06512
answering to your question , i want to return values for all rows in my cursor , is this the problem?
how can i fix it ? where can i found more specific docs
about cursors....
Thanks in advanced
|
|
|
|
| Re: trouble with a cursor [message #2318 is a reply to message #2264] |
Fri, 05 July 2002 08:45 |
NiGger
Messages: 6
Registered: July 2002
|
Junior Member |
|
|
Thankz a lot , My sp finally works....
the script works this way
First i made the following package:
--------------------------------------------
CREATE OR REPLACE PACKAGE schema.ref_cursor
AS
TYPE refcur_delphi_units IS REF CURSOR;
END;
--------------------------------------------
after this i'd make mi SP as follows:
--------------------------------------------
CREATE OR REPLACE PROCEDURE schema.show_delphi_unit
(
p_unit OUT schema.schema.ref_cursor.refcur_delphi_units
)
AS
BEGIN
OPEN p_unit FOR
SELECT field1, field2, fieldn
FROM schema.table
ORDER BY field1;
RETURN;
END;
-----------------------------------------------
OBS:The exception area wasn't included
But i think that the response time is to slow , approximately 10 seconds , with a table with 13.000
records , the table has only 1 primary key , and is indexed standard , anybody have any idea?
Thankz In advanced...
Nigger
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
