Error PATH= is converting into XXXX

[Micheal_S]

Hi,

I am executing one SAS DI Job with user written code. I am using one oracle library in which I am providing User=, Password= and Path=, this job is failing because of error saying

ERROR: Invalid option name =.

and program automatically converting PATH= into XXXX= and that's the reason option is invalid.

I don't know why it is happening automatically, other similar programs are executing well.

Thanks,

Mike

[ChrisHemedinger]

Moving this to where the SAS Data Integration Studio experts can see it better.

Chris

[CTorres]

Please provide the libname statement you are using.

Make sure you are using the engine ORACLE:

LIBNAME oralib ORACLE  PATH=path  SCHEMA=schema  USER=user  PASSWORD="password" ;

[Micheal_S]

Hi,

I am using the below libname

libname abc Oracle SCHEMA=caps PATH=capdb USER=ca123 PASSWORD="abcdef12";

In proc sql the below connect statement is mentioned :-

connect to oracle (user=&_pUser password=&_PPwd path=&_pPRDB);

but log is showing the error as below : converting PATH to XXXX somehow

connect to oracle (user=&_pUser password=&_PPwd XXXX=&_pPRDB);

Thanks,

Mike

[ChrisHemedinger]

Things to try:

A period to terminate the PPwd macro var:

connect to oracle (user=&_pUser. password=&_PPwd. path=&_pPRDB.);

Rearrange the statement:

connect to oracle (user=&_pUser  path=&_pPRDB password=&_PPwd.);

Just trying to outsmart the parser...

Chris

[Patrick · Accepted solution]

To try first thing first: Use quotes for your parameters. I believe double quotes should work even though the docu shows only single quotes (else:quote the quotes by doubling them).

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

connect to oracle (user=''&_pUser'' password=''&_PPwd'' path=''&_pPRDB'');

[LinusH]

Using user written code is NOT best practice, one of the major benefits from using DI Studio and metadata gets lost.

So, register your Oracle library in metadata, and use the metadata way to assign it, i.e. authdomain etc.

[JBailey]

"In proc sql the below connect statement is mentioned :-

connect to oracle (user=&_pUser password=&_PPwd path=&_pPRDB);

but log is showing the error as below : converting PATH to XXXX somehow

connect to oracle (user=&_pUser password=&_PPwd XXXX=&_pPRDB);"

Any chance that it is parsing &_PPwd and thinking Pwd is actually an option? If it did that it might think path is the password. I don't have SAS at the moment so I can't try this out.

[TomKari]

Are you actually using commas in your statement? If you are, take them out.
Tom

[jakarman]

The behavior of XXX of the PATH statement seems to be explained by SAS as seeing that as the password.  Using those Quotes as Patrick indicated is better and avoid those issues.

You coded the path statement as a macro-var pointing to a TNSName entry. It is more direct to code the TNSNAMES line content as the value of your Path. You are needing those quotes " "  around then.

@Linus, using Authdomain? mwah that is the old topic/discussion again.  

SAS build something that looks technical nice, the problem however is that is fails to align with common version and releasemanagement requirements (parallel development , DTAP etc.).

Having authdomains you should think to align those in securitydomains. That naming is correct equal meaning, than you the naming of authdomains as technical tools (eg oracle) being used that is causing problems. Suppose there are several Oracle databases and they are using the same account for a different goal, who is the owner of that account? Another is ease of auditing/validating the correct users is used for the external connection. How should that be done?  Than we have the process of creating/deleting authdomains who is doing that
I can imagine the easy bypass is coding it by hand. Yup the opposite achievement of the goal not having those in source code.  

[LinusH]

, don't know really what you are getting at. Authdomain is merely a technique to automatically login to whatever domain (DBMS in this case) using the current metauser as a base. How you define accounts, groups etc it's a totally different story. My point was to use metadata to make your code (including user/psw) dynamic, not hard coded.

[Micheal_S]

Hi Linus,

I am working on a migration activity, under which I am migrating DI jobs from 9.2 to 9.4 and not suppose to make changes in job legally, if I need to change anything then I have to use change management, that the reason its working on hard coded stuff instead of authdomain and db library creation.

Thanks,

Mike

[Micheal_S]

This thing finally worked, when I used double inverted commas for macro variables but its still a mystery for me, all other jobs are using the same db and accepting macro values without double inverted commas.

Thanks Patrick and everyone for helping me out.

Mike

[jakarman]

Jbailey, you are right it is strange as seeing the log having those XXXX and the macro-vars not being expanded.

When they would have been defined they should show their expanded values. It seems the macro-var not being resolved results in the next string (that path) being set as password.

It explains also why those Quotes are helping to solve that and other jobs not needing this. The other jobs have that macrovars being resolved.

Running the job should give (using the quotes an oracle access problem issue.

Linus what I am getting at is that there is difference between: a/ Using a coding option because it exists   b/ using a coding option because it fulfills the needed requirements.

I know autdomain exist but are saying it doesn't fulfill automically the requirements.  Which requirements?

Well not having the user/password is a checkpoint for decent coding habits but it is not the requirement. The requirements are normally RBAC Audiablily traceablity as mentioned by regulators. That difference can be a sad misunderstanding and misaligmment.  I already mentioned some of those where it is a fail.

See DI 4.9 (the latest) SAS(R) Data Integration Studio 4.9: User's Guide Hard code user/password as result of generates code. That is a terrible hurting one. 

It does (did) when there is explit DDL code generated for some external DBMS systems. DDL is normally already a problem as usually only DML is allowed. MDL is too specific as there are also menu SQL dialects.  The auditablilty/traceablity being another issue and the release management needing to run on different accounts/settings with different stages of DTAP. Aside that with parallel development/testing there are others to solve. That is of course toe neglect in a single stage (only production) for a limited user approach (2-4).