sqloracle-databaseoracle-apex

Oracle Apex: ORA-24344 Success with compilation error

I am currently working on an oracle database in APEX.

First of all, I do not have access to any tools. I can only use what is provided in apex.oracle.com and I am writing the script in NotePad++ before uploading it over there. It's a school assignment, so I am not allowed to use any other tools, even if there is something that would make things easier.

I am creating a long script that creates a bunch of tables in a database, creates a bunch of records in every table, and creates all the constraints. This part works fine. Now i must create several functions and procedures in the same script, after the other stuff.

Every single time I try to create one, I get this: ORA-24344: success with compilation error

Also, the other instructions in the script after this error are not executed. Everything that was before the error works fine.

http://i.imgur.com/lHIjfcE.png

Here is one of the functions that create this error:

CREATE OR REPLACE FUNCTION SP_03Recherche (titre_art VARCHAR2, nom_aut VARCHAR2, type_art VARCHAR2)
RETURN CURSOR
IS
    CURSOR articles (p_titre_art VARCHAR2, p_nom_aut VARCHAR2, p_type_art VARCHAR2) IS
    SELECT * FROM BI_Articles INNER JOIN (BI_ArticlesAuteurs INNER JOIN BI_Auteurs ON BI_ArticlesAuteurs.AuteurID = BI_Auteurs.AuteurID) ON BI_Articles.ISBN = BI_ArticlesAuteurs.ISBN
    WHERE (Titre LIKE p_titre_art) AND ((Nom LIKE p_nom_aut) OR (Prenom LIKE p_nom_aut)) AND TypeArticle LIKE type_art;
BEGIN
    RETURN articles(titre_art, nom_aut, type_art);
END;
Solution

  • sys_refcursor is the data type that you seemingly want to return. That's a generic type for a weak ref cursor. My guess is that you want something like

    CREATE OR REPLACE FUNCTION SP_03Recherche (
  p_titre_art VARCHAR2, 
  p_nom_aut VARCHAR2, 
  p_type_art VARCHAR2
)
  RETURN sys_refcursor;
IS
  l_rc sys_refcursor;
BEGIN
  OPEN l_rc
   FOR select *
         from bi_articles art
              inner join BI_ArticlesAuteurs art_auth
                 on (art.isbn = art_auth.isbn)
              inner join BI_Auteurs auth
                 on (art_auth.auteurID = auth.auteurID)
        where titre LIKE p_titre_art
          and (nom LIKE p_nom_auth or
               prenum LIKE p_nom_auth)
          and typearticle LIKE type_art;

  RETURN l_rc;
END;

    Now, a few suggestions