How to assign value from table to environment variable?

userasif · Post by **userasif** » Wed Jan 17, 2007 2:42 am

Hi to all,

I want to assign single value from a table (using SQL query) to my environment variable... How can i do it?

Thanks & Regards,

ITsME

ray.wurlod · Post by **ray.wurlod** » Wed Jan 17, 2007 3:09 am

In a job sequence run a small job to return the value. Write it to a file or to the job's user status area. Follow this with an Execute Command activity to set the environment variable (and, perhaps, to export the same).

userasif · Post by **userasif** » Wed Jan 17, 2007 4:59 am

Thanx Ray for your prompt,

But I am not Premium Member..so could not read your reply.

Regards

rleishman · Post by **rleishman** » Wed Jan 17, 2007 5:34 am

I'm not a Premium Member either, so I can safely avoid plagiarism (at least directly - I'm sure it was Ray from whom I originally learned the same trick in the days before Premium Membership).

Create a Job Sequence with two linked jobs: Job1 and Job2.

Job1 has 3 stages: an OCI source, a Transformer, and an OCI target (you can use a Sequential File target, but with OCI you can make the job multi-instance).
In the source OCI, write your SQL to return the variable you need.
In the transformer, set the Constraint on the output link to @FALSE so that nothing is written to the target OCI - you can write back to any table you please since no row will go there - I use DUAL.
Also in the transformer, create a Stage Variable that calls a routine SetUserStatus that you must write - search this form for syntax. Pass the value SELECTed from the OCI to SetUserStatus

Add a parameter to Job 2. When you call Job2 in the Job Sequence, set the value of the parameter to the $UserStatus of Job1 - you should find it in the popup expression builder.

userasif · Post by **userasif** » Wed Jan 17, 2007 5:44 am

Thanx Leishman,

I did as what you suggested. But my question is how i would assign this Parameter value to my Environment Variable????

Thanks & Regards.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 17, 2007 3:35 pm

Premium membership is a mechanism to help to pay for the bandwidth to keep this valuable site running. At only a few cents per day it's worth it.
The answer is in my earlier post. A year's premium membership would cost less than engaging my professional services for an hour to answer that question, and you get all the other premium answers as a bonus.

rleishman · Post by **rleishman** » Wed Jan 17, 2007 4:28 pm

Once again, I'll have a go at a free answer...

If the second job is actually a Job Sequence, and the parameter you assign with $UserStatus in the second job is an Environment Variable (declared in the Enviroment section in Administrator), then every job subordinate to Job2 will automatically inherit the value, providing you do two things:

- Include the Environment Variable as a parameter in all of those subordinate jobs. eg. If you have a call path several levels deep like this: Job2->Job3->Job4->Job5; then Job5 will inherit the original value set in Job2 ONLY if Job3 and Job4 also include the Environment Variable.

- The Default Value of the Environment Variable in every job (set up in the Parameter page) must be $ENV. When you call jobs set up this way, you don't have to manually enter any parameter values into the Activity - it just uses the default.

Since we are talking about Environment Variables, the value is also available in all subordinate external calls to the OS.

As a final note, I agree with Ray. The cost of Premium Membership is well worth it. Ray's answer is almost certainly more concise, correct, and complete than mine, and it was available 20 minutes after you asked for it. In terms of value for money, nothing else compares.
However, to be fair to @userasif, perhaps he doesn't want or need extraordinary service - my answers are nothing if not ordinary

userasif · Post by **userasif** » Thu Jan 18, 2007 12:07 am

Thanks to all,

With the help of you guys, I have done this. :D

Regards.