Hi all,

There is a neat thing that you can do with an Interbase database that I cannot find an equivalent for in Oracle and I have had a good look. That is issue a select statement against a stored procedure.

Is there some way in Oracle of approximating this behaviour.

Thanks in advance.


An Interbase example is “select * from selectnew(5);” where selectnew is a stored procedure, see below.

PHP Code:
select from selectnew(5);

BRIGADENO  INCIDENTID STATUS 
========= =========== ====== 

        
5     1718571      0 
        5     1718577      0 
        5     1718580      0 
        5     1718609      0 
        5     1718120     
-
The stored procedure looks like:
PHP Code:
ALTER PROCEDURE SELECTNEW (PBRIGADENO INTEGER)
RETURNS (BRIGADENO SMALLINT,
INCIDENTID INTEGER,
STATUS SMALLINT)
AS 
DECLARE 
VARIABLE CountOfPOA SMALLINT;
DECLARE 
VARIABLE CountOfIncident SMALLINT;
begin
/* This procedure compiles a list of incidents that are to be sent to */
/* stations, when the station requests new incidents to be downloaded */
/* include in the list all the new incidents for the requesting brigade */
  
for select BrigadeNoIncidentIdStatus
  from Incident
  where brigadeno 
= :pbrigadeno
  
and (status or status 8)
  
into :BrigadeNo, :IncidentId, :Status
  
do
  
begin
    select count
(*)
    
from POA p
    where incidentid 
= :IncidentId
    
and p.Status 1
    into 
:CountOfPOA;
    if ( 
CountOfPOA then
      suspend
;
  
end

/* also include in the list all calls for the requesting brigade where */
/* the brigade was in attendance that have yet to be completed. */
  
for select distinct BrigadeNoIncidentId, -1
  from POA
  where brigadeno 
= :pbrigadeno
  
and status 0
  into 
:BrigadeNo, :IncidentId, :Status
  
do
  
begin
    select count
(*)
    
from incident i
    where i
.incidentid = :IncidentId
    
and ( (i.Status or i.Status 5)
        or ( 
i.brigadeno = :brigadeno ))
    
into :CountOfIncident;
    if ( 
CountOfIncident then
      suspend
;
  
end
end