Oracle Bug

[AiKier]

SELECT OWNER, OBJECT_NAME, PROCEDURE_NAME, OBJECT_TYPE FROM ALL_PROCEDURES WHERE (OWNER = :OWNER OR :OWNER IS NULL) AND (OBJECT_NAME = :NAME OR :NAME IS NULL) AND (OBJECT_TYPE = 'PROCEDURE' OR OBJECT_TYPE = 'PACKAGE') AND NOT (PROCEDURE_NAME IS NULL AND OBJECT_TYPE = 'PACKAGE') AND OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM') In Oracle 10.2, Report errors：invalid identifier. In Oracle 10.2, ALL_PROCEDURES has no OBJECT_TYPE field.

In Oracle 11.2, the OBJECT_TYPE field is in ALL_PROCEDURES

[martinjw]

An old Oracle version! The fix to ProviderSchemaReaders\Databases\Oracle\StoredProcedures.cs

            if (Version(connectionAdapter.DbConnection) < 11)
            {
                //In Oracle 10.2, ALL_PROCEDURES has no OBJECT_TYPE field. The OBJECT_TYPE field is in DBA_OBJECTS.
                 Sql = @"SELECT P.OWNER,
  P.OBJECT_NAME,
  P.PROCEDURE_NAME,
  O.OBJECT_TYPE
FROM ALL_PROCEDURES P
INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = P.OBJECT_ID
WHERE (P.OWNER = :OWNER OR :OWNER IS NULL)
AND (P.OBJECT_NAME = :NAME OR :NAME IS NULL)
AND (O.OBJECT_TYPE = 'PROCEDURE' OR O.OBJECT_TYPE = 'PACKAGE')
AND NOT (P.PROCEDURE_NAME IS NULL AND O.OBJECT_TYPE = 'PACKAGE')
AND P.OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM')";
            }

[AiKier]

In 10.2, there is no OBJECT_ID in ALL_PROCEDURES.

[AiKier]

Maybe can try it. I ran successfully in 10.2.0.1.0

select P.OWNER, P.OBJECT_NAME, O.OBJECT_TYPE, P.PROCEDURE_NAME from ALL_PROCEDURES P left outer join ALL_OBJECTS O on ( O.OWNER = P.OWNER and O.OBJECT_NAME = P.OBJECT_NAME ) where ( (P.OWNER = :OWNER OR :OWNER IS NULL) AND (P.OBJECT_NAME = :NAME OR :NAME IS NULL) AND (O.OBJECT_TYPE = 'PROCEDURE' OR O.OBJECT_TYPE = 'PACKAGE') AND NOT (P.PROCEDURE_NAME IS NULL AND O.OBJECT_TYPE = 'PACKAGE') AND P.OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM') )