DSXchange

Hi,

I'm using Stored Procedure stage to call Oracle SP. In the data connection,
I'm able to pass the parameter for Data source and Username. However, I'm unable to pass the parameter for password as it accepts encrypted format.

Can you suggest the way to accept the parameter?

Not really following. Passwords as encrypted parameters can be passed to anything, they are unencrypted during the process. Can you provide more details about your issue, please?

I'm not suppose to hardcode the password inside the STP stage. It should be passed from sequence job. So I used the corresponding parameter name in the place of password, which is not getting accepted by this stage.

You'll need to let us know what "not getting accepted" means. If you are getting errors, please post them.

Error: ORA-01017: invalid username/password; logon denied
is the error message I received

It could be as simple as a syntax error.

How are you typing in the Data source and user name? Do you just name the parameter -- parmDataSource -- or are you using the escape # -- #parmDataSource#?

I know that in other stages, you must use #, and in other stages you must not. Usually the difference is if you have an edit button for the text box.

Hi Frank,
Im using like below
#parmDataSource#
#parmUser#
#parmPassword#

I believe it's that the password is encrypted, and not being converted back to text by the runtime engine. I defer to others who use the SP stage to comment further, because I've hit the extent of my experience with this.

Good luck.

this could be TOTALLY unrelated and off the wall, but let me describe a recent issue I was having...

I was having my password constantly rejected for a teradata system. My password was rather lengthy and about 50% special characters (standard USA keyboard punctuation, math, and similar stuff like !@#$%^&*() etc). What seems to have been happening is that I stumbled across some password that, when encrypted, generates datastage control characters, maybe #junk#, or something, I do not know exactly what, but it just would not work.

I changed my password on that database and the exact same code and such worked perfectly since.

So, all that to say, maybe change your password, if you think you are doing everything correctly?

I've experienced that as well. The software restricts a lot of special characters. There are documented here:

Naming restrictions for user IDs and passwords

NICE, I looked but did not find this list.
Looks like the one I was using had at least 5 offending characters in it..

Also seems like login to the tool does not matter what you have, but inside jobs it will cause troubles.

We have also worked cases where the password that contains prohibited special characters works just fine when you click "View Data" on a Connector stage but the same password aborts the same job at run time. The password-handling code behind the "View Data" service provided by the services tier is different code than the run-time code in the engine tier. I have not worked with the Stored Procedure stage much but would, perhaps riskily, assume it would behave in a similar way.

Just wanted to say this was a great eye opener to read, not current issues with this but perhaps just avoided any in the future. Thanks everyone!