ORA-00900: invalid SQL statement

Toad for Oracle
1

Hello,
Is it possible anonymous block?

DECLARE
CURSOR CUR IS
SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES;
BEGIN
FOR I IN CUR LOOP
EXECUTE IMMEDIATE I.RS;
END LOOP;
END;

Thanks for your advice

2

If you're asking if Toad/Oracle can execute anonymous blocks, then answer is yes.

If you're asking advice on coding, this is the wrong forum. That said, I don't think you're code above will work, for a number of reasons, even though I'm not a PL/SQL expert.

Here's a link with examples to get you started... there's tons of other stuff on the net you can dig up:
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm

Hope this helps.

3

The reason that your pl/sql block fails is because your cursor loop is selecting some values out of the HR.EMPLOYEES table, and then trying to run those values as a sql statement.

I'm not really sure what you intended for that code to do, but either of these runs without error:

DECLARE
  CURSOR CUR IS
    SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES;
BEGIN
  FOR I IN CUR LOOP
    dbms_output.put_line(I.RS);
  END LOOP;
END;


BEGIN
  EXECUTE IMMEDIATE 'SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES';
END;
1 Like
4

execute immediate will execute any valid statement (SQL or PL/SQL).
Error message is your best friend, in term of what might go wrong.
Try to run the output of the cursor in any application (Toad, SQL-Developer, sqlplus...) and the error message will be the same.
The statement, that the code is running, is something like "SMITH 11 17-DEC-80" which is not a SQL statement. 00900 is the only response expected.

5

New to the forum and not sure if this is how to post but here it goes.
A user is getting the ORA-00900 invalid SQL statement in Toad for Oracle 12.9 64 bit. This is a recent install. The code is as follows. Others that run this same code with the same version of Toad for Oracle do not get the error.

select
INBND_ACCT_NBR ,
CUSTMR_NBR ,
CUSTMR_BILL_GRP_ID ,
SRC_CD ,
AMT_PD ,
RECRD_INSERT_DT ,
SRC_NM ,
FILE_DT ,
EFF_DT ,
TRANSACT_ID ,
FILE_POOL_ID ,
COUNTRY_CD ,
RETURN_CD ,
CHK_NBR ,
PMT_TYPE ,
ACCT_COUNTRY_CD ,
SYSTEM_OF_RECRD ,
PARTIAL_ACCT_NBR ,
TRANSACT_ACCT_NBR ,
ACCT_NBR ,
SENT_STATUS,
SENT_FILE_DT

FROM MART_FINC. PMT_GW_DATA_RPT
--WHERE --FILE_DT between '20190712' and '20190715'
WHERE FILE_DT = '20190108'
AND src_cd like '%LBCP%' ---UWQC'---CANP', 'CASP', 'CANR', 'CASR'2
--WHERE PARTIAL_ACCT_NBR like '%112005549670%'
--and AMT_PD = '301.10'
AND COUNTRY_CD ='US'
AND FILE_POOL_ID LIKE '%0256'

Please help!

6

I suspect it's because you have a blank line and no semicolon. Possibly.

I know Sql*plus does not like blanks at all, Toad doesn't mind, but:

If you have two statements, and no semicolon, then they need to be separated by a blank line.

I think what's happening here is that your trying to F9 (execute) the second part of the SQL - where is the caret when the error occurs - and the Toad parser thinks you have two statements, the second of which starts at FROM MART_FINC which is indeed invalid.

Sorry if I sound vague, I'm not near a database or my Toad, and I always use semicolons. :wink:

HTH

Cheers,
Norm. [TeamT]

7

PS. This:

WHERE FILE_DT = '20190108'

should be:

WHERE FILE_DT = to_date('20190108', 'yyyymmdd')

If FILE_DT is a DATE data type. If there is an index on the column, using a string literal as in your example, will prevent the index being used.

If, on the other hand, it is a VARCHAR2 column, I'm afraid you need to go and have a word with the data designer and convince him/her to use the correct data types. :wink:

Cheers,
Norm. [TeamT]