From: newopt@my-deja.com
Subject: Using the MAx function in PL/SQL
Date: 2000/05/04
Message-ID: <8erst8$qp7$1@nnrp1.deja.com>#1/1
X-Http-Proxy: 1.1 x26.deja.com:80 (Squid/1.1.22) for client 169.152.69.137
Organization: Deja.com - Before you buy.
X-Article-Creation-Date: Thu May 04 13:11:17 2000 GMT
X-MyDeja-Info: XMYDJUIDnewopt
Newsgroups: comp.databases.oracle.tools
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows 95)


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.