Error at retrieving database information

Apr 29, 2009 at 12:34 PM

While using the Entity Data Model Wizard, during the Retrieving database information, I was receiving a 'ORA-01427: single-row subquery returns more than one row' error message. To solve that, I modifeid the 'EFOracleStoreSchemaDefinition.ssdl' file, adding a OBJECT_TYPE comparison at "SchemaName" subqueries.

Like that:

    <EntitySet Name="SFunctions" EntityType="Self.Function">
      <DefiningQuery>
          SELECT
          '''' || ret.PACKAGE_NAME || '_' || func.OBJECT_NAME || '''' "Id"
          , CAST(NULL as varchar2(1)) "CatalogName"
          , (SELECT A.OWNER FROM ALL_OBJECTS A WHERE A.OBJECT_NAME = func.OBJECT_NAME AND A.OBJECT_TYPE = func.OBJECT_TYPE) "SchemaName"
          , func.OBJECT_NAME    "Name"
          , LOWER(ret.DATA_TYPE)"ReturnTypeName"
          , ret.DATA_LENGTH     "ReturnMaxLength"
          , ret.DATA_PRECISION "ReturnPrecision"
          , ret.DATA_PRECISION "ReturnDateTimePrecision"
          , ret.DATA_SCALE    "ReturnScale"
          , CAST(NULL as varchar2(1)) "ReturnCollationCatalog"
          , CAST(NULL as varchar2(1)) "ReturnCollationSchema"
          , CAST(NULL as varchar2(1)) "ReturnCollationName"
          , CAST(NULL as varchar2(1)) "ReturnCharacterSetCatalog"
          , CAST(NULL as varchar2(1)) "ReturnCharacterSetSchema"
          , ret.CHARACTER_SET_NAME "ReturnCharacterSetName"
          , 0                   "ReturnIsMultiSet"
          , 0                   "IsAggregate"
          , 0                   "IsBuiltIn"
          , 0                   "IsNiladic"
          FROM
          USER_PROCEDURES func
          INNER JOIN USER_OBJECTS obj ON obj.OBJECT_NAME = func.OBJECT_NAME
          AND obj.OBJECT_TYPE = 'FUNCTION'
          INNER JOIN USER_ARGUMENTS ret on ret.OBJECT_NAME = func.OBJECT_NAME
          AND ret.POSITION = 0
      </DefiningQuery>
    </EntitySet>

    <EntitySet Name="SProcedures" EntityType="Self.Procedure">
      <DefiningQuery>
          SELECT
          '''' || (CASE WHEN proc.PROCEDURE_NAME IS NULL THEN '' ELSE proc.OBJECT_NAME END) || '_'|| (CASE WHEN proc.PROCEDURE_NAME IS NULL THEN proc.OBJECT_NAME ELSE proc.PROCEDURE_NAME END) || '''' as "Id"
          , CAST(NULL as varchar2(1)) "CatalogName"
          ,(SELECT A.OWNER FROM ALL_OBJECTS A WHERE A.OBJECT_NAME = proc.OBJECT_NAME AND A.OBJECT_TYPE = PROC.OBJECT_TYPE) "SchemaName"
          , CASE WHEN proc.PROCEDURE_NAME IS NULL THEN proc.OBJECT_NAME ELSE proc.PROCEDURE_NAME END "Name"
          FROM
          USER_PROCEDURES proc
          INNER JOIN USER_OBJECTS obj ON proc.OBJECT_NAME = obj.OBJECT_NAME
          AND (obj.OBJECT_TYPE = 'PROCEDURE' OR obj.OBJECT_TYPE = 'PACKAGE')
      </DefiningQuery>
    </EntitySet>

Coordinator
May 5, 2009 at 11:25 AM
Hi, thanks for posting this issue. We'll investigate it and include fix in next release.