BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mohassan99
Obsidian | Level 7

 

 

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

7 REPLIES 7
Patrick
Opal | Level 21

@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 

mohassan99
Obsidian | Level 7
The link seemed to be broken. I'm fairly certain the issue is that referenced below. Its at least one of them. Thank you.
ChrisNZ
Tourmaline | Level 20

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;

 

 

 

.

mohassan99
Obsidian | Level 7

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;



 

ChrisNZ
Tourmaline | Level 20

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?

mohassan99
Obsidian | Level 7
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.
ChrisNZ
Tourmaline | Level 20

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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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