cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
613
Views
0
Helpful
3
Replies

Oracle Stored Procedure call from DB Read

mserwon
Level 1
Level 1

Does anyone have an example of calling a oracle stored procedure from a DB Read step? I am currently using exec stored_proc $input_var , $output_var from the DB Read but only get a SQL error in return. It would really help if the example could show a stored procedure so I could pass it along to my DBA.

3 Replies 3

BCOLE2007
Level 1
Level 1

I had the same need as you, and so far only

have been able to get a Oracle PL/SQL Function not Procedure to work like this

for the function:

select time_function as result from dual

and define a table for the result set

this is very limited, as the parser didn't like input values passed in through the columns. this works for now but will probably convert it to the expression language when time permits,

another technique to try would be insert values into another input table, then use the Select from dual to call a function, while in the PL/SQL function use the values from the input table, not very smooth but works in theory

I think another way would be thru Java and JDBC, but have not done it.

Eventually I gave up on PL/SQL procedures and just used views and the Script to do the work.

I can get the db read step to return a row, but not the db get step. How did you define the table for the result set?

Thanks

create an empty table in Oracle with the same field structure as you select from dual , do you have Sql Plus, Toad, or Sql Developer?

for example

select 'x' from dual is your DB READ

now go into oracle , easiest way to create a table

create TEST_WORK_TBL

AS SELECT 'X' FROM DUAL

,You may need your DBA to put other parameters on this such as a tablespace and sizing, even though it will never have any data in it

DELETE * FROM TEST_WORK_TBL (to remove the 'X')

select * from TEST_WORK_TBL, to prove its empty

go back to CRS and

now point your DB GET to TEST_WORK_TBL, remember to refresh your schema to see it in the drop down

on another note, over the weekend I was able to pass a $var to a really wacked out SQL statement, to obtain the next sunday, except when its Sunday, this should work the same with PL/SQL functions, I didn't have the time to write the date functions in CRS/Java and most RDBMS stuff Oracle, Sql Server, or others are usually pretty easy,

this did the trick for the DB READ

select to_char(b.dt,'MM/DD/YYYY') as DATE1 from dual a ,

(select NEXT_DAY

(TO_DATE( $strWorkDate ,'MM/DD/YYYY'),

'Monday')-1 as dt from dual) b

and define a get work table like above

notice the spaces on both sides of the variables, someone has pointed that out with a on stored procedures, (I think they were SQL server)

The second Dual select is treated as a "virtual table"

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: