Database wizard and Procedure data access expression

[chicodobrazil]

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

Thanks for your help folks

[b]URS logs below:[/b]
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: [color=red][b]UPDATE_TEST BR_SITE='999'[/b][/color]
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

[b]DBServer logs below:[/b]
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' [color=red][b]execute sp: UPDATE_TEST BR_SITE='999'[/b][/color]
+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

[b]Related procedure below:[/b]
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;
/

[Kevin S]

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.

[René]

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é

[chicodobrazil]

[quote author=Kevin S link=topic=2560.msg9882#msg9882 date=1194375818]
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.
[/quote]

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

[quote author=René link=topic=2560.msg9885#msg9885 date=1194383019]
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é
[/quote]

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

[b]Sql statement in the wizard below:[/b]
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

[René]

What version of Oracle we're talking about?

R.

[chicodobrazil]

[quote author=René link=topic=2560.msg9893#msg9893 date=1194435586]
What version of Oracle we're talking about?

R.
[/quote]

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

[René]

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

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

R.

[chicodobrazil]

[quote author=René link=topic=2560.msg9896#msg9896 date=1194437647]
Ok. I will test it on my system and let you know then.

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

R.
[/quote]

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)".

[René]

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

[b]URS log[/b]
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

[b]DBServer log[/b]
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.

[chicodobrazil]

Thanks René for your help

10:43:18.311_I_W_00a50180f7cfe0eb [0F:02] [b]abort request time is over[/b]
10:43:18.311 Int 21001 interaction 00a50180f7cfe0eb [b]data lookup fails[/b]
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