Help about DML inside SELECTs

Hello,

I don't know if someone has already asked this question (and if someone has already answered it) , but.. here it goes...

1) Basically, I need to do a INSERT and return a value.
2) But FUNCTIONs do not allow me to do INSERTs (error ORA-14551: DML operations are not allowed inside SELECTs...);
3) And PROCEDUREs do not allow me to return values.

Am I missing something? Am I right about my assumptions?

TIA...
Roberto.

Roberto,

Just a couple of things.

Functions do allow you to do inserts, it must be the way you are trying to do the insert that is at fault. If you post the code I (or someone else) could have a look and point you at the error.

Procedures do allow return values, in the procedure specification you can specify OUT or IN OUT and that will return the value to the calling procedure.

Hope this is of help.

m1l,

Thanks for your hints! I tried using a PROCEDURE with a OUT parameter, but I can't realize where the value will go...

The original code is just below...

---
CREATE OR REPLACE PROCEDURE addNode
(
p_namv IN VARCHAR2,
p_desv IN VARCHAR2,
p_parv IN VARCHAR2,
p_lnkv IN VARCHAR2,
p_nhsv IN VARCHAR2,
p_ordv IN VARCHAR2,
p_frv IN OUT NUMBER,
p_idv IN OUT NUMBER
) IS
BEGIN
p_idv := 0;
SELECT first INTO p_frv FROM nodes WHERE id = p_parv;
IF SQL%FOUND THEN
SELECT nodes_id_seq.NEXTVAL INTO p_idv FROM DUAL;
IF( p_parv = 0 ) THEN
p_frv := p_idv;
END IF;
IF( p_idv > 0 ) THEN
INSERT INTO nodes VALUES (p_namv, p_desv, p_frv, p_parv, p_idv, p_ordv);
INSERT INTO nodesAttribs VALUES( p_idv, 'NUMHDS', p_nhsv);
INSERT INTO nodesAttribs VALUES( p_idv, 'LINK', p_lnkv);
END IF;
END IF;

RETURN p_idv;
END;
/

---

As you can see, the INSERTs are not allowed. How people use to work around this situation? I have an alternative plan, which is first get some values and then insert them. But I'd rather do it in one piece...

I'll just add to the previous sentiments. There is absolutely no relationship between FUNCTIONS and DML. The only one I can think of is if you are then trying to use the function (which contains DML) inside a SELECT statement. *That* you cannot do. This is because a function used in a SELECT statement must not update the state of the database.

Check out this link for more information on calling PL/SQL functions from SELECT statements
http://technet.oracle.com/doc/precom...ch2.htm#toc015

As for the code you provided. The RETURN statement only applies to a FUNCTION, not a PROCEDURE, so remove that. An OUT parameter will simply be filled with the value that the parameter held at the end of the proc's execution.

...
x := 2;
Square(x); -- The next few lines are not part of this PL/SQL
---------------- Block. I'm just showing the proc decl. for ref.
---------------> CREATE OR REPLACE PROCEDURE SQUARE
--------------->( p_value IN OUT NUMBER )
--------------->AS
--------------->BEGIN
--------------->p_value := p_value * p_value;
--------------->END;
DBMS_OUTPUT.PUT_LINE(x)
...

OUTPUT:
----------
4

This should answer the 'but I can't realize where the value will go' question


If I am completely off-base with both these issues, then please provide more info. Since we do not have the tables and cannot compile the proc, lines such as 'As you can see, the INSERTs are not allowed' don't really mean much to us :)

- Chris

Hello,

Thanks for your explanation Chris.
And sorry about my English! I'll try to make myself more clear.

The code I've sent was meant to be a FUNCTION, actually! Otherwise, the RETURN will never make sense! You are right, Chris!

And I also read the info in the link... Thanks! But I've already read about that in "ORACLE Library". And that's the reason why I seek your help!!!

The big problem is that I'm using PostgreSQL. In PostgreSQL, you CAN define a function that 1) gets a value from a sequence; 2) inserts a entry in a table using the value obtained in 1); 3) RETURNS that value.

As you can see, all that operations were done inside onde piece of PostgreSQL PL/PGSQL code. My problem is that I could not do that in ORACLE, using PL/SQL.

1) Can I do all that operations in one single stored procedure (or function) in PL/SQL?
2) If this a impossible desire, how do people use to work around this situation?

I would be very grateful if you could help me...

TIA,

Roberto.

Yes, you can do all that in one function:

1) gets a value from a sequence; 2) inserts a entry in a table using the value obtained in 1); 3) RETURNS that value.

I was simply making assumptions about what your particular problem might have been. As I said, if my assumptions were wrong, and I'm guessing they were, then I need more info.

The function looks fine to me:

If you simply have:

...
val := addNode (...)
...

you should not have a problem. Again, are you calling this function inside a SELECT statement, because that is not allowed.

If this is not the case, then I am at a loss. In order for us to help, you will then need to *really* elaborate on what is happening. How are you calling the function, precisely? What line do you get the error on? What happens if you remove all the code except the SELECT and INSERT? Any and every piece of information that might possibly shed some light would be appreciated.

However, just as a point on style. Functions should only be used when the entire purpose is to calculate a value. If you are simply returning values as a matter of convenience, and not the primary focus of the code, then a procedure should be used. Since the main purpose of this proc is to insert some records, then it really should be a proc and not a function, IMHO.

- Chris

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

Excellent. Now we are getting somewhere. In the SQL*PLUS example, you are calling this function inside a SELECT statement, which is, as I said, not allowed. You cannot call a function that alters the database inside a SELECT statement, hence the error.

As for the Java issue, you are also calling your function from inside a SELECT statement. Again, you cannot do this. Period. You need to find out how to call a procedure from Java, without a surrounding SELECT statement. Unfortunately, I do not know Java.

************************************
*** ANY JAVA CODERS OUT THERE????????
************************************

My best guess would be:

CREATE OR REPLACE PROCEDURE
addNews
(
l_headline IN VARCHAR2,
l_dt IN DATE ,
l_newid OUT NUMBER
)
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 );

END;


PreparedStatement l_prepStat = new PreparedStatement( "Execute addNews( ?, ?, ? ) " );
l_prepStat.setString( 1, "This is a headline" );
l_prepStat.setDate ( 2, l_somedate );
l_prepStat.setInt ( 3, l_newID );
ResultSet l_rs = con.executeQuery( l_prepStat, l_prepStat.toString() );

However, I would guess that con.executeQuery returns a result set, which you will not have. There is more likely a command called con.executeStatement or something similar that will simply execute your statement and not expect a result set. You must accept the fact that you *cannot* call this function inside a SELECT. Therefore, it does not need to be a function. It should be a proc. There *has* to be a way for java to be able to call a proc. There may be some special coding necessary to handle the OUT parameter (another param in l_prepStat.setInt or maybe a l_prepStat.setIntOut), but this is the direction you should investigate. Finally, depending on how the call is implemented in Java, you may or may not need the EXECUTE in front of AddNews().


************************************
AGAIN, ANY HELP FROM A JAVA CODERS OUT THERE WOULD BE APPRECIATED.
************************************

Hope this helps,

- Chris

Chris,

You are really right everything! And even though you don't know Java, you were able to guess a lot of things!

I will try to do exactly as you said:

1) Use a PROCEDURE instead of a FUNCTION;
2) Find a way to see if there is Java function to get out the returning parameter.

THANKS A LOT! I will let you know if I get it.

Roberto.