Oracle Stored Procedure call from DB Read

Unanswered Question
Nov 14th, 2007
User Badges:

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.

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
BCOLE2007 Fri, 11/16/2007 - 10:42
User Badges:
  • Bronze, 100 points or more

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.


mserwon Tue, 11/27/2007 - 16:10
User Badges:

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

BCOLE2007 Tue, 11/27/2007 - 17:49
User Badges:
  • Bronze, 100 points or more

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"



Actions

This Discussion