Re: Using the MAx function in PL/SQL

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_at_nnrp1.deja.com>,   newopt_at_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. Received on Fri May 05 2000 - 00:00:00 CEST