Stored Procedure for Oracle 10g return emtpy column

 Hi there,

There is strange thing happened when we use SP against Oracle 10g, a very simple select statement, first column is 'Id', along with other four columns.

It return right 'Id' value, but other column values are empty. Any thoughts?

Thanks in advance.

Lake.

I post SP here.

====================

CREATE OR REPLACE
PROCEDURE CUMETADATA (v_design_id  IN  CU_METADATA.DESIGN_ID%TYPE,
    v_design_cu_id IN CU_METADATA.DESIGNER_CU_ID%TYPE,
       v_work_location_id IN CU_METADATA.WORK_LOCATION_ID%TYPE,
                     results OUT SYS_REFCURSOR) AS
BEGIN
   OPEN results FOR
     SELECT
    CU_METADATA.DESIGN_ID AS Id,
    CU_METADATA.DESIGNER_CU_ID,
       CU_METADATA.WORK_LOCATION_ID,
       REPLACE(CU_METADATA.Name, '_', ' ') AS name_0,
       REPLACE(CU_METADATA.Value, 'E', '') AS value_0
     FROM
       PROCESS.CU_METADATA
     WHERE
       CU_METADATA.DESIGN_ID = v_design_id
         AND CU_METADATA.DESIGNER_CU_ID = v_design_cu_id
      AND CU_METADATA.WORK_LOCATION_ID = v_work_location_id;
END CUMETADATA; 

If the values other than Id are being returned as null (or the default value for that type) then it sounds like there may be an error with how the mapping is defined on the entity. Can you post the details for the entity you are mapping this into?

Thanks!

Jeremy

 Hi Jeremy,

I post the code here, please let me know what else you want. Thanks a lot!

================

[Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  public partial class CumetadataResult : Entity<int>
  {
    #region Fields
 
    private System.Nullable<decimal> _designerCuId;
    private string _workLocationId;
    private string _name0;
    private string _value0;

    #endregion
   
    #region Field attribute and view names
   
    /// <summary>Identifies the DesignerCuId entity attribute.</summary>
    public const string DesignerCuIdField = "DesignerCuId";
    /// <summary>Identifies the WorkLocationId entity attribute.</summary>
    public const string WorkLocationIdField = "WorkLocationId";
    /// <summary>Identifies the Name0 entity attribute.</summary>
    public const string Name0Field = "Name0";
    /// <summary>Identifies the Value0 entity attribute.</summary>
    public const string Value0Field = "Value0";

    #endregion
   
    #region Properties

    public System.Nullable<decimal> DesignerCuId
    {
      get { return Get(ref _designerCuId, "DesignerCuId"); }
      set { Set(ref _designerCuId, value, "DesignerCuId"); }
    }

    public string WorkLocationId
    {
      get { return Get(ref _workLocationId, "WorkLocationId"); }
      set { Set(ref _workLocationId, value, "WorkLocationId"); }
    }

    public string Name0
    {
      get { return Get(ref _name0, "Name0"); }
      set { Set(ref _name0, value, "Name0"); }
    }

    public string Value0
    {
      get { return Get(ref _value0, "Value0"); }
      set { Set(ref _value0, value, "Value0"); }
    }

    #endregion
  }

The names of the columns being returned from the stored procedure dont map to what are defined in the entity so this will be what is causing the empty values. You will either need to update the stored procedure to return the same names as are expected in the entity, for example you have:

CU_METADATA.DESIGN_ID AS Id

So similarly you would want to alias the other columns, e.g.

CU_METADATA.DESIGNER_CU_ID as DesignerCuId,
CU_METADATA.WORK_LOCATION_ID as WorkLocationId,
REPLACE(CU_METADATA.Name, '_', ' ') as Name0,
REPLACE(CU_METADATA.Value, 'E', '') as Value0

Alternatively if you can specify the column name if those are going to be the names you consistently select out when fetching data for that table by assigning the column name property for each entity property within the LightSpeed designer by selecting the entity property and then editing the column name within the properties pane.

Jeremy