CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-procedures
user3106601
user3106601

Reputation: 11

procedure Error on Oracle DB

Hi all i'm trying to create the a procedure on Oracle DB, but i'm getting 2 Erros:

Error(5,5): PL/SQL: SQL Statement ignored
Error(6,20): PL/SQL: ORA-00923: FROM keyword not found where expected

Here is my CODE:

create or replace PROCEDURE Nir2 
IS
BEGIN
    select  DISTINCT AREP.CATALOG_TAG as REPOSITORY, UPPER(AREP.maindb), UPPER(MREP.SCHEMANAME), MREP.MACHINE, ctime AS CREATEDATE, TO_CHAR(MREP.LOGON_TIME, 'DD-MON-YYYY') AS LASTLOGON,
          IF  UPPER(a2i_xcat_dbs.a2i_serverstatus.CATALOG_TAG ) IS UPPER( a2i_xcat_dbs.a2i_catalogs.CATALOG_TAG) 
            THEN
              'LOAD' AS MATCH
          ELSE IF  UPPER(MREP.SCHEMANAME) IS UPPER(AREP.maindb)
            THEN
              'MOUNT' AS MATCH
            END
      from a2i_xcat_dbs.a2i_serverstatus LREP, v$session MREP, a2i_xcat_dbs.a2i_catalogs AREP, SYS.USER$ S
      where UPPER(MREP.SCHEMANAME) = UPPER(AREP.maindb) AND MREP.SCHEMANAME <> 'SYSMAN'
        AND MREP.LOGON_TIME >= ALL (select MREP1.LOGON_TIME FROM v$session MREP1 where MREP.SCHEMANAME=MREP1.SCHEMANAME  )
ORDER BY REPOSITORY, CREATEDATE;
END;

EDIT:

Here is my new code:

create or replace PROCEDURE Nir2 
IS
BEGIN
    select  DISTINCT AREP.CATALOG_TAG as REPOSITORY, UPPER(AREP.maindb), UPPER(MREP.SCHEMANAME), MREP.MACHINE, ctime AS CREATEDATE, TO_CHAR(MREP.LOGON_TIME, 'DD-MON-YYYY') AS LASTLOGON,
          CASE WHEN ( UPPER(a2i_xcat_dbs.a2i_serverstatus.CATALOG_TAG ) = UPPER( a2i_xcat_dbs.a2i_catalogs.CATALOG_TAG) )
            THEN
              'LOAD'
          WHEN ( UPPER(MREP.SCHEMANAME) = UPPER(AREP.maindb))
            THEN
              'MOUNT'
            END AS MATCH
      from a2i_xcat_dbs.a2i_serverstatus LREP, v$session MREP, a2i_xcat_dbs.a2i_catalogs AREP, SYS.USER$ S
      where UPPER(MREP.SCHEMANAME) = UPPER(AREP.maindb) AND MREP.SCHEMANAME <> 'SYSMAN'
        AND MREP.LOGON_TIME >= (select MREP1.LOGON_TIME FROM v$session MREP1 where MREP.SCHEMANAME=MREP1.SCHEMANAME  )
ORDER BY REPOSITORY, CREATEDATE;
END;

still i'm getting the following Errors:

Error(5,5): PL/SQL: SQL Statement ignored
Error(6,82): PL/SQL: ORA-00904: "A2I_XCAT_DBS"."A2I_CATALOGS"."CATALOG_TAG": invalid identifier

Upvotes: 0

Views: 144

Answers (2)

Alen Oblak
Alen Oblak

Reputation: 3325

You have at least two errors in your procedure.

  1. IF statement is not allowed in the select statement. Use CASE instead.

  2. You must include INTO clause in your select statement, example:

    create or replace my_procedure
IS
   l_value1 varchar2(200);
   l_value2 varchar2(200);
BEGIN
   select 'value 1', 'value 2'
   into   l_value1, l_value2
   from   dual;
 END;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52336

You can't do "IF" in SQL. Instead, do:

Case
  when UPPER(a2i_xcat_dbs.a2i_serverstatus.CATALOG_TAG ) = UPPER( a2i_xcat_dbs.a2i_catalogs.CATALOG_TAG) 
    THEN 'LOAD'
  when UPPER(MREP.SCHEMANAME) = UPPER(AREP.maindb)
    THEN 'MOUNT'
END MATCH

http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm#SQLRF20037

Edit:

this ...

MREP.LOGON_TIME >= ALL (select MREP1.LOGON_TIME FROM v$session MREP1 where MREP.SCHEMANAME=MREP1.SCHEMANAME  )

... might be better as ...

MREP.LOGON_TIME >= (select MAX(MREP1.LOGON_TIME) FROM v$session MREP1 where MREP.SCHEMANAME=MREP1.SCHEMANAME  )

Upvotes: 2

Related Questions