Re: Record sequent number in query?

From: <jmvegalerin_at_my-deja.com>
Date: Wed, 01 Sep 1999 13:15:28 GMT
Message-ID: <7qj8t4$gnj$1@nnrp1.deja.com>

In article <37ccb6b3.0_at_news.global-one.ru>, "Kirill Dylewski" <k.dylevsky_at_globalone.ru> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_020E_01BEF462.E2DEB0D0
> Content-Type: text/plain;
> charset="windows-1251"
> Content-Transfer-Encoding: quoted-printable
>
> Hi All.
>
> Any ideas about PL/SQL function like this:
>
> FUNCTION RecordSequentNumber (query_text VARCHAR2,
> order_by_clause =
> VARCHAR2
> ) RETURN NUMBER
>
> Example:=20
> ...
> n:=3DRecordNumber('SELECT something FROM somewhere WHERE any =
> condition','something1 DESC, something2');
> ...
>
> Thanks, Kirill Dylewski.
>
> ------=_NextPart_000_020E_01BEF462.E2DEB0D0
> Content-Type: text/html;
> charset="windows-1251"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
> <HTML>
> <HEAD>
>
> <META content=3D"text/html; charset=3Dwindows-1251" =
> http-equiv=3DContent-Type>
> <META content=3D'"MSHTML 4.71.1712.3"' name=3DGENERATOR>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT color=3D#000000 size=3D2>Hi All.</FONT> </DIV>
> <DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
> <DIV><FONT color=3D#000000 size=3D2>Any ideas about PL/SQL function
like =
>
> this:</FONT></DIV>
> <DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
> <DIV><FONT size=3D2>FUNCTION RecordSequentNumber (query_text=20
> VARCHAR2,</FONT></DIV>
> <DIV><FONT size=3D2></FONT><FONT size=3D2><FONT=20
>

size=3D2>          &nb s=
>

p;           &nbs p=
>

;           &nbsp ;=
>

             &=
> nbsp;    =20
> order_by_clause VARCHAR2</FONT></FONT></DIV><FONT size=3D2>
> <DIV>) RETURN NUMBER</FONT></DIV>
> <DIV><FONT size=3D2></FONT> </DIV>
> <DIV> </DIV>
> <DIV><FONT color=3D#000000 size=3D2>Example: </FONT></DIV>
> <DIV><FONT color=3D#000000 size=3D2>...</FONT></DIV>
> <DIV><FONT color=3D#000000 size=3D2>n:=3DRecordNumber('SELECT
something =
> FROM somewhere=20
> WHERE any condition','something1 DESC,
something2');</FONT> </DIV>
> <DIV><FONT color=3D#000000 size=3D2>...</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=3D#000000 size=3D2>Thanks, Kirill=20
> Dylewski.</FONT></DIV></BODY></HTML>
>
> ------=_NextPart_000_020E_01BEF462.E2DEB0D0--
>
>

Hi,
Do you need to use the dbms_sql package. This example is from Oracle 7 documentation PL/SQL User's guide and reference:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

   cid INTEGER;
BEGIN
   /* Open new cursor and return cursor ID. */    cid := DBMS_SQL.OPEN_CURSOR;
   /* Parse and immediately execute dynamic SQL statement built by

      concatenating table name to DROP TABLE command. */    DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);    /* Close cursor. */
   DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
   /* If an exception is raised, close cursor before exiting. */    WHEN OTHERS THEN

      DBMS_SQL.CLOSE_CURSOR(cid);
      RAISE;  -- reraise the exception

END drop_table;

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 08:15:28 CDT