Home » SQL & PL/SQL » SQL & PL/SQL » how to get a multirow result set from a function or procedure
how to get a multirow result set from a function or procedure [message #6218] Thu, 03 April 2003 11:06 Go to next message
Marco Brogioni
Messages: 3
Registered: April 2003
Junior Member
Interbase offers an extremely useful instruction, called SUSPEND
Suppose you have a procedure called PROC(param1, param2,param3).
Inside the body you change param1, param2, param3 values according to your needs.Whenever you call SUSPEND within the body of the procedure, actual values of param1, param2, param3 are output as a row of a query, as many times as you call suspend. So from an SQL console you can use the SQL instruction:
select * from PROC(1, 2,3)
and you get a result set that is exactly the same as a query from a table.
Is there a way to do it with Oracle?
I mean: Can I write a function or procedure that takes params and can be called from a simple select instruction (for example in sql+ or a C++Builder Tquery object)?
Re: how to get a multirow result set from a function or procedure [message #6220 is a reply to message #6218] Thu, 03 April 2003 12:39 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The only way to return a result set as part of a SELECT is to return a collection from a function and then cast that collection, defined at the SQL level, as a table.

But the far more preferred and common way to return a result set from a procedure or function in Oracle is through a REF CURSOR. SQL*Plus and the TQuery object in C++Builder/Delphi fully support result sets based on a REF CURSOR.

See here for more details:

Re: how to get a multirow result set from a function or procedure [message #6222 is a reply to message #6218] Thu, 03 April 2003 15:02 Go to previous messageGo to next message
Anand
Messages: 161
Registered: August 1999
Senior Member
Try "TABLE CAST" feature. This is a powerful feature where you can use a function in the 'FROM' clause of a query. The RETURN parameters of the function is returned in the form of a table. You need to create an object record and table for this purpose.
Re: how to get a multirow result set from a function or procedure [message #6227 is a reply to message #6218] Fri, 04 April 2003 01:34 Go to previous messageGo to next message
Marco Brogioni
Messages: 3
Registered: April 2003
Junior Member
Thanx for your reply, but, I'm sorry, I still can't do what I like.
Take a look at this code:

CREATE TABLE ADIFETTI (
CODDIFETTO CHAR (6) NOT NULL,
DESCDIFETTO CHAR (25) ) ;

=====================================================================
package MARC_PKG
as
TYPE ADIFETTIREC IS RECORD
(
CODICE ADIFETTI.CODDIFETTO%TYPE,
DESCRIZIONE ADIFETTI.DESCDIFETTO%TYPE
);
END MARC_PKG;
=====================================================================
package MARC_PKG_CURSDEF
as
type MARCELLOCURS is ref cursor return MARC_PKG.ADIFETTIREC;

END MARC_PKG_CURSDEF;
=====================================================================
FUNCTION "MARCELLOLIPPI"(numero in number) RETURN MARC_PKG_CURSDEF.MARCELLOCURS
AS
--ACTUALLY PARAM "NUMBER" IS NOT NEEDED...
LIPPI MARC_PKG_CURSDEF.MARCELLOCURS;
BEGIN

OPEN LIPPI FOR SELECT ADIFETTI.CODDIFETTO AS CODICE, ADIFETTI.DESCDIFETTO AS DESCRIZIONE FROM ADIFETTI;

RETURN lippi;
END;
=====================================================================
Just two questions:
1) Which select should I use to retrieve data associated with cursor in SQL+?
2) Which select (or any other SQL sentence) shoud I write in the SQL property of a TQery (C++Builder/Delphi) to get the result set?

Thanx very very very much to everybody
Re: how to get a multirow result set from a function or procedure [message #6234 is a reply to message #6218] Fri, 04 April 2003 18:59 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Here's a SQL*Plus example:

sql>create table ADIFETTI ( 
  2  CODDIFETTO char (6) not null, 
  3  DESCDIFETTO char (25) ) ;
 
Table created.
 
sql>insert into adifetti values ('Test', 'Description');
 
1 row created.
 
sql>insert into adifetti values ('Test 2', 'Another one');
 
1 row created.
 
sql>commit;
 
Commit complete.
 
sql>create or replace package MARC_PKG_CURSDEF
  2  as
  3    type MARCELLOCURS is ref cursor return ADIFETTI%rowtype;
  4  end MARC_PKG_CURSDEF;
  5  /
 
Package created.
 
sql>create or replace function MARCELLOLIPPI
  2    (numero in number)
  3    return MARC_PKG_CURSDEF.MARCELLOCURS
  4  as
  5    LIPPI MARC_PKG_CURSDEF.MARCELLOCURS;
  6  begin
  7    open LIPPI for 
  8      select CODDIFETTO as CODICE, DESCDIFETTO as DESCRIZIONE
  9        from ADIFETTI;
 10  
 11    return lippi;
 12  end;
 13  /
 
Function created.
 
sql>var rc refcursor
sql>exec :rc := MARCELLOLIPPI(1)
  
PL/SQL procedure successfully completed.
 
sql>print rc
 
CODICE DESCRIZIONE
------ -------------------------
Test   Description
Test 2 Another one
 
2 rows selected.


Note that I didn't use a record type here - just a typing based on the rowtype of the table.

In a TQuery, you would put an anonymous block like this in the SQL property:

begin
  :rc := MARCELLOLIPPI(1);
end;


And then set the type of the rc variable to 'Cursor'. You can then just Open the TQuery as if you had a select statement in the SQL property.
Re: how to get a multirow result set from a function or procedure [message #6253 is a reply to message #6218] Mon, 07 April 2003 05:38 Go to previous messageGo to next message
Marco Brogioni
Messages: 3
Registered: April 2003
Junior Member
Sorry to bother you once again, but I still have trouble with C++Builder 5
Whwn I write these instructions in a TQuery:
begin
:rc := MARCELLOLIPPI(1);
end;
I got an error from C++Builder. The fact is that C++ Builder thinks that = is a parameter, since there is a : before the = sign. The Error i got is "Field '=' is of an unknown type" I tried assigning several kind of types to the = "parameter", but it still doesn't work.
Have you got any idea?
I couldn't find a way to work around.
Thanx in advance for your kindness.
Marco
Re: how to get a multirow result set from a function or procedure [message #6259 is a reply to message #6253] Mon, 07 April 2003 14:05 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Sorry, I can't speak specifically for C++Builder - just for Delphi. I was hoping that the TQuery objects in both were similar enough that this would work. Apparently not.

Have you tried using a TStoredProc component?

Previous Topic: how to generate reports in SQL!!PLZ HELP!!!!!!!!!!!!!!!!!!!!
Next Topic: Index
Goto Forum:
  


Current Time: Thu Mar 28 13:27:35 CDT 2024