Chris,

I am calling the stored procedure within a Java application, via JDBC connection.
So, I'm doing the following:

------------------ BEGIN example
PreparedStatement l_prepStat = new PreparedStatement( "SELECT addNews( ?, ? ) FROM DUAL" );
l_prepStat.setString( 1, "This is a headline" );
l_prepStat.setInt ( 2, l_newID );
ResultSet l_rs = con.executeQuery( l_prepStat, l_prepStat.toString() );

if( l_rs.next() )
{
// Does what have to be done...
}
------------------- END example

As you can see, everything points that I should use a function: I do need to get a value from the ResultSet returned by the "executeQuery(...)".

But, when I run the code, I get an error. I tried to reproduce the error message below (doing almost the same, BUT in SQL*Plus):

------------------ BEGIN example
SQL> select addnews( 'teste', SYSDATE );
select addnews( 'teste', SYSDATE )
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select addnews( 'teste', SYSDATE ) FROM DUAL;
select addnews( 'teste', SYSDATE ) FROM DUAL
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ENHYDRA.ADDNEWS", line 9
ORA-06512: at line 1
------------------- END example

The stored procedure is as follows:

------------------ BEGIN example
CREATE OR REPLACE FUNCTION addNews( l_headline VARCHAR2, l_dt DATE )
RETURN NUMBER AS

l_newId NUMBER( 9 );
BEGIN

l_newId := 1;
SELECT news_id_seq.NEXTVAL INTO l_newId FROM DUAL;

INSERT INTO news( id, headline, dt )
VALUES( l_newId, l_headline, l_dt );

RETURN l_newId;

END;
/
------------------- END example

As you can see, the clause "INSERT INTO news( id, headline, dt ) VALUES( l_newid, l_headline, l_dt );" is not allowed, because it is a DML operation, and the error ORA-14551 is raised.

I got your message when you talk about "style". But, as I said, I'm trying to move my Java code from PostgreSQL to ORACLE as painless as possible. If possible :)

Thanks for all your attention! And sorry (again) for my English...

Roberto