Re: NEWBIE: converting MSSQL to oracle

Hello,

Why not just use a reference cursor ??? Seems much simpler ...

CREATE OR REPLACE package my_pkg as
  type myCursor is ref cursor;
  function p1(BeginDate IN Date, EndDate IN Date) return myCursor; end;
/

CREATE OR REPLACE package body my_pkg as

function p1(BeginDate IN Date, EndDate IN Date) return myCursor  as
 l_cursor myCursor;
 begin
   open l_cursor for 'Select * from t1 where myDate between :BeginDate and :EndDate' using BeginDate, EndDate;

   return l_cursor;
 end;

end;
/

... and in sqlplus:

variable x refcursor;
exec :x := my_pkg.p1(to_date('1-Aug-03', 'dd-mon-yy'), To_Date('28-Aug-03', 'dd-mon-yy')) ;
print x

Rgds.

VC

"Jonathan Gennick" <jonathan_at_gennick.com> wrote in message news:8tjvtv05u3f7pvkl9vntmqltissv7m7rdl_at_4ax.com...
> On 16 Dec 2003 14:54:01 -0800, info_at_athinktank.com (PN) wrote:
>
> > 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
>
> You might look into table functions, which allow you to write
> something like:
>
> SELECT * FROM some_function(arg, arg);
>
> I give an example of a table function in the following article:
>
> http://www.oracle.com/oramag/oracle/01-sep/index.html?o51o9i.html
>
> I know this must seem a lot more complicated than SQL Server makes it.
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com
>
> Join the Oracle-article list and receive one article on Oracle
> technologies per month by email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article, or send
> email to Oracle-article-request_at_gennick.com and include the word
> "subscribe" in either the subject or body.
Received on Wed Dec 17 2003 - 06:27:37 CST