Hi team, I need to run a stored procedure using jasper. Code should be given in the job (say, it can be given in tfileInputDelimited). So that, the code should be fetched by the database and should be run. toraclesp components calls a stored procedure which is already created in db. But i need to create a proc using the code which is to be given by me in the job. Is this possible? Thanks in advance
Hi Hermion, You can always run the create or replace script of the stored procedure in the tOracleRow component. This component accepts all kinds of statements - even those to create or replace procedures or functions. You should check after the creation the Oracle build-in view user_errors to be sure there is not error message regarding your new created or updated procedure. (all flows are OnComponentOK) tFileInputDelimited ---> tJavaRow (build the procedure create statement) ---> tOracleRow (run the create statement) ---> tOracleInput (select user_errors) --- (main flow) ---> tDie (die of you find a record) tOracleInput --- (OnSubjobOK) ----> tOracleSP (call your procedure) to check if your procedure is installed correctly use this SQL code. This select returns zero rows in case of OK.
select * from USER_ERRORS where NAME='your_procedure_name'
Hi jlolling, Sorry.. I am not able to create the job as mentioned. In tJavaRow, how to build the procedure create statement and in tOracleRow component, how to run the create statement. Regards, Hermione
Hi Hermion, To be honest, I would do the job if you would life in my region ;-) For a more detailed help I need more information how you get your stored procedure code. If you get the code from a file without any necessary changes you can remove the tJavaRow component. The job you want to create is actually not a good task for beginners. You have to be familiar with tOracleRow: This component can run arbitrary statements and tOracleInput which can run a select statement and finally tOracleSP. I would start with build the stored procedure code and save it in a context variable. This context variable (now containing the SQL create statement of your stored procedure) have to use in the tOracleRow component as Quere. To take care the store procedure is correctly compiled in the database you have to check that with the select statement to the view user_errors. This will be done with the tOracleInput component and if there is a record the tDie component let your job die. After that check you could run the stored procedure with tOracleSP (even with parameters as prepared statements). Not a rocket since and a bit straight forward but this should work. If you have further question, please ask a bit more concrete about specific aspects. Happy new year!
Regarding the job, I have used the flow as below: tFileInputDelimited -> tOracleRow ->tOracleInput -> tDie toraclesp-> (on subjob ok) -> tmsg In tFileInputDelimited, using context (File type), i have used the below mentioned proc (for testing purpose) create or replace procedure sample_job as begin insert into temp values(1,'Inserted'); end; In tOracleRow, in the query, i gave the context name.
In tOracleInput, the query used is select * from USER_ERRORS where NAME='sample_job';
In toraclesp, i have given the proc name
But everytime i run the job, am getting 'invalid SQL statement' as error. Job is trying to run the proc given and it is not compiling it. How to do this? Pls help..
In which component do you get this message? Please erase the ; at the end of the select * from user_errors... sorry my fault. Oracle compiles the procedure automatically but if something fails this will be reflected only in the user_errors view.
Hi jlolling, ORA-00900: invalid SQL statement ORA-00900: invalid SQL statement ORA-00900: invalid SQL statement ORA-00900: invalid SQL statement Exception in component tOracleSP_1 java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'CONTEXT.PROC' must be declared This is the error which am getting. and in toraclesp component, i get this message. Pls help me for resolving this issue Sorry for the delayed response. Regards, Hermione
I guess you have in you context variable proc the SQL code. Remove all double quotas ! I guess you have written in the tOracleSP component: "context.proc" This send exactly this string to the database and of course this is not what you want. Use: context.proc simply without double quotas.
Hi jlolling, I have a doubt here. in tfileinputdelimited component, when i use context.proc, what should be given as schema. Until now, i used list. Is this correct or what should be used in edit schema option for tfileinputdelimited. Pls help Regards, hermione
in toraclerow, am getting the error. i guess, the proc is not pulled into the toraclerow component properly. am not using any double quotes or ; in the components. but still the error persists as 'invalid sql statement'
Hello Guys I am facing same issue with executing a parametrized proc in toraclerow my statement is something like BEGIN KNVDEVPOC.XDLU_LOAD_CATALOGENTRIESS.EXECUTE_XDLU(1); END; Exception in component tOracleRow_1 java.lang.NullPointerException at catalogfeeder_1.catalogfeederetl_process_run_0_1.CatalogFeederETL_Process_run.tOracleRow_1Process(CatalogFeederETL_Process_run.java:9041) please help Thanks VH