0

Appreciate,if anybody help me out with this.

I want to select a record from DB using a DB adapter in OSB and for that i have written a stored procedure in oracle which is like below:-

create or replace
PROCEDURE TRY_POC(ERROR_STATUS IN varchar2,TYPE_ERROR IN  varchar2,ERROR_TIMESTAMP IN varchar2,p_cur out sys_refcursor) IS

BEGIN

open p_cur for
select * from CommonLogTable where STATUS=ERROR_STATUS
and ERROR_TYPE=TYPE_ERROR
and
 to_date(
(
substr(Error_Timestamp,9,2)||'-'||
substr(Error_Timestamp,6,2)||'-'||
substr(Error_Timestamp,1,4)||' '||
substr(Error_Timestamp,12,2)||':'||
substr(Error_Timestamp,15,2)||':'||
substr(Error_Timestamp,18,2)
),'DD-MM-YYYY HH24:MI:SS') < to_date(to_char(current_timestamp,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
AND  to_date(
(
substr(Error_Timestamp,9,2)||'-'||
substr(Error_Timestamp,6,2)||'-'||
substr(error_timestamp,1,4)||' '||
substr(Error_Timestamp,12,2)||':'||
substr(Error_Timestamp,15,2)||':'||
substr(Error_Timestamp,18,2)
),'DD-MM-YYYY HH24:MI:SS') >= to_date(to_char(current_timestamp-(5 * (1/24/60)),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-
YYYY HH24:MI:SS');
end TRY_POC;

What this stored procedure is doing that it is fetcing te records from CommonLoTable based upon the Status,Error_Type and Error_Timestamp(which will be passed at runtime),and fetch the records which lies between

Current Timestamp=Current Timestamp-5 And the error Timestamp Format i need is like 2016-08-13T16:30:00.

I want to pass the values of all Error_Type,Status and Error_Timestamp at runtime.I am not able to pass the value of Error_Timestamp at runtime from OSB .

Thanks in Advance

Insingh
  • 1
  • 2

1 Answers1

0

You can use xquery function execute-sql() in pipeline and call your sql statements. Input parameters can be accepted at runtime from proxy service and then map those values to sql query condition parameters.

TarunChhabra
  • 99
  • 1
  • 4