Ross
Ross

Reputation: 11

AquaData Oracle create procedure error 00900

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;

Upvotes: 0

Views: 32

Answers (0)

Related Questions