Re: A few simple Oracle questions

From: Richard Willemsen <rwillems_at_xs4all.nl>
Date: 1998/04/02
Message-ID: <352345a9.1426163@192.168.0.1>#1/1

On Wed, 01 Apr 1998 22:22:38 -0800, "Mark W. Eaton" <eaton_at_best.com> wrote:

>Our organization is relatively new to Oracle but fairly seasoned with SQL Server
>development. We have a few questions we need to understand to make the
>transition as quickly as possible. Please respond directly via email as I do
>not read this newsgroup regularly,
>
>Sincerely,
>Mark Eaton
>eaton_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.

Try this:

select fld1 from table1 where fld2 > 100 AND ROWNUM < 11;

SQL Reference:

ROWNUM  For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle7 selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT *

	FROM emp
	WHERE ROWNUM < 10



>------------------------------------------------------------------------

>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
>------------------------------------------------------------------------
>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 ?
>------------------------------------------------------------------------

---

Real programmers don't comment their code. It was hard to write, it should be hard to understand.

---

Richard Willemsen
http://www.xs4all.nl/~rwillems/ Received on Thu Apr 02 1998 - 00:00:00 CST