ORA-24333

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

ORA-24333

 

 

I'm trying to run the following in a SQL Pass through and get ORA-24333.  How do I accomplish this with a pass through?

 

I ended up doing the delete in SQL Developer, using the delete clause below, but if I run in again I get 24333.  Nevertheless, I commented it out and ran the insert clause and got 24333 again. Is it because I can't reference D.?  I thought I should be able to.

 

 

PROC SQL;
	connect to ORACLE as hoa (user="&USR[HOA]" password="&PWD" PATH="@HOAP");

	%put &sqlxmsg;

	create table HOA.OBSER_QA AS
	select *
	   from connection to hoa
		(
		DELETE FROM HOA.OBSER_QA;

		INSERT INTO HOA.OBSER_QA
			(SELECT *
					,TODAY() AS LOAD_DATE
					,&_CLIENTUSERID. AS LOAD_BY
			 FROM D.SUMMARY&YEARMTH._0762;
		 	)
		);

	%put &sqlxmsg;

	disconnect from hoa;
QUIT;

 

 

 

 

D.SUMMARY&YEARMTH._0762 has 23,624 observations.

 

Sample data attached.

 


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 2,516

Re: ORA-24333

Posted in reply to mohassan99

You show us a data step and an SQL error?

Run this before the code

options sastrace=',,,db' sastraceloc=saslog ;

and show us the full log please

View solution in original post


All Replies
Respected Advisor
Posts: 4,797

Re: ORA-24333

[ Edited ]
Posted in reply to mohassan99

@mohassan99

First step is always to copy/paste such error messages into a Google search. Below what I found:

http://support.sas.com/kb/15/432.html 

Occasional Contributor
Posts: 14

Re: ORA-24333

The link seemed to be broken. I'm fairly certain the issue is that referenced below. Its at least one of them. Thank you.
Super User
Posts: 2,516

Re: ORA-24333

[ Edited ]
Posted in reply to mohassan99

1. You seem to be using SAS syntax in a pass-thru query.

 

Anything between the parentheses must be Oracle syntax and pointing to Oracle data.

 

from connection to hoa	( );

 

2. You also seem to be pointing to a SAS table? That won't work either in a pass-thru query.

 

3. What are you hoping to select  in select *  from connection to hoa when there is no select statement sent to Oracle?

 

4. I reckon you don't want pass-thru here. Just run something like:

 

proc sql; 

  delete from ORALIB.OBSER_QA;

quit;

proc append data=D.SUMMARY&YEARMTH._0762 base=ORALIB.OBSER_QA;

run;

 

 

 

.

Occasional Contributor
Posts: 14

Re: ORA-24333

[ Edited ]

Thank you!


1. I guess I need to find the SAS syntax, that wasn't my intention. I assumed today() was an oracle function too.
2. Yes, I just learned that's at issue. I'll come back to that.
3. I'm not sure I understand the question.
4. That might work. I should try it.

Regarding 2& 3, the reason I switched to a pass-through was because 1. management has a desire to move away from SAS in our department. We have limited internal SAS support and no dedicated SAS Admin at our company 2. I was getting this error:

"ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties.

Currently, even though an error occurs, SAS still fetches both tables and processes the join."

Source:
http://support.sas.com/kb/40/132.html

The original code causing this error was:

PROC DELETE DATA=HOA.OBSER_QA;
RUN;

DATA HOA.OBSER_QA;
     SET D.SUMMARY&YEARMTH._0762;
     LOAD_DATE=TODAY();
     FORMAT LOAD_DATE MMDDYY10.;
     LENGTH LOAD_BY $20;
     LOAD_BY="&_CLIENTUSERID.";
RUN;



 

Super User
Posts: 2,516

Re: ORA-24333

[ Edited ]
Posted in reply to mohassan99

You seem to have limited knowledge of Oracle as well. You may want to keep the working code that you had.

Why did you open a duplicate question here?

Occasional Contributor
Posts: 14

Re: ORA-24333

I didn't think that was a duplicate. Yes, I've been programming in SAS for years, but my Oracle experience is limited to a couple pass-throughs a while ago and a course about 10 years ago.
Solution
3 weeks ago
Super User
Posts: 2,516

Re: ORA-24333

Posted in reply to mohassan99

You show us a data step and an SQL error?

Run this before the code

options sastrace=',,,db' sastraceloc=saslog ;

and show us the full log please

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 152 views
  • 2 likes
  • 3 in conversation