topic Re: Insert into different database from oracle server through SQL pass through in New SAS User

Insert into different database from oracle server through SQL pass through

R_Chung — Mon, 04 Jul 2022 09:41:35 GMT

Hello everyone,

I am trying to insert something to different oracle database from my SAS dataset.

As usual, the syntax of selecting table from other database is

select * from otherowner.tablename;

Let say I have a SAS dataset aaa.

proc sql noprint;    
		connect to oracle 
			(user=xxx password=xxx path=xxx);    
			execute (insert into otherowner.tablename select * from work.aaa) by oracle;
			disconnect from oracle; 
quit;

It seems that SAS seek "otherowner" as libname to operate the above statement.

Meanwhile, I have tried to add preserve_comments, i.e,

proc sql noprint; 
        connect to oracle (user=xxx password=xxx path=xxx preserve_comments=YES); 
        execute (insert into /*otherowner.tablename */ select * from work.aaa) by oracle; 
        disconnect from oracle; 
quit;

However, this is also not working.

Could any expert advise how to implement what I want to execute. Many thanks.

Re: Insert into different database from oracle server through SQL pass through

Tom — Mon, 04 Jul 2022 15:13:29 GMT

You cannot use code pushed to Oracle to access data in SAS datasets (the oracle code will have no idea what WORK.SAS is).

You need to run SAS code to push SAS datasets into Oracle.

Easiest thing it is make a libref that points to ORACLE.

libname myora oracle ..... ;
proc append base=myora.tablename data=work.sas;
run;

If for some reason you need to insert into a table that is in a different Oracle schema than the one you connect to the libref to try using the SCHEMA= dataset option.

libname myora oracle ..... ;
proc append base=myora.tablename(schema='otherowner') data=work.sas;
run;

Re: Insert into different database from oracle server through SQL pass through

R_Chung — Tue, 05 Jul 2022 02:09:37 GMT

Hi Tom,

For option one, I usually using this for inserting data into table.

However, if I need to insert into other database I need to especially specify other username in my libname statement.

libname myora oracle ..... user=otheruser pw="";other user password="";

This may lead to a concern that everyone will know the password.

I have tried the second option but there is still an error.

ERROR: The ORACLE table <table_name> has been opened for OUTPUT. This table already exists, 
or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

I have read the support post: https://support.sas.com/kb/32/461.html

But they suggest to omit the schema=

Could you advise again?

I think the second option is the closest solution for my case.

Thank you.

Re: Insert into different database from oracle server through SQL pass through

ricric — Wed, 06 Jul 2022 06:03:44 GMT

Actually, specifying schema is already enough for your case.

If adding schema in your proc append is not working.

Try adding it in your library statement.

libname myora oracle <your connection string> schema=otherowner ;

See if this works.

Re: Insert into different database from oracle server through SQL pass through

Patrick — Sat, 09 Jul 2022 07:55:23 GMT

@R_Chung As for your concern with credential: Look-up what authentication domains could do for you.