Page 2 of 2
Posted: Fri Jun 13, 2008 12:17 pm
by pranay
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.
Posted: Fri Jun 13, 2008 1:09 pm
by kirankota79
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!
Posted: Fri Jun 13, 2008 1:49 pm
by chulett
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.
Posted: Fri Jun 13, 2008 2:06 pm
by kirankota79
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?
Posted: Fri Jun 13, 2008 2:48 pm
by chulett
Yup. A LONG, eh? Yuck.
Posted: Mon Jun 16, 2008 2:40 pm
by kirankota79
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?
Posted: Mon Jun 16, 2008 2:48 pm
by chulett
Yup, you need a direct grant, something your DBA should be able to confirm for you.
Posted: Tue Jun 17, 2008 7:48 am
by kirankota79
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?
Posted: Tue Jun 17, 2008 3:54 pm
by kirankota79
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.
Posted: Wed Jun 18, 2008 7:26 am
by kirankota79
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.