0

I'm an Oracle newbie (SQLServer background) and I'm having a heck of a time creating a stored procedure to return a record set. The below code [names obfuscated, they're named better than that in real life...] seems to match other examples I've seen here and elsewhere, but Aquadata returns an error for "FAILED to execute EXPLAIN plan: ORA-00900: invalid SQL statement" when I run the CREATE. When I try to execute the stored procedure, I get "00905 - missing keyword".

For the life of me, I can't see the syntax error. Any help is greatly appreciated.

CREATE OR REPLACE PROCEDURE P_MyProc (
p_id in number,
prc out SYS_REFCURSOR
)
IS
BEGIN
    OPEN prc FOR
        SELECT mm.fld1, mm.fld2, mm.fld3, mm.fld4, mm.fld5, mm.fld6, mm.fld7, mm.fld8, mm.fld9, loa.fld1 FROM DB.tbl1 mm
        INNER JOIN db2.tbl2 loa ON mm.fld1 = loa.fld1
        WHERE mm.fld5 = '44,53' AND mm.fld1 =p_id;
 
END P_MyProc;
Aqib Chattha
  • 197
  • 11
Ross
  • 11
  • 4
  • 1
    SQL and PL/ SQL are different, and you can't get an explain plan for PL/SQL. It isn't clear if that's happening automatically or if you're running this incorrectly. Anyway, [does this help](https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation16/page/10/Procedure-Function-Package-Editor)? – Alex Poole Sep 01 '23 at 21:44
  • Well, the LINK didn't help but your answer did lead me to the source of the error - for some reason Aquadata was setup to always show an execution plan / explain table. When I got rid of that, the errors cleared up. Thanks! – Ross Sep 01 '23 at 23:08

0 Answers0