Reputation: 11
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