Can Oracle do this!?

Can Oracle do this!?

Post by Jona » Sun, 31 Dec 1899 09:00:00



Hi all.
I'm trying to move my SQL code from IB to Oracle, but there are
problems.
In IB using a stored procedure, I can at any time return the values of
the returns variables.
For example:
  FOR SELECT a, b FROM myTable
  WHERE c = 'XXXX'
  INTO :RA, :RB
  DO BEGIN
    SUSPEND; <-- Fetch all returns variables (Now with the quey values)
    RA = 'AAA';
    RB = 'BBB';
    SUSPEND; <-- Fetch all returns variables (Now with my values)
  ....
Can I do this in Oracle?

Tanks in advance!



 
 
 

Can Oracle do this!?

Post by Lothar Armbüste » Sun, 31 Dec 1899 09:00:00


Jonas wrote at 08-Aug-00  16:51:07
on the subject "Can Oracle do this!?":

Quote:>Hi all.

Hello Jonas

Quote:>I'm trying to move my SQL code from IB to Oracle, but there are
>problems.
>In IB using a stored procedure, I can at any time return the values of
>the returns variables.
>For example:
>  FOR SELECT a, b FROM myTable
>  WHERE c = 'XXXX'
>  INTO :RA, :RB
>  DO BEGIN
>    SUSPEND; <-- Fetch all returns variables (Now with the quey values)
>    RA = 'AAA';
>    RB = 'BBB';
>    SUSPEND; <-- Fetch all returns variables (Now with my values)
>  ....
>Can I do this in Oracle?

You could do something similar:

begin
   for a_rec in (select a,b from mytable where c='XXXX') loop
      /* Don't know what suspend does */
      my_proc(a_rec.a,a_rec_b);
   end loop;
end;

In the above code, a_rec is a record variable the takes the values of a
and b from mytable succesively. You can acces these values in the given
way.

Hope that helps,
Lothar

--


D-65346 Eltville        |

 
 
 

Can Oracle do this!?

Post by Greg Akin » Sun, 31 Dec 1899 09:00:00


In Oracle the keyword RETURN returns values and exits the
function.  Is this what you're looking for?
In your program
FOR SELECT a, b FROM myTable
WHERE c = 'XXXX'
INTO :RA, :RB
DO BEGIN
SUSPEND; <-- Fetch all returns variables (Now with the quey
values)
RA = 'AAA';
RB = 'BBB';
SUSPEND; <-- Fetch all returns variables (Now with my values)
...
It looks like teh program will always terminate with Query
values.  Doesn't suspend exit the function?

So
BEGIN
Rec_found := '00' ;
for my_rec  in (SELECT a, b FROM myTable
WHERE c = 'XXXX') loop
  if emp_rec.RA = '01' then
    return my_rec.RB ; <- Exits
  end if ;
end loop ;
return Rec_found ; <- Exits
END ;

Or some such thing?

-greg

-----------------------------------------------------------

Got questions?  Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

 
 
 

Can Oracle do this!?

Post by Rober » Sun, 31 Dec 1899 09:00:00


Jonus,

I'm not sure if I understand the question completely, but look at the
following:

CREATE OR REPLACE PROCEDURE SP_TEST
    (IN_ONE IN VARCHAR2
    , IN_OUT IN OUT VARCHAR2
    , OUT_ONE OUT VARCHAR2
    , OUT_TWO OUT VARCHAR2
    , OUT_THREE OUT VARCHAR2)
IS
BEGIN
SELECT COL_ONE , COL_TWO , COL_THREE ,COL_FOUR
INTO OUT_ONE,OUT_TWO,OUT_THREE, IN_OUT  
FROM TABLEA WHERE COL_ONE = IN_ONE AND COL_TWO = IN_OUT;
END;

The parameters I have declared at the beginning of the procedure(IN_ONE,
IN_OUT, etc. are parameters that can be incoming values or outgoing
(return) values depending on how they are declared.  The calling program
would pass it's own variables and receive the values back.

Robert


> Hi all.
> I'm trying to move my SQL code from IB to Oracle, but there are
> problems.
> In IB using a stored procedure, I can at any time return the values of
> the returns variables.
> For example:
>   FOR SELECT a, b FROM myTable
>   WHERE c = 'XXXX'
>   INTO :RA, :RB
>   DO BEGIN
>     SUSPEND; <-- Fetch all returns variables (Now with the quey values)
>     RA = 'AAA';
>     RB = 'BBB';
>     SUSPEND; <-- Fetch all returns variables (Now with my values)
>   ....
> Can I do this in Oracle?

> Tanks in advance!




--
Posted via CNET Help.com
http://www.help.com/
 
 
 

1. MDX : Canned Report or OLAP

Here is the requirement spec for a report about various
values(items below) for different projects.

item#1 item#2 item#5For2001 item#5For2002 item#6For2001 ...

Here item#1 and 2 are measured in general for the entire
lifecyle of the project, item#5 & item6 is being reported
yearwise. Can this kinda of data be stored in a single
cube? If Yes then what will be the MDX query to fetch the
data in above format.

Thanks,

Ashu

2. PDOXDOS 4.O MEMOS IN REPORTS

3. Anyone know of some canned (cheap or free) DB performance testing software

4. REMINDER: OOPSLA '99 WS on Java and Databases

5. canned code to get db on web quickly via perl or

6. Animating Access 2.0

7. Cans access2.0 engine access btrieve files?

8. bcp canned app

9. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

10. Switching from inhouse to canned package.

11. Canned PARADOX scripts?

12. ADO thinks DBMS is done, but DBMS ain't done