NEWBIE: converting MSSQL to oracle

I have many questions. I hope that the *groups* can help.

environment, TOAD. Oracle 9i. Access 2000. example. I have an orders table and order_item table.

create table orders (

 order_id number,
 order_date date,
 order_ref varchar2(40));
 

create table orders_items (
 order_id number,
 sku_id varchar2(40),
 sku_name varchar2(40) );

I am trying to create a procedure to reuse of a query of the form

select *
from orders, orders_items
where orders.order_id = orders_items.order_id

AND order_ref = 'SOMEREF'
and order_date < trunc(sysdate) 
and order_date >= trunc(sysdate-SOMENUMBER)

the two parameters I would like to pass to the function are SOMEREF AND SOMENUMBER am guessing something like this
call get_order_info( 'ref1', 30);
to get the orders from ref1 in the last 30 days.

I have read that a ref cursor is the way to go. But all of the examples use the DBMS_OUTPUT package to display results, I can not find something that works and actually can be called from either TOAD or Access and display the result set.

I have created several attemps at this but am not posting the ramblings of the attempts. I have just plain confused myself.

In MSSQL land I would just put a select into the stored procedure, and it would work just fine, but here in Oracle land I am completely lost. Received on Tue Dec 16 2003 - 16:54:01 CST