Field ORDER in procedure

[vbellema]

Since I did the update, when I run a procedure (which has not been changed) I get an error message:

ORA-06550 - line 2, column 3:
PLS-00306: wrong number or type of arguments in call to 'P_REF_NB_INS_CRS_TRIM'
ORA-06550: line 2, column 3:
PLS-00306: wrong number or type of arguments in call to 'P_REF_NB_INS_CRS_TRIM'
ORA-06550: line 2, column 3:
PL / SQL: Statement ignored

in the stack trace log

before:
BEGIN
   PRISME.P_REF_NB_INS_CRS_TRIM (: p0,: p1,: p2);
END;
- P0: Output Cursor (Size = 0; DbType = Object) []
- P1: Input Char (Size = 2, DbType = String) [03]
- P2: Input Char (Size = 7 = DbType String) [ABC1234]
- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: Build year: 3.1.43.0

and now:
BEGIN
   PRISME.P_REF_NB_INS_CRS_TRIM (: p0,: p1,: p2);
END;
- P0: Input Char (Size = 2, DbType = String) [03]
- P1: Input Char (Size = 7 = DbType String) [ABC1234]
- P2: Output Cursor (Size = 0; DbType = Object) []
- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: MetaModel Build: 4.1.184.0

is it possible that this is due to the order of the parameters?

[MariiaI]

Could you please specify (or send us) the following:
- the SQL script for creating your procedure 'P_REF_NB_INS_CRS_TRIM';
- the generated code for the corresponding method (in the *.Designer.cs file);
- whether you are working with a predefined or custom template ( please see Properties->Type by right-clicking on the current template in the Model Explorer->Templates).

Looking forward to your reply.

[vbellema]

the SQL query is:

CREATE OR REPLACE PROCEDURE PRISME.P_REF_NB_INS_CRS_TRIM(
p_recordset IN OUT SYS_REFCURSOR,
p_eta_cd IN prisme.cours_etablissement.eta_cd%TYPE,
p_crs_cd IN prisme.cours_etablissement.crs_cd%TYPE)
is
BEGIN
OPEN p_recordset FOR
SELECT z2.eta_cd,
z2.crs_cd,
z2.decl_aatrim,
ce2.crs_ti,
ce2.cdr_cd,
ce2.uni_admin_cd,
ce2.crs_cr_nb/10,
ce2.etd_niv,
ce2.stage_ind,
ce2.typ_stage_ind,
ce2.crs_hrs_que_ind,
z2.nb_etdt,
z2.nb_eeetp
FROM (SELECT z1.eta_cd,
z1.crs_cd,
z1.decl_aatrim,
z1.nb_etdt,
z1.nb_eeetp,
MAX(ce1.ouv_aatrim) ouv_aatrim
FROM (SELECT z.eta_cd,
z.crs_cd,
z.decl_aatrim,
COUNT(z.perm_cd) nb_etdt,
SUM(z.eeetp_crs_nb) nb_eeetp
FROM prisme.z_inscription_cours z
WHERE z.eta_cd = p_eta_cd
AND z.crs_cd = p_crs_cd
GROUP BY z.eta_cd,
z.crs_cd,
z.decl_aatrim) z1,
prisme.cours_etablissement ce1
WHERE z1.eta_cd = ce1.eta_cd
AND z1.crs_cd = ce1.crs_cd
AND z1.decl_aatrim >= ce1.ouv_aatrim
GROUP BY z1.eta_cd,
z1.crs_cd,
z1.decl_aatrim,
z1.nb_etdt,
z1.nb_eeetp) z2,
prisme.cours_etablissement ce2
WHERE z2.eta_cd = ce2.eta_cd
AND z2.crs_cd = ce2.crs_cd
AND z2.ouv_aatrim = ce2.ouv_aatrim
ORDER BY z2.decl_aatrim;
end P_REF_NB_INS_CRS_TRIM;


When I add the procedure in the diagram Devart, it changes the type of some fields and the order is no longer the same. It recreate the Complicated type back from my cursor.

We solved the problem because we could not wait for your next release. here is the new query (we only change the order of parameters):

CREATE OR REPLACE PROCEDURE PRISME.P_REF_NB_INS_CRS_TRIM(
p_eta_cd IN prisme.cours_etablissement.eta_cd%TYPE,
p_crs_cd IN prisme.cours_etablissement.crs_cd%TYPE,
p_recordset OUT SYS_REFCURSOR)
is
BEGIN

OPEN p_recordset FOR
SELECT z2.eta_cd,
z2.crs_cd,
z2.decl_aatrim,
ce2.crs_ti,
ce2.cdr_cd,
ce2.uni_admin_cd,
ce2.crs_cr_nb/10 crs_cr_nb,
ce2.etd_niv,
ce2.stage_ind,
ce2.typ_stage_ind,
ce2.crs_hrs_que_ind,
z2.nb_etdt,
z2.nb_eeetp
FROM (SELECT z1.eta_cd,
z1.crs_cd,
z1.decl_aatrim,
z1.nb_etdt,
z1.nb_eeetp,
MAX(ce1.ouv_aatrim) ouv_aatrim
FROM (SELECT z.eta_cd,
z.crs_cd,
z.decl_aatrim,
COUNT(z.perm_cd) nb_etdt,
SUM(z.eeetp_crs_nb) nb_eeetp
FROM prisme.z_inscription_cours z
WHERE z.eta_cd = p_eta_cd
AND z.crs_cd = p_crs_cd
GROUP BY z.eta_cd,
z.crs_cd,
z.decl_aatrim) z1,
prisme.cours_etablissement ce1
WHERE z1.eta_cd = ce1.eta_cd
AND z1.crs_cd = ce1.crs_cd
AND z1.decl_aatrim >= ce1.ouv_aatrim
GROUP BY z1.eta_cd,
z1.crs_cd,
z1.decl_aatrim,
z1.nb_etdt,
z1.nb_eeetp) z2,
prisme.cours_etablissement ce2
WHERE z2.eta_cd = ce2.eta_cd
AND z2.crs_cd = ce2.crs_cd
AND z2.ouv_aatrim = ce2.ouv_aatrim
ORDER BY z2.decl_aatrim;

end P_REF_NB_INS_CRS_TRIM;

I would not like to have to test all my procedures in my application each times we do an update of DEVART.

thanks

[MariiaI]

Thank you for the additional information. Yes, this issue is due to the fact that parameters of the method are generated in the wrong order and we are working on it, but we cannot provide any timeframe at the moment. We will inform you when any results are available.

As a workaround, you can try setting the correct order for parameters manually, i.e. :
1) Add attribute [ResultType(typeof(YourTypeResult), 0, ResultTypeOrigin.OutCursorParameter)] on the corresponding method,e.g.:

Code: Select all

[Function(Name=@"PRISME.P_REF_NB_INS_CRS_TRIM")]
[Devart.Data.Linq.Mapping.ResultType(typeof(YourTypeResult),0,ResultTypeOrigin.OutCursorParameter)]
public Devart.Data.Linq.ISingleResult<YourTypeResult> P_REF_NB_INS_CRS_TRIM([Parameter(Name="ID", DbType="NUMBER(38)")] System.Nullable<decimal> ID, [Parameter(Name="ID2", DbType="NUMBER(38)")] System.Nullable<decimal> ID2)
        {... }

However, if you add it manually to the *.Designer.cs file, these changes will be lost after making any changes to your model and regenerating the code. To prevent it, please do the following:
- open Model Explorer;
- right-click your stored procedure in Methods and select Attributes;
- press 'Yes' in opened dialog;
- in the opened 'Model Settings' window press "Add..." and find and select 'Devart.Data.Linq';
- you will see the list of available attributes, please select only 'Devart.Data.Linq.Mapping.ResultTypeAttribute';
- select ResultTypeAttribute(Type, Int32, ResultTypeOrigin) and move it to the Selected Attributes;
- in Constructor Parameters set 'origin' to "OutCursorParameter", 'type' to you return type (e.g., YourTypeResult), order to 0 (this is an ordinal number of a procedure parameter);
- save changes to your model.

After this, the ResultType attribute will be added to the mapping of your stored procedure in the *.Designer.cs file and it will not be lost after regenerating the code.

2) Use the way you have described: recreate procedure with the corresponding parameters order.

Please tell us if this helps.

[vbellema]

As I mentioned in my previous post make a manual change in the order parameters. I tried your suggestion to give the return value, but it causes an error in my application, so I kept the solution manual editing.

[MariiaI]

Please specify exactly what error you are getting.
We are sending you a small test project, which demonstrates adding the 'ResultType' attribute for the method, to the e-mail address you provided in your forum profile. Please check that the letter is not blocked by your mail filter.
Please tell us if this helps.