Error executing stored procedure in Outsystems error ORA-01830: date format picture

Hi,

I'm getting this error 

  • ORA-01830: date format picture ends before converting entire input string

when I executed a stored procedure in Outsystems, but when I executed the stored procedure in Oracle I get results.

I already tried to pass the date in Outsystems as date type and as text but return the same error. On database the format of the date is dd.mm.yyyy and the field is a varchar2. I'm converting in the stored procedure to the date format yyyy-mm-dd so the input parameter in the stored procedure is on format yyyy-mm-dd like the Outsystems format.

Can someone help me?

Hi,


I need to add "and to_date(:3,''yyyy-mm-dd'') >=to_date(dim04,''dd.mm.yyyy'') and (to_date(:4,''yyyy-mm-dd'') <=to_date(dim05,''dd.mm.yyyy'') or to_date(dim05,''dd.mm.yyyy'') is null))" this on stored procedure and remove the to_char.

Thanks.

Hello @Ana Rita Loureiro 

Does your code logic allow you to convert the date format inside the OutSystems app and not in the stored procedure? This way with the appropriate breakpoints in place you could also make sure you are receiving the expected data inside your app, then you convert it to the desired yyyy-mm-dd format.

Alternatively, could you share the code for the date format conversion in the stored procedure? 

Best regards,

AJ

Hi AJ,

The code is inside of a package with many stored procedure:

PROCEDURE POLICY_GET_XXXXX (

P_POLICY IN a.POLICY_NO_ALT%TYPE,

P_DURATION IN NUMBER,

P_DATE IN DATE,

A_CURSOR OUT SYS_REFCURSOR) AS


STMT VARCHAR2(4000);


BEGIN


IF P_POLICY IS NOT NULL THEN


STMT := 'select p.policy_no_alt as PolicyNumber,o.NO_OF_DAYS as Duration,o.NO_OF_PEOPLE as NoPeople ';

STMT := STMT || 'from obj_XXX o,a p, s pl ';

STMT := STMT || 'where o.agr_line_no = PL.AGR_LINE_NO ';

STMT := STMT || 'and p.mp_policy_no = (select policy_no from a where policy_no_alt = :1 and newest =''Y'') ';--apólice

STMT := STMT || 'and p.policy_no = pl.policy_no ';

STMT := STMT || 'and p.transaction_type != ''C'' AND P.NEWEST= ''Y'' AND PL.NEWEST= ''Y'' and o.newest=''Y'' ';

STMT := STMT || 'and o.NO_OF_DAYS = :2 ';--duração

STMT := STMT || 'and p.c06 = (select dim03 from CUBE where structure_key = ''LP_GL_TARIFF'' and type = ''TRF_GROUP_AP'' and dim01 = ''2M'' and dim02 IN (217)

and to_date(:3,''yyyy-mm-dd'') >=dim04 and (to_date(:4,''yyyy-mm-dd'') <=dim05 or dim05 is null)) '; -- DataOriginalRegisto



OPEN A_CURSOR FOR STMT USING P_POLICY, P_DURATION, to_char(P_DATE, 'yyyy-mm-dd'), to_char(P_DATE, 'yyyy-mm-dd');

ELSE

NULL;

END IF;

END POLICY_GET_XXXX;


END LCP_XXXXX;


This code works, in Oracle. When I tried to execute in Outsystems gives that error.

Thanks.

Hi,


I need to add "and to_date(:3,''yyyy-mm-dd'') >=to_date(dim04,''dd.mm.yyyy'') and (to_date(:4,''yyyy-mm-dd'') <=to_date(dim05,''dd.mm.yyyy'') or to_date(dim05,''dd.mm.yyyy'') is null))" this on stored procedure and remove the to_char.

Thanks.

Hi Ana,

have you read the document https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Logic/Built-in_Functions/Format because it could be that you are formatting a date using yyyy-mm-dd, where mm stands for minutes and MM for month.

regards Hans

Hi Hans,

Isn't a date time format that I wanted, but a date.

Thanks.

Hi Ana, 

when calling the stored procedure, you are passing a date value as text, right? I can imagine that you use format_datetime(date, 'yyyy-mm-dd') to pass a date as text to the oracle SP. But that must be format_datetime(date, 'yyyy-MM-dd'). 

Can you share the code about how you pass this date value from outsystems to the Oracle SP?

regards Hans 

Hi Hans,

I retrieve the date from the database is in format #2021-09-17 00:00:00#, I convert the date to text and stay in the format "2021-09-17" and send that format to the stored procedure.

Thanks.

errocancelpolicy4.png

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.