topic Re: ORA-24333 in SAS Programming

ORA-24333

mohassan99 — Tue, 24 Jul 2018 00:12:23 GMT

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.

Re: ORA-24333

Patrick — Tue, 24 Jul 2018 00:27:10 GMT

@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

Re: ORA-24333

ChrisNZ — Tue, 24 Jul 2018 03:54:33 GMT

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;

Re: ORA-24333

mohassan99 — Mon, 30 Jul 2018 18:35:53 GMT

The link seemed to be broken. I'm fairly certain the issue is that referenced below. Its at least one of them. Thank you.

Re: ORA-24333

mohassan99 — Mon, 30 Jul 2018 18:44:51 GMT

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;

Re: ORA-24333

ChrisNZ — Mon, 30 Jul 2018 22:53:42 GMT

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?

Re: ORA-24333

ChrisNZ — Mon, 30 Jul 2018 22:51:12 GMT

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

Re: ORA-24333

mohassan99 — Tue, 31 Jul 2018 19:55:24 GMT

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.