executing a set of sql queries in a job

pranay · Post by **pranay** » Fri Jun 13, 2008 12:17 pm

is ur problem resolved.

pranay wrote:you need to create the stored proc in the database first and then call it through datastage.

kirankota79 wrote:
pranay wrote:the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.

do i need to create the procedure on the database first? Is it not possible to use the sql procedure directly in the stored procedure stage and run the job against the tables.

kirankota79 · Post by **kirankota79** » Fri Jun 13, 2008 1:09 pm

i have to ask the database team if they allow me to create a procedure on their database. Once i do this, i will let you know.

Thanks for all your responses!

chulett · Post by **chulett** » Fri Jun 13, 2008 1:49 pm

Before you go too far, you've never said what kind of "queries" these sets are. Insert/Update DML? Then your sproc idea could work. Select type stuffs? Not so much.

kirankota79 · Post by **kirankota79** » Fri Jun 13, 2008 2:06 pm

all the sql statements are update statements. each query update a "comments" column of long data type in different tables. Since datastage cannot handle lond datatypes and i have to use go with this process on all the tables that has a comments column with long type. Does this makes sense?

chulett · Post by **chulett** » Fri Jun 13, 2008 2:48 pm

Yup. A LONG, eh? Yuck.

kirankota79 · Post by **kirankota79** » Mon Jun 16, 2008 2:40 pm

Is it not possible to create the procedure if my userid has the privileges through a role instead of direct privileges for tables? Even after granting the privilege "create Procedure" i am getting into ORA-01031-insufficient privileges errors

I came to know in order to compile the procedure , you need to have direct privileges on the tables that you use in the block of the procedure instead through a role. Is it right?

chulett · Post by **chulett** » Mon Jun 16, 2008 2:48 pm

Yup, you need a direct grant, something your DBA should be able to confirm for you.

kirankota79 · Post by **kirankota79** » Tue Jun 17, 2008 7:48 am

pranay wrote:the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.

[/quote]

I have created the procedure...and can you be more specific how you mapped the input column. Do you need to declare a variable in procedure so that it can match the input column just to trigger the job?

kirankota79 · Post by **kirankota79** » Tue Jun 17, 2008 3:54 pm

can you let me know how the mapping is done.

i am getting the following errors:

ODBC: When checking operator: When binding output interface field "DUMMY" to field "DUMMY": Converting nullable source to non-nullable result; fatal runtime error could occur (use modify operator to specify value to which null should be converted)

ToUpdate,0: Info: ToUpdate: BEGIN SCOTT.MASKCOMMENTS(:1); END;
Info: ToUpdate: Using NLS map ISO-8859-1

ToUpdate,0: Warning: ToUpdate: The value of the row is: DUMMY = X

ToUpdate,0: Failure during execution of operator logic.

ToUpdate,0: Input 0 consumed 1 records.

APT_CombinedOperatorController,0: Fatal Error: Fatal: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'MASKCOMMENTS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

node_node1: Player 1 terminated unexpectedly.

kirankota79 · Post by **kirankota79** » Wed Jun 18, 2008 7:26 am

i have done this on my test database (on my PC). It is working and i have to test this on the real database...once once i am done with it , i will tag it as resolved!

thanks for all the suggestions.