One Star

How to call a procedure from a wordpad

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

Regards,
Hermione
10 REPLIES
Seventeen Stars

Re: How to call a procedure from a wordpad

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'
One Star

Re: How to call a procedure from a wordpad

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
Seventeen Stars

Re: How to call a procedure from a wordpad

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!
One Star

Re: How to call a procedure from a wordpad

Hi jlolling,
Happy new year to u too Smiley Happy

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..
Seventeen Stars

Re: How to call a procedure from a wordpad

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.
One Star

Re: How to call a procedure from a wordpad

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
Seventeen Stars

Re: How to call a procedure from a wordpad

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.
One Star

Re: How to call a procedure from a wordpad

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
One Star

Re: How to call a procedure from a wordpad

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'
One Star

Re: How to call a procedure from a wordpad

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