DBQuery from Oracle procedure (mercator 6.7.1)

[OAIusr]

I used to select records from a Sybase database with Mercator 6.7.1. via a stored procedure in an output card :
PUT("FILE","%PATH_DATA%", Package(
DBQUERY("spu_my_sybase_stored_proc 'param1'", "%DB_SYBASE%
)
)
But we have now an Oracle database and I try to do as simple. It doesn't work : nothing is returned by the procedure despite the map ends "successfully".
Moreover if I execute the procedure in the PL/SQL the procedure returns my records well.

Can anyone explain me where's the mistake ?
regards

Here is the Oracle procedure script :

CREATE OR REPLACE PROCEDURE "OPEUSR"."SPU_OAIM_GETSERVPARAM" (
P$service IN oai_demande_service.oserivc_code_service%TYPE
)
is
begin

DECLARE

CURSOR C_GSP ( LC$service IN oai_demande_service.oserivc_code_service%TYPE ) IS
SELECT odes.odesii_ref AS iref, odes.oserivc_code_service AS serv, opar.opar_vc_code_param AS code_param, opar.opar_vc_valeur_param AS param
FROM oai_demande_service odes, oai_parametre opar
WHERE opar.odesii_ref = odes.odesii_ref
AND odes.odes_i_status = 2
AND odes.oserivc_code_service = LC$service ;

BEGIN

dbms_output.enable(1000000);

UPDATE oai_demande_service
SET odes_i_status = 2
WHERE oserivc_code_service = P$service;

FOR cur IN C_GSP ( P$service ) LOOP
dbms_output.put_line ( To_char( cur.iref ) || ' ' || cur.serv || ' ' || cur.code_param || ' ' || cur.param );
END LOOP;

END;
end;

 

[BocaBurger]

First turn on the DB trace to see if that is even being called. You could also eliminate the PUT(File) stuff and just run the DBQUERY and look at the output of the card to see if that is returning what you expect, still with a -T or -TE in the DBQUERY command line.

PUT (and Get) has limited error handling (by design)



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[OAIusr]

i do it with a new procedure where i do the same thing.
I print to the screen my result(whith dbms_output)

when i execute the procedure in Oracle client i have what i expect, but when i use mercator i have nothing. I can catch the result in my file.

Mercator Commande:
=DBQUERY("begin spu_oaim_GetParamService ; end; ","-DT ORACLE -C CONNECT_STRING -US LOGIN -PW PASSWD -T test.dbl")

The file test.dbl:
<367-257>: Validating the adapter command...
<367-257>: Database type is Oracle
<367-270>: Participating in active transaction.
<367-270>: Interface library version 6.7(306)
<367-270>: Map: C:\WORKAREA\Mercator\_COMMUN_\exemples\TriggerOracle\test.mmc, Timestamp: 08/04/04 15:23:44.
<367-270>: Data being retrieved for DBQUERY function.
<367-270>: Database adapter: Oracle8i Version 6.7(306)
<367-270>: Starting a database unload...
<367-270>: Query : begin spu_oaim_GetParamService ; end;
<367-270>: Statement execution succeeded.
<367-270>: Retrieved 0 records (0 bytes).
<367-270>: Returned status: (0) Success
<367-270>: Cleaning up and closing the transaction...
<367-270>: The transaction was successfully committed.
<367-270>: Returned status: (0) Success
<367-270>: Disconnecting...
<367-270>: Database disconnect succeeded.
<367-270>: Returned status: (0) Success

the new procedure:
CREATE OR REPLACE PROCEDURE OPEUSR.spu_oaim_GetParamService
IS

l_i_NumLigne INTEGER :=0;
l_i_ref_trigger INTEGER;
l_i_ref_dmd INTEGER;

l_vc_type_demande VARCHAR(10);

CURSOR c_ParamService
IS
SELECT otrs.otrsii_ref AS c_i_ref_trigger,
otrs.otrs_vc_type_demande AS c_vc_type_demande,
otrs.odsrii_ref AS c_i_ref_dmd,
odsr.olsrivc_code_service AS c_vc_code_service,
odsr.odsr_dt_declanchement AS c_dt_declanchement,
olsr.olsr_vc_nomrepertoire AS c_vc_nomrepertoire,
olsr.olsr_vc_nomfichier AS c_vc_nomfichier,
oltp.oltp_vc_code_param AS c_vc_code_param,
oltp.oltp_vc_valeur_param AS c_vc_valeur_param
FROM oai_trigger_service otrs,
oai_dmd_service odsr,
oai_list_service olsr,
oai_param_dmd_service oltp
WHERE otrs.odsrii_ref = odsr.odsrii_ref
AND odsr.olsrivc_code_service = olsr.olsrivc_code_service
AND odsr.odsrii_ref = oltp.odsrii_ref
AND otrs.otrs_i_status = 2
ORDER BY 9;

BEGIN

UPDATE oai_trigger_service
SET otrs_i_status = 2
WHERE ROWNUM <= 1;

FOR l_cur_res IN c_ParamService
LOOP
IF( l_i_NumLigne = 0 ) THEN
dbms_output.put_line( l_cur_res.c_i_ref_trigger || '#' ||
l_cur_res.c_vc_type_demande || '#' ||
l_cur_res.c_i_ref_dmd || '#' ||
l_cur_res.c_vc_code_service || '#' ||
l_cur_res.c_dt_declanchement|| '#' ||
l_cur_res.c_vc_nomrepertoire || '#' ||
l_cur_res.c_vc_nomfichier );

l_i_ref_trigger := l_cur_res.c_i_ref_trigger;
l_i_ref_dmd := l_cur_res.c_i_ref_dmd;
l_vc_type_demande := l_cur_res.c_vc_type_demande;

l_i_NumLigne := 1;
END IF;

dbms_output.put_line( l_cur_res.c_vc_code_param || '#' ||
l_cur_res.c_vc_valeur_param );
END LOOP;

DELETE oai_trigger_service
WHERE otrsii_ref = l_i_ref_trigger;

IF(l_vc_type_demande = 'TTMPREEL' ) THEN
INSERT INTO oai_etat_service (
odsrii_ref,
olstii_status)
VALUES (l_i_ref_dmd,
5);
ELSE
INSERT INTO oai_etat_service (
odsrii_ref,
olstii_status)
VALUES (l_i_ref_dmd,
3);
END IF;

END;
/


HELP PLEASE

 

[BocaBurger]

I think you need to use "Call?=procedurename (param1, param2, ?)" not the begin;end syntax you have. The second ? is for the return parameter. First ? is for the dbquery return.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[OAIusr]

Thanks for your solution about the dbquery function.
But now, what's the method with an input card ?
For exemple, my oracle function or procedure returns the object TAB_RECORD below :

TYPE t_record is RECORD (
Id_cd varchar2,
Val_cd integer);

TYPE TAB_RECORD IS TABLE OF t_record;

The function returning this type is f_getTRecord.
How can I get the typetree corresponding to the returned type ? I'v tried in the database interface designer with a Query named CallFunction : call ?= f_getTRecord
but look at the typetree generated :
Data
--CallFunction
----Column
------RETURN_VALUE
----DBSelect
----Row

No one of my t_record types appears (the varchar2 and INT).

Best Regards

 

[BocaBurger]

Not sure what version of the product you are using, but you don't generate a stored procedure tree from a query, you generate it from a stored procedure. you should have three items under the Databased in the DID,

Databases
Database name
Queries
Stored Procedures
Tables
Variables



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[BocaBurger]

Now I read the title of this thread (I'm thick sometimes), you have 6.7.1, so you should see what I described.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[OAIusr]

Ok, in fact I don't want the typetree corresponding to the stored proc parameters (usefull for an output card) but the typetree corresponding to the returned type as described, to get it in my input card.

To summary, I have to call a function or stored proc (with or without params) in an INPUT card to get records.
So how could I get the typetree, and what's the command line for the oracle adapter in the input card ?

 

[BocaBurger]

To generate a tree with the results from the stored procedure, you use the call statement in the DID, and use the MDQ in the input card. You will have a tree that validates the return of the stored procedure.

Beyond this, you will have to contact Ascential Support (or wait for someone else ) for more help.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[OAIusr]

That's what I tried to explain in my previous post with my exemple t_record : the typetree generated by the DID contains only one item instead of the tow items defined in the type t_record.

Is my explanation so full of mistakes ?

 

[BocaBurger]

No, but without seeing the MDQ and what you did in it, it is hard to diagnose. Send Ascential Support the MDQ, SQL to create the stored procedure, and an explanation of what you are trying to do and the results you expect, and if needed, they can engage engineering.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[OAIusr]

Bocaburger,

After search on other forums, the only response I needed was that it's impossible to get multiple rows in input cards from an Oracle stored proc... We are forced to write the select statement in the command line adapter or in the mdq file.

Oaiusr

 

[eyetry]

Hope I understand the problem.... Sounds like a tree problem.

We don't have anything running that requires more than one row of data. However we do have a Stored procedure that reponds with multiple occurances of data found in a single column.

In DBID wrote a quiry like this...

call GET_INFO_PROC(#var1#,#var2#,?,?,?,?,?,?,?)

The ? representing columns of response data not accounted for as a variable, ie response will contain 9 columns of data, some columns will occur multiple times.

In our case your data is returned looking like....

[column1:1]|[column2:1]|[column3:1]|[column4:1|column4:2|column4:3]|[column5:1|column5:2|column5:3]|[column6:1|column6:2|column6:3]etc...........

or
[0]|[JOHN]|[DOE]|[MARY|MARK|ALICE]|[SMITH|DOE|DOE]|[SPOUSE|SON|DAUGHTER]etc...........


In the sample above there Columns 1 - 3 might be subscriber specific and will only occur once. While column 4 and 5 would contain information on 3 dependents and occur 1 to many times.

DBID did a fine job of defining the Query tree but the Tree used for the Stored Procedure left something to be desired. We manually tweaked it to get it to work.

Tree looked something like....

MASTER_PROCEDURE_GROUP
PROCEDURE_CALL_GROUP (0:S)
RETURN_CODE_GROUP (0:1)
RETURN_CODE_ITEM Argument (0:2147483647)
COLUMN2_GROUP (0:1)
COLUMN2_ITEM Argument (0:2147483647)
COLUMN3_GROUP (0:1)
COLUMN3_ITEM Argument (0:2147483647)
COLUMN4_CODE_GROUP (0:1)
COLUMN4_ITEM Argument (0:2147483647)
COLUMN5_GROUP (0:1)
COLUMN5_ITEM Argument (0:2147483647)
COLUMN6_GROUP (0:1)
COLUMN6_ITEM Argument (0:2147483647)
....

Keep in mind that the systems we use with the stored procedures were initially created in version 6.0. Some of the issues that were present in 6.0 may be resolved in 6.7.1.

Let me know if this was of help.

new2game