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.

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;

1 Answer

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

• Doing a select * is almost always a bad idea. Particularly when you are joining multiple tables. Do you really, really want the structure of the result set to change every time someone adds an additional column to any of three tables? That seems unlikely.
• Use aliases everywhere. Looking at the code, I have no idea what table titre comes from. Or nom or prenum or typearticle. Use aliases to identify which table a column comes from. That makes your code clearer and makes it more robust when additional tables or columns are added in the future which may have the same column names (multiple entities might have a nom column for example).