Re: NEWBIE: converting MSSQL to oracle

VC,
Part of my problem is that I am new to Oracle programming. Most all of the examples I have seen have been of the type you described below.  They typically use either SQL*plus and print, or DBMS_OUTPUT. Which for a production system, does me no good.

My clients need to run these queries via odbc. Personally, I have been using TOAD to develop queries.

How would one run this query in TOAD, or via SQL through an odbc CONNECTION. Thanks to you all for trying to help me out!

"VC" <boston103_at_hotmail.com> wrote in message news:<ZYXDb.135069$_M.691196_at_attbi_s54>...
> 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 - 18:33:25 CST