29-03-2021

Ora 00900

Invalid Sql Statement
Ora-00900 Invalid
Ora-00900 Invalid Sql Statement In Java

ORA-00900: invalid SQL statement; Cause. The statement that you've tried to execute is not a valid SQL statement. The option(s) to resolve this Oracle. Ora-00900: Invalid SQL Statement On EXECUTE IMMEDIATE Call PL/SQL object (Doc ID 2503264.1) Last updated on DECEMBER 19, 2019. Applies to: PL/SQL - Version 11.2.0.4 and later. I add an oracle store procedure to OLE DB Command. This store procedure works at oracle db. However, in SSIS, it shows error:ORA-00900 Error: 0xC0047022, SSIS error.

General

A. When calling to a remote PL/SQL procedure from sqlplus - all works fine,
But when calling to the same remote PL/SQL procedure from PL/SQL code, an ORA-00900: invalid SQL statement error is thrown.

B. When calling a PL/SQL directly, all works fine, but when calling same procedure vi db link, it fails with ORA-02064 Distributed operation not supported

Why is that?

ORA-00900 Example

In this example, a DB_LINK named KUKU is created to the remote database.

CREATE DATABASE LINK KUKU CONNECT TO REMOTE_USER IDENTIFIED BY REMOTE_PASSWORD USING 'REMOTE_HOST_NAME';

In the remote database, a getDate Procedure in Package PKG_TEST should be activated.

In sqlplus
From sqlplus, any of these versions work:
EXEC PKG_TEST.getDate@KUKU;
EXEC REMOTE_USER.PKG_TEST.getDate@KUKU;

When creating a Synonym, it can be also used, to simplify code.

CREATE SYNONYM REMOTE_PKG FOR REMOTE_USER.PKG_TEST@KUKU

SELECT * FROM USER_SYNONYMS WHERE synonym_name = 'KUKU'

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------- ---------------- --------------- ---------------
REMOTE_PKG REMOTE_USER

Invalid Sql Statement

PKG_TESTKUKU
So in sqlplus, it would be:
EXEC REMOTE_PKG.getDate;

In PL/SQL
When activating the same code in PL/SQL it MUSTbe inside a BEGIN END block.

Any other syntax, such as EXECUTE IMMEDIATE <sql_string> would fail with 'ORA-00900: invalid SQL statement'.

This is the correct code:

BEGIN
PKG_TEST.getDate;
EXCEPTION
WHEN OTHERS THEN
WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
RAISE;
END;

ORA-02064 Example

In this example, same DB_LINK named KUKU is created to the remote database.
The procedure now is a DML Procedre, performing an update, and a commit.

The remote procedure has these API:

PKG_TEST.setDate(v_date IN DATE,
v_result OUT NUMBER);

When calling the remote procedure in PL/SQL, it would be:

DECLARE
v_date DATE;
v_result NUMBER;
BEGIN
v_date := SYSDATE;
PKG_TEST.setDate(v_date,v_result);
EXCEPTION
WHEN OTHERS THEN

WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
RAISE;

END;

The returned error is:
ORA-02064: distributed operation not supported
ORA-06512: at 'REMOTE_USER.PKG_TEST', line 491
Per Oracle documentation,
Error Cause:
One of the following unsupported operations was attempted:

1. array execute of a remote update with a subquery that references a dblink, or

2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or

3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.

Action:
Simplify remote update statement.

Ora-00900 Invalid

Per Oracle Metalink CALLING REMOTE PACKAGE RECEIVES ORA-2064 (Doc ID 1026597.6):

In the documentation for ORA-2064, it states that one of the disallowed actions is 'A commit is issued in a coordinated session from an RPC with OUT parameters.'

It happens that the return value of a function call counts as an OUT parameter for purposes of this rule.

As a workaround for some cases Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package.'

So, a work around this problem would be to change the remote Procedure to be Autonomous Transaction.

PKG_TEST.setDate(v_date IN DATE,
v_result OUT NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;

Have you tried to run a query and got the “ORA-00979: not a group by expression” error? Learn what it is and how to resolve the error in this article.

ORA-00979: not a group by expression

The ORA-00979 error happens when you have at least one column in your SELECT clause that is not in your GROUP BY expression when you are using an aggregate function. Common aggregate functions include SUM, AVG, MIN, MAX, and COUNT. Any column or expression in your SELECT clause must also be listed in the GROUP BY clause.

Here’s an example of a query that will generate the error:

Result:

ORA-00979: not a GROUP BY expression

Why Do I Get The ORA-00979 Error?

This error happens because you’re using an aggregate function, and there is at least one column in the SELECT clause that is not in the GROUP BY clause.

Using the example query above:

Because I use an aggregate function (COUNT), I need to define all of the columns in a GROUP BY clause that are in the SELECT clause.

In this example, I have specified the first_name and last_name column in the SELECT clause, but the last_name column is not in the GROUP BY clause.

Even if I have some fields in the GROUP BY clause, if I don’t specify all of the fields from the SELECT clause, I’ll still get an error.

For example:

Result:

ORA-00979: not a GROUP BY expression

As you can see, this will still give me an error.

Why does Oracle give an error?

Because, if you don’t have a GROUP BY but you want to SELECT the column, Oracle doesn’t know what value to show when using this aggregate function. Should it show the first value? The last value? A random value?

How To Resolve the ORA-00979 Error

Ora-00900 Invalid Sql Statement In Java

To resolve the ORA-00979: not a group by expression error, simply ensure that all of the GROUP BY columns match the SELECT clause.

You can do this by adding columns to the GROUP BY.

So, using the example above:

Result:

FIRST_NAME	LAST_NAME	*COUNT()**
John	Smith	2
Mark	Anderson	1
Michael	Condor	1
Brendan	Jefferson	1
Peter	Stark	1
Sally	Lincoln	1
Michelle	Brumby	1
Amy	Ford	1
Rose	Minson	1
Tina	Mitchell	1

Or, using the second example:

Result:

FIRST_NAME	LAST_NAME	ADDRESS_STATE	*COUNT()**
John	Smith	California	1
Mark	Anderson	Texas	1
Michael	Condor	Florida	1
Brendan	Jefferson	Florida	1
Peter	Stark	Illinois	1
Sally	Lincoln	California	1
Michelle	Brumby	Texas	1
Amy	Ford	Texas	1
Rose	Minson	California	1
Tina	Mitchell	Illinois	1
John	Smith	Texas	1

The columns don’t need to be in the same order to correct the error. They just need to be present.

One thing to remember with a GROUP BY clause, is if you give your columns aliases, you need to specify the original column or expression.

So, if you have a query like this:

Result:

ORA-00904: “FULL_NAME”: invalid identifier

You get an error, because you can’t refer to a column alias within the GROUP BY clause. You’ll need to use a query like this:

FULL_NAME	*COUNT()**
John Smith	2
Mark Anderson	1
Michael Condor	1
Brendan Jefferson	1
Peter Stark	1
Sally Lincoln	1
Michelle Brumby	1
Amy Ford	1
Rose Minson	1
Tina Mitchell	1

Conclusion

So, in conclusion, the ORA-00979: not a group by expression error happens because the columns in the SELECT clause don’t match the columns in the GROUP BY clause. To resolve the error, make sure the columns match.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!