SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SQL Execute transformation

Reply
Contributor
Posts: 21

SQL Execute transformation

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;

Super User
Posts: 3,254

Re: SQL Execute transformation

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;

Contributor
Posts: 21

Re: SQL Execute transformation

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.

Contributor
Posts: 21

Re: SQL Execute transformation

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?

Trusted Advisor
Posts: 3,214

Re: SQL Execute transformation

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 --<-----
Contributor
Posts: 21

Re: SQL Execute transformation

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.

Ask a Question
Discussion stats
  • 5 replies
  • 944 views
  • 0 likes
  • 3 in conversation