Libname syntax from connection string

[OscarBoots2]

HI,

Can anyone show me how how to convert my connection string into a Libname statement?

As you can see below, I have the HOST, PORT, SERVICE NAME, USER & PASSWORD.

I want to use these credentials to create a Libname statement & use in a Pass Thru query to Oracle, any suggestions?

libname mylib oracle  ;

connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))
user=xxxxxxx pw=xxxxxxxx);

[Damo]

Hi @OscarBoots2

Did you get a chance to review LIBNAME Statement Specifics for Oracle ?

It shows you can use, for instance:

libname x oracle user=myusr1 pw=mypwd1 
  path="(DESCRIPTION= 
          (ADDRESS_LIST=
            (ADDRESS= (PROTOCOL=TCP)(HOST=pinkfloyd)(PORT=1521))
             )
              (CONNECT_DATA= 
       "  	     (SID=alien)
       "   )
       " )
       " ";

Hope that helps.

Cheers,
Damo

[OscarBoots2]

Thanks Damo,

I've tried to adapt this to my settings, which currently work in other queries, but I get a

libname mylib oracle user=XXXXXX pw=XXXXXX 
path="(DESCRIPTION=
		(ADDRESS = (PROTOCOL = TCP) 
			(HOST = XXXXXXXXXXX.com)(PORT = 1234))
				(CONNECT_DATA = (SERVICE_NAME=XXXXXX)))";

proc sql noprint;

create table TABLE1 as select * from connection to mylib 
( 
select DISTINCT 
ID

FROM DBC.TABLE2

WHERE ROWNUM < 50
)
;QUIT;

The error is below but doesn't make sense as my credentials are correct?

ERROR: The MYLIB engine cannot be found.
ERROR: A Connection to the mylib DBMS is not currently supported, or is not installed at your site.

[ballardw]

---

@OscarBoots2 wrote:

Thanks Damo,

I've tried to adapt this to my settings, which currently work in other queries, but I get a

libname mylib oracle user=XXXXXX pw=XXXXXX 
path="(DESCRIPTION=
		(ADDRESS = (PROTOCOL = TCP) 
			(HOST = XXXXXXXXXXX.com)(PORT = 1234))
				(CONNECT_DATA = (SERVICE_NAME=XXXXXX)))";

proc sql noprint;

create table TABLE1 as select * from connection to mylib 
( 
select DISTINCT 
ID

FROM DBC.TABLE2

WHERE ROWNUM < 50
)
;QUIT;

The error is below but doesn't make sense as my credentials are correct?

ERROR: The MYLIB engine cannot be found.
ERROR: A Connection to the mylib DBMS is not currently supported, or is not installed at your site.

---

Show the code from the LOG with error message.

The error would make me think the actual submitted code used:

libname oracle mylib ...

The engine, such as oracle, when used should follow the libref

[OscarBoots2]

Thanks ballardw,

The code submitted is in the order I posted.

such as;

libname mylib oracle user=XXXXXX pw=XXXXXX 
path="(DESCRIPTION=
		(ADDRESS = (PROTOCOL = TCP) 
			(HOST = XXXXXXXXXXX.com)(PORT = 1234))
				(CONNECT_DATA = (SERVICE_NAME=XXXXXX)))";

Any ideas as what may be wrong otherwise?

Thanks

[Damo]

Hi @OscarBoots2

If you want to reuse a previously defined library, I think you have to use "connect using libref".

In your case, it would look like this.

Libname mylib oracle ...;

PROC SQL;
  CONNECT USING mylib;
  ...;
  DISCONNECT FROM MYLIB;
QUIT;

This is new with SAS 9.3.

Hope that helps.

Cheers,
Damo

[OscarBoots2]

Hi Damo,

It's strange but the word CONNECT is coloured Orange?

If I remove the ';' after 'proc sql noprint' it goes back to black as it should be?

Any ideas?

[Damo]

Hi @OscarBoots2

Can you post the entire code and its log ?

Did you try to apply an existing sample code to your case, just to see the result ?

Cheers,
Damo

[SASKiwi]

@Damo - I tried this in EG 7.1 and I can reproduce the orange colour error. It appears EG doesn't colour code CONNECT USING correctly. The code runs fine though.