SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: PL/SQL compiling problem...
-
Aug 7, 2003, 14:49 #1
- Join Date
- Aug 2002
- Location
- Ottawa, Ontario, Canada
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
PL/SQL compiling problem...
Maybe one of you can open my eyes to what is going on.
I have a procedure I wrote that won't compile unless it is dynamic SQL. The statement works. I know it does since I tested it with hard-coded values in SQL*Plus worksheet before I wrote the proc. But for some reason Oracle won't compile the statement.
Code:PROCEDURE sgovmrgn3(p_stab_yy IN scc_gov_mrgn_nsr.stab_yy%TYPE, p_prov_code IN scc_gov_mrgn_nsr.prov_code%TYPE, p_prov_mncpty_code IN scc_gov_mrgn_nsr.prov_mncpty_code%TYPE, p_gov_mrgn_type_code IN scc_gov_mrgn_nsr.gov_mrgn_type_code%TYPE, p_gov_mrgn_code IN scc_gov_mrgn_nsr.gov_mrgn_code%TYPE, p_non_cal_op IN NUMBER, p_gov_mrgn_amt IN OUT scc_cur) IS v_mncpty scc_gov_mrgn_nsr.prov_mncpty_code%TYPE; v_non_cal NUMBER; BEGIN IF NOT use_mncpty_in_select(p_stab_yy,p_prov_code,p_prov_mncpty_code,p_gov_mrgn_type_code,p_gov_mrgn_code) THEN v_mncpty:=0; ELSE v_mncpty:=p_prov_mncpty_code; END IF; IF p_non_cal_op <> 0 THEN v_non_cal:=0; ELSE v_non_cal:=1; END IF; -- this block works -- remove double dashes below to comment out block --/* OPEN p_gov_mrgn_amt FOR ' SELECT inner.gov_inv_mrgn_amt ' ||' FROM (SELECT gov_inv_mrgn_amt,' ||' ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn' ||' FROM scc_gov_mrgn_nsr' ||' WHERE stab_yy = p_stab_yy' ||' AND prov_code = p_prov_code' ||' AND prov_mncpty_code = p_prov_mncpty_code' ||' AND gov_mrgn_type_code = p_gov_mrgn_type_code' ||' AND gov_mrgn_code = p_gov_mrgn_code) inner' ||' WHERE inner.rn ' ||'BETWEEN 1+v_nc ' ||' AND 5+v_nc ' USING v_non_cal, v_non_cal; --*/ -- this block won't compile... WHY? -- this block will work in SQL*Plus worksheet -- add double dashes below to enable block /* SELECT inner.gov_inv_mrgn_amt FROM (SELECT gov_inv_mrgn_amt, ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn FROM scc_gov_mrgn_nsr WHERE stab_yy = p_stab_yy AND prov_code = p_prov_code AND prov_mncpty_code = p_prov_mncpty_code AND gov_mrgn_type_code = p_gov_mrgn_type_code AND gov_mrgn_code = p_gov_mrgn_code) inner WHERE inner.rn BETWEEN 1+v_non_cal AND 5+v_non_cal; --*/ END sgovmrgn3;
Code:LINE/COL ERROR -------- ----------------------------------------------------------------- 205/41 PLS-00103: Encountered the symbol "(" when expecting one of the following: , from
Code:column text format a50 select text from user_source where name = upper('scc_strct_chnge') and type = upper('package body') and line = 205;
Code:TEXT -------------------------------------------------- ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn
Cheers,
Keith.
-
Aug 8, 2003, 03:46 #2
- Join Date
- Oct 2001
- Location
- location location
- Posts
- 89
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'd define an explicit cursor, IN PL/SQL, you can't just put a select statement, there has to be some sort of cursur, either implicit or explicit.
Hope this helps a little, if you need more info, say and post the code again with returns :-)
-
Aug 8, 2003, 06:08 #3
- Join Date
- Aug 2002
- Location
- Ottawa, Ontario, Canada
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
It isn't the cursor at issue. Yes, I would use a cursor for the second block, but that won't prevent the compile from failing.
New version of the proc:
Code:PROCEDURE sgovmrgn3(p_stab_yy IN scc_gov_mrgn_nsr.stab_yy%TYPE, p_prov_code IN scc_gov_mrgn_nsr.prov_code%TYPE, p_prov_mncpty_code IN scc_gov_mrgn_nsr.prov_mncpty_code%TYPE, p_gov_mrgn_type_code IN scc_gov_mrgn_nsr.gov_mrgn_type_code%TYPE, p_gov_mrgn_code IN scc_gov_mrgn_nsr.gov_mrgn_code%TYPE, p_non_cal_op IN NUMBER, p_gov_mrgn_amt IN OUT scc_cur) IS v_mncpty scc_gov_mrgn_nsr.prov_mncpty_code%TYPE; v_non_cal PLS_INTEGER; BEGIN IF NOT use_mncpty_in_select(p_stab_yy, p_prov_code, p_prov_mncpty_code, p_gov_mrgn_type_code, p_gov_mrgn_code) THEN v_mncpty:=0; ELSE v_mncpty:=p_prov_mncpty_code; END IF; IF p_non_cal_op <> 0 THEN v_non_cal:=0; ELSE v_non_cal:=1; END IF; OPEN p_gov_mrgn_amt FOR -- This works --/* SELECT middle.gov_inv_mrgn_amt FROM (SELECT inner.*, ROWNUM AS rn FROM (SELECT gov_inv_mrgn_amt FROM scc_gov_mrgn_nsr WHERE stab_yy = p_stab_yy AND prov_code = p_prov_code AND prov_mncpty_code = v_mncpty AND gov_mrgn_type_code = p_gov_mrgn_type_code AND gov_mrgn_code = p_gov_mrgn_code ORDER BY stab_yy ASC ) inner ) middle WHERE middle.rn BETWEEN 1 + v_non_cal AND 5 + v_non_cal; --*/ -- this block won't compile... WHY? -- this block will work in SQL*Plus worksheet -- the compile error centres around the -- (ORDER BY stab_yy ASC) subclause -- add double dashes below to enable block /* SELECT inner.gov_inv_mrgn_amt FROM (SELECT gov_inv_mrgn_amt, ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn FROM scc_gov_mrgn_nsr WHERE stab_yy = p_stab_yy AND prov_code = p_prov_code AND prov_mncpty_code = p_prov_mncpty_code AND gov_mrgn_type_code = p_gov_mrgn_type_code AND gov_mrgn_code = p_gov_mrgn_code) inner WHERE inner.rn BETWEEN 1+v_non_cal AND 5+v_non_cal; --*/ END sgovmrgn3;
The proc works as noted above, but it still bugs me that the simpler version won't compile.
Cheers,
Keith.
-
Aug 8, 2003, 07:16 #4
- Join Date
- Oct 2001
- Location
- location location
- Posts
- 89
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
The code with returns would still be nice ;-) If you post it with returns, I'll be happy to have a good think, where now I'm just guessing !
I'd take the line number Oracle gives you with a pinch of salt, the source will hold the comments, lines which are completely blank and other stuff, but when oracle gives the line number in errors, it will usually strip them.
-
Aug 8, 2003, 08:50 #5
- Join Date
- Aug 2002
- Location
- Ottawa, Ontario, Canada
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I have always been under the impression that selecting based on line number from user_source returns the correct line..?
In any case, I got an answer that I think fits with what is occurring:
Answer at dbasupport.com
Thanks for the replies though
Cheers,
Keith.
Bookmarks