Re: NEWBIE: converting MSSQL to oracle

Hello,

Please see examples with 'ref cursor', for ODBC in particular, at: http://osi.oracle.com/~tkyte/ResultSets/index.html

VC

"PN" <info_at_athinktank.com> wrote in message news:e3d76c80.0312171633.5375204f_at_posting.google.com...
> 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 - 20:52:18 CST