Access Oracle Database Table from Server Routine

[gnan_ram]

Hi All,
I need to write a routine to accomplish the below task,

1. Get value from oracle table which is of single row table and store this value in stage variable.

I had tried to implement the above scenario using below methods,

1. Read from oracle table and write into a sequential file, then use execute command activity in the sequence to read the file and assign the value to a variable in User Variable Activity, and pass this value to the next job where my actual code, expecting this value, resides.

2. Read from oracle table and write it into a hash file and use a routine to read in the stage variable expression. by this way i can eliminate the execute command activity and user variable activity usage in the sequence. But here also i am using 2 jobs and 1 routine.

But i wanted to do this exercise with one job and one routine. It needs a routine which can connect to the Oracle Database and get the value.
I i have one routine do this task and i can use the same in my stage variable expression to assign value.

Here the reason for this is i have to compare the date value from oracle table against the records from input file for determining the order of incoming year. which we cannot accomplish using hash file.

Please help me in this regard.

[chulett]

Ugh, that's going to be an unwieldy solution, not to mention harder to code and maintain then you can possible imagine. You'd have to go "BCI" and leverage ODBC and then deal with the fact that the supplied ODBC drivers are not licensed for use outside of the GUI. Far better to stick with your first solution but clean it up a little.

Write your value to USERSTATUS rather than a hashed or sequential file. Your next job in the Sequence can then automatically pick up the value from there for a parameter without the need for UserVariable or routines of any kind. Yes, still two jobs but... so what.

Or depending on exactly where you need to use this value in your job, source the value from Oracle and store it in a single record hashed file with a hard-coded key value in the same job that needs to use it. This then becomes a 'regular' lookup and sure, you look the same value up over and over but it's cached after the first hit and again... so what.

Hopefully one of the two will work for you. If not, please be precise about the "why not" and we'll go from there.

ps. For the first solution, search here for "DSSetUserStatus" for the generic routine you'd need to accomplish this. I would do this regardless, even if you don't go down that solution path as it will come in handy in the future for something else and it's good to have that ready for when you do need it.

pps. Welcome. :D

[nikhilanshuman]

In the server routine,do following :

cmd= "sqlplus -s userid/passwd@server Your query"
Call DSExecute("UNIX",cmd,Output,Returnval)

*the variable "Output" contains the output of the query.

Call DSLogInfo("The output from query is ":Output,"Output")
*The above line will print the output of query execution.Now you have to get the value of the particular column.See following.

*Now use field function to get the result line.When oracle returns the result of a query,the lines are separated by filed marker.We call it as @FM.
Also,the column values are separated by spaces.
You need to use Field function to get the exact line(generally the output starts from fourth line).

Line=Field(Output,@FM,4)

now,get the appropriate column value :

col=Field(Line," ",column position)

*Now the variable "col" will contain the actual value of the column.
*Now return the value by the routine :

return(col)


Flow ->

---user variable activity--->Job activity

Calling routine in user varable activity -->
Retcode = RoutineName(Parameters)

* Now the column value will be assigned to variable Retcode in the user *variable activity.You can use it anywhere in your job

Getting return code in the job activity :

uservrableactivityname.retcode

pass this value as a parameter to the job activity.Assign the parameter to the stage variable in your job.

Note : Do not use routine activity to call this routine.Since the value returned by the routine will not be zero,it will be considered as error by datastage while running.

If you find it difficult to implement in routine,you can use similar logic in a shell script.

[chulett]

Or you can do that. Still falls into the "ugh" category for me.

[gnan_ram]

Ugh, that's going to be an unwieldy solution, not to mention harder to code and maintain then you can possible imagine. You'd have to go "BCI" and leverage ODBC and then deal with the fact that the supplied ODBC drivers are not licensed for use outside of the GUI. Far better to stick with your first solution but clean it up a little.

Write your value to USERSTATUS rather than a hashed or sequential file. Your next job in the Sequence can then automatically pick up the value from there for a parameter without the need for UserVariable or routines of any kind. Yes, still two jobs but... so what.

Or depending on exactly where you need to use this value in your job, source the value from Oracle and store it in a single record hashed file with a hard-coded key value in the same job that needs to use it. This then becomes a 'regular' lookup and sure, you look the same value up over and over but it's cached after the first hit and again... so what.

Hopefully one of the two will work for you. If not, please be precise about the "why not" and we'll go from there.

ps. For the first solution, search here for "DSSetUserStatus" for the generic routine you'd need to accomplish this. I would do this regardless, even if you don't go down that solution path as it will come in handy in the future for something else and it's good to have that ready for when you do need it.

pps. Welcome. :D

Hi Craig,
I had tried the second method, which works flawlessly.

Still i have to try the first method, which i am currently working on.

[nikhilanshuman]

If you are using server job then the server routine i mentioned above can be directly called in the server job itself.There is no need of user variable activity/job activity and any sequence.

[gnan_ram]

In the server routine,do following :

cmd= "sqlplus -s userid/passwd@server Your query"
Call DSExecute("UNIX",cmd,Output,Returnval)


*** If you find it difficult to implement in routine,you can use similar logic in a shell script.

Hi Nikhil,
The above code is not working.
It is throwing some error, saying the words after the server are not recognizable.

I know the way to pass the query to sqlplus command in shell script, but here it is not working.
Here is the code which i usually use in shell scripts.

sqlplus -s username/password@DBNAME <<EOF
Select * from dual;
select 'This command executes multiple queries and output in screen' from dual;

EOF

Here the new lines are mandatory, but in routine if try to assign the same command value to cmd variable then it throws the compilation error saying the quote is not closed, as routine is always looking for the closing quote in the same line.

cmd = "sqlplus -s username/password@DBNAME <<EOF
Select * from dual;
select 'This command executes multiple queries and throws output in screen' from dual;
EOF
"

Please advice me if i am wrong.

Thanks
Ramachandran

[chulett]

Error checking command line sqlplus sessions is always problematical if you've never done it before. Even when you get the syntax correct and things work normally, you need to be able to detect and handle the "abby-normal" things that can and will happen.

[nikhilanshuman]

Try this :
write your query in a sql file and execute as below.it will be easier:

*Write query to a temporary sql file.
cmd= "echo select * from dual > query.sql"
Call DSExecute("UNIX",cmd,Output,Retval)

*execute the sql file
cmd="sqlplus -s DSGPS_BATCH/gpsd1@GPSD<query.sql"
Call DSExecute("UNIX",cmd,Output,Returnval)

**Specify proper path along with the file name e.g. /tmp/query.sql according to your directory structure.

[nikhilanshuman]

Also,chulett is correct.You will have to handle the Oracle errors as well otherwise you might get NULL values in the output.

Use the count function and always check if your output contains word like "ORA" and "Error"

cnt=count(Output,"ORA")

cnt1=count(Output,"ERROR')

if cnt >0 or cnt1>0
Call DSLogWarn("The query returned error!!!","Error")
end if

[gnan_ram]

Try this :
write your query in a sql file and execute as below.it will be easier:

*Write query to a temporary sql file.
cmd= "echo select * from dual > query.sql"
Call DSExecute("UNIX",cmd,Output,Retval)

*execute the sql file
cmd="sqlplus -s DSGPS_BATCH/gpsd1@GPSD<query.sql"
Call DSExecute("UNIX",cmd,Output,Returnval)

**Specify proper path along with the file name e.g. /tmp/query.sql according to your directory structure.

Hi Nikhil,
I had tried the above method of writing the sql statement to a file and then execute the sql command from file later using the sqlplus.
At very first i got some error, as the echo statement printed all the directories inside the Project directory into the sql file.
Later i found the reason for it and added single quuotes around the sql statement, which looks like

Code: Select all

 cmd= "echo [b]'select * from dual;'[\b] > /export/home/capriusr/rama/query.sql" 

This worked great!!!

And after i saw your reply i thought of doing the same without using an external file.
All i have to add is newline to the sql statement being passed to sqlplus.
To accmplish that i had modified my routine which was not working earlier

cmd = "sqlplus -s username/password@DBNAME <<EOF
Select * from dual;
select 'This command executes multiple queries and throws output in screen' from dual;
EOF
"[\quote]

TO

cmd = "sqlplus -s username/password@DBNAME <<EOF":char(10):"select * from dual;":char(10):"EOF":char(10) [\quote]

This started working to the expected level.

Thanks a lot for your help.

Here i had figured the Routine code to connect to Oracle Database and execute the query, hurrraaayyy!!!!!javascript:emoticon(':o')

But i am planning to use the second method suggested by Craig in my job as it looks simple and works smoothly. And also the oracle errors will be handled by the internal job itself. More importantly i can have all the stages in a single job itself.

Ofcourse, if we go with the database connect routine we will be having a single job but the routine will be the second object which needs to be present.

I am in course of completing the Database connect Routine code, once it is completed, i will be posting the code here.

Thanks a lot for your help Craig and Nikhil. :D :D

[chulett]

In my first post, I took your intention to "connect to the database" quite literally rather than thinking you meant running a sqlplus session from the command line. There are a whole suite of BCI (BASIC Call Interface) functions for connecting to and running sql from external databases and that's what I was referring to.

Check it out sometime. An exact search here will turn up some interesting conversations on the topic.