BookmarkSubscribeRSS Feed
etl_tool
Calcite | Level 5

Hi,

I'm trying to delete rows using SQL execute transformation. The data is in oracle. I'm getting ERROR: ORACLE execute error: ORA-00933: SQL command not properly ended.

proc sql;

      connect to ORACLE

      (

          PATH=ORCL_DEV AUTHDOMAIN="Orcl_OracleAuth"

      );

      execute

      (

        delete from OrCLDEV.rgov.TABLE_OBLG a where a.id in

(select b.uniq_id from OrCLDEV.rgov.TABLE_YHKL b where b.id=1);

      ) by ORACLE;

disconnect from ORACLE;

quit;

5 REPLIES 5
SASKiwi
PROC Star

Try removing the semicolon inside the EXECUTE:

proc sql;

      connect to ORACLE

      (

          PATH=ORCL_DEV AUTHDOMAIN="Orcl_OracleAuth"

      );

      execute

      (

        delete from OrCLDEV.rgov.TABLE_OBLG a where a.id in

(select b.uniq_id from OrCLDEV.rgov.TABLE_YHKL b where b.id=1)

      ) by ORACLE;

disconnect from ORACLE;

quit;

etl_tool
Calcite | Level 5

Thanks SASWiki. Here's the same error I got after removing the semicolon

All I want to do is delete from table where id=123

ERROR: ORACLE execute error: ORA-00933: SQL command not properly ended.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

464        disconnect from ORACLE;

NOTE: Statement not executed due to NOEXEC option.

etl_tool
Calcite | Level 5

I used delete transformation to SQL delete transformation the records. But it is not possible to join a SAS Dataset and Oracle tablle using that transformation..Is there a way to join a SAS Dataset and oracle table and delete records based on the result set?

jakarman
Barite | Level 11

use the sastrace option to see what is happening with the SQL translation.

You cannot use explicit SQL (execute statements) and SAS tables inside of that. Oracel doesn't know anything of the existence of sas. Upload your SAS table (temp area) to Oracle and use explicit Sql with that one.

---->-- ja karman --<-----
etl_tool
Calcite | Level 5

Thanks, i guess I'll try to do an insert and update. But i have another question regarding that which I will post as another thread. It's regarding oracle sequence.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2416 views
  • 0 likes
  • 3 in conversation