Re: A few simple Oracle questions

Mark W. Eaton <eaton_at_best.com> wrote in article <35232EAE.83802664_at_best.com>...

> SQL*Plus related
> --------------------------
> How do I tell the Server to return only a fixed number of rows in
 response to a
> query.
> In isql (Sybase) I would say
> set rowcount 10
> select fld1 from table1 where fld2 > 100
> This would return the first 10 rows that fulfilled the condition.
> How do I achieve the same in SQL*Plus. There does not seem any
> environment variable that I can set.

You can use rownum as another poster has said, but be aware the rownum check occurs before any sort in a order by i.e. select a from b where rownum < 10 order by a
will return 10 rows picked at random, not the top ten rows after ordering by A

> ------------------------------------------------------------------------
> OCI related
> -----------------
> OCIStmntExecute( ) returns the status of executing a SQL command.
> Is there a call to find the number of result rows before doing a fetch ?
> ( equivalent to a Sybase DBCOUNT () ) which would facilitate doing a
> single memory allocation for storing the results

You cannot count the rows without effectively running the query twice. Normally, a reasonably sized host array is defined (with e.g. a hundred elements) and array fetches are used. This is only slighly more complicated than fetching all rows in one go.

> ------------------------------------------------------------------------
> Oracle Database Server - Datatypes related
> --------------------------------------------------------------
> User defined types in Oracle refer to composite fields.
> In Sybase they are more like typedefs in C/C++
> e.g I would define a user defined type ut_zip as char(6)
> This way whenever I had addresses the zip field would be of type ut_zip.
> This facilitated a single point of change to the zip fields and also
> maintenance / searching the database for zip fields.
> How can I achieve the same in Oracle ?
> ------------------------------------------------------------------------
I don't believe you can. Normally, this is handled by the database design tool. Alternatively, a table creation script in sql*plus can have substitution variables e.g.

define NameType = 'VARCHAR2(35)'

create table x ( y &NameType NOT NULL );   Received on Thu Apr 02 1998 - 00:00:00 CST