Database wizard and Procedure data access expression

Architect & Flows
1

Hi All,

Well, I’m not a rookie with IRD but I did not use it since a while and especially the database wizard.
I’m stuck using the procedure data access expression.
My procedure is working fine when executed from Toad or SQL*Plus.

I don’t remember how to cast an Oracle procedure easily : shame on me :cry:

Thanks for your help folks >:frowning:

URS logs below:
17:25:53.451_I_I_00a50180f7cfe0b0 [07:38] HERE IS XDATA
D_I [07:38] request 6 to dbserver DEV_PRI_DBS_01 sent: UPDATE_TEST BR_SITE=‘999’
17:25:53.451_M_I_00a50180f7cfe0b0 [17:11] VQ 00d93500 first available call: none, reason=strategy
…
17:25:53.466_I_I_00a50180f7cfe0b0 [09:05] >>>>>>>>>>>>resume interpretator(0)
_I_E_00a50180f7cfe0b0 [09:05] error in strategy: 0013 Remote error
_I_I_00a50180f7cfe0b0 [09:04] ASSIGN: __DBReturn(SCRIPT) ← STRING

DBServer logs below:
17:28:59.236 Dbg 10741 ‘App (DEV_PRI_URS_01,livdatgen05,0)’ has put request id=‘7’ into queue
17:28:59.236 Dbg 10737 Forwarding request ‘7’ from ‘App (DEV_PRI_URS_01,livdatgen05,0)’ to ‘Oracle: 40.1’
17:28:59.236 Dbg 10743 ‘Oracle: 40.1’ is spare
17:28:59.252 Dbg 10739 Oracle: id=‘40.1’ req=‘7’ execute sp: UPDATE_TEST BR_SITE=‘999’
+0015 MSG_ERROR status=‘5’ msg='ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to ‘UPDATE_TEST’
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Related procedure below:
CREATE OR REPLACE PROCEDURE UPDATE_TEST (
BR_SITE IN CHAR, OUTPUT OUT CHAR)
AS

– String definition
STR_SITE CHAR(3) := BR_SITE;

BEGIN

INSERT INTO RPT1 (BR_SITE) VALUES (STR_SITE);

OUTPUT := 'OK';
COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    OUTPUT := 'KO';

END;
/

2

I am not familiar with Oracle, but my first guess would be it’s looking for a parameter to go with the “OUTPUT OUT CHAR” in your definition.

1 Like
3

Hi,

Your stored procedure has 2 parameters so you have to specify both when trying to call it. Please modify configuration in IRD and add following parameter “OUTPUT = @out”.

René

1 Like
4

Thanks Kevin for your help but the “OUTPUT OUT CHAR” is the right syntax in a stored procedure.

Thanks René, I’ve done it but it’s still failing.
The next step in the wizard is to assign an output value to a variable and I’ve done it too.
I tried to change the type of the variable used with an integer and I’ve changed it into the procedure too but it’s still failing! Those output values are a nightmare :-[

Sql statement in the wizard below:
UPDATE_TEST BR_SITE=varSITE,OUTPUT=@out
If I remove the OUPUT=@out in the wizard, it’s working… but I need to get a status from the procedure.

Any other ideas out there :cry:

5

What version of Oracle we’re talking about?

R.

6

I’m using the Oracle’s version 10i.

2 Likes
7

Ok. I will test it on my system and let you know then.

BTW. What version of Genesys URS and DBServer you have?

R.

8

Thanks for you help René.
URS 7.5.000.07
DBServer 7.5.000.07

In order to go further on my strategy, I’ve removed the OUPUT=@out from the sql statement and I’ve quoted the box “Do not use output value(s)”.

9

I’ve been able to do some testing and it’s working for me.

Components used: DBServer 7.5.000.07, URS 7.2.000.01, Oracle 10g Express

IRD DB Wizard configuration - please see attached screenshot

URS log
17:09:10.296_I_I_00930182b8226001 [07:38] HERE IS XDATA
D_I [07:38] request 1 to dbserver rut_dbserver sent: UPDATE_TEST BR_SITE=‘SGGU’,OUTPUT=@out
17:09:10.296_I_I_00930182b8226001 [09:04] <<<<<<<<<<<<suspend interpretator(XDATA_EX), timers:00000
nMsgID = MSG_RETRIEVED; nServer = 0; nStatus = 1; nRequestID = 1; nProfileTime 0 szSQLCmd = ‘SP Params’; pRecord = 0x1d85b40
key OUTPUT [String] value: “SGGU”
17:09:10.312_D_I_00930182b8226001 [05:02] MSG_RETRIEVED(status success) is received from dbserver rut_dbserver
nMsgID = MSG_PROCCOMPLETED; nServer = 0; nStatus = 1; nRequestID = 1; nProfileTime 0 szSQLCmd = ‘’; pRecord = 0x0
17:09:10.312_D_I_00930182b8226001 [05:02] MSG_PROCCOMPLETED (status 1) is received from dbserver rut_dbserver

DBServer log
17:09:10.296 Dbg 10739 Oracle: id=‘1.1’ req=‘1’ execute sp: UPDATE_TEST BR_SITE=‘SGGU’,OUTPUT=@out
+0000 SP fetch record failure (no data)
+0000 MSG_RETRIEVED status=‘DBM_SUCCESS’ text=‘SP Params’
+0000 MSG_PROCCOMPLETED status=‘DBM_SUCCESS’ msg=‘The procedure completed ok.’

R.

1 Like
10

Thanks René for your help 8)

10:43:18.311_I_W_00a50180f7cfe0eb [0F:02] abort request time is over
10:43:18.311 Int 21001 interaction 00a50180f7cfe0eb data lookup fails
10:43:18.311_M_I_00a50180f7cfe0eb [17:11] VQ 00d5ce80 first available call: 00a50180f7cfe0eb, reason=strategy
10:43:18.311_I_I_00a50180f7cfe0eb [09:05] >>>>>>>>>>>>resume interpretator(0)
_I_E_00a50180f7cfe0eb [09:05] error in strategy: 0013 Remote error
_I_I_00a50180f7cfe0eb [09:04] ASSIGN: __DBReturn(SCRIPT) ← STRING

I’ve fixed it with a URS request_timeout option = 1000 instead of 2 (by default).
I forgot that point.

Bye ::slight_smile: