From: Luis Cabral <luiscabral@starmedia.com>
Subject: Re: Using the MAx function in PL/SQL
Date: 2000/05/05
Message-ID: <8eu7kh$evd$1@nnrp1.deja.com>#1/1
References: <8erst8$qp7$1@nnrp1.deja.com>
X-Http-Proxy: 1.0 linux.comlasa.com.br:3128 (Squid/2.1.PATCH2), 1.0 x38.deja.com:80 (Squid/1.1.22) for client 192.168.1.244, 200.246.181.216
Organization: Deja.com - Before you buy.
X-Article-Creation-Date: Fri May 05 10:26:33 2000 GMT
X-MyDeja-Info: XMYDJUIDluiscabral
Newsgroups: comp.databases.oracle.tools
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT)


Hi

You use the INTO clause inside a PL/SQL block.
In a dynamic statement like that, you must not
use it. Instead, you must code the Select normally
(without INTO) and use DBMS_SQL functions to get
the value returned.


In article <8erst8$qp7$1@nnrp1.deja.com>,
  newopt@my-deja.com wrote:
> I am using the MAX function to retrieve the latest date/timestamp
 value
> from a column in an Oracle dbase table.
>
> When I try to include an "INTO" statement it fails??
> Here's the error statement:
> SELECT MAX(TDM_TS_DT) INTO v_tgttstmp FROM SINCR.WIPMASTER (the sqlstr
> value)
> *
> ERROR at line 1:
> ORA-00905: missing keyword
>
> Here's the code sniplet:
> procedure calc_elapsed_time (v_regionschema Varchar2) IS
> BEGIN
> -- Open new cursor and return cursor ID.
>   cid := DBMS_SQL.OPEN_CURSOR;
>
> -- Build the "Select MAX" SQL statement
>   sqlstr := 'SELECT MAX(TDM_TS_DT) FROM
> '||v_regionschema||'.WIPMASTER'; (this format works)
>   sqlstr := 'SELECT MAX(TDM_TS_DT) INTO v_tmpdate FROM
> '||v_regionschema||'.WIPMASTER'; (this format errors)
>
>   dbms_output.put_line(sqlstr);
>
> -- Execute the dynamic SQL statement built by concatenating the
> -- ICOMS table name to the SELECT MAX command.
>   DBMS_SQL.PARSE(cid, sqlstr, dbms_sql.native);
> -- 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 calc_elapsed_time;
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>


Sent via Deja.com http://www.deja.com/
Before you buy.