QUESTION POSED ON: 27 January 2006
I'm having a problem with dynamic SQL, specifically getting my incoming
parameter into the SQL statement. I am using Oracle 9i and this
procedure will be part of a package. I have a .NET front end that
will generate the WHERE portion of the statement and pass it in.
Here's the code:
SQL> CREATE OR REPLACE PROCEDURE p_Report_Records(sWhere_in IN VARCHAR2)
2 IS
3 CURSOR curGetRecs
4 IS
5 SELECT InstCode, InstructCode, ProgTitle, DegCode, CIP,
6 SUBSTR(CIP,1,2) CIP2,
7 NVL(Deg_Year1, 0) Deg_Year1,
8 NVL(Deg_Year2, 0) Deg_Year2,
9 NVL(Deg_Year3, 0) Deg_Year3,
10 NVL(Deg_Year4, 0) Deg_Year4,
11 NVL(Deg_Year5, 0) Deg_Year5,
12 (Deg_Year1+Deg_Year2+Deg_Year3+Deg_Year4+Deg_Year5) Degree_Total,
13 (Deg_Year1+Deg_Year2+Deg_Year3+Deg_Year4+Deg_Year5/5)
Degree_5YrAvg,
14 NVL(Enr_Year1, 0) Enr_Year1,
15 NVL(Enr_Year2, 0) Enr_Year2,
16 NVL(Enr_Year3, 0) Enr_Year3,
17 NVL(Enr_Year4, 0) Enr_Year4,
18 NVL(Enr_Year5, 0) Enr_Year5,
19 (Enr_Year1+Enr_Year2+Enr_Year3+Enr_Year4+Enr_Year5) Enroll_Total,
20 (Enr_Year1+Enr_Year2+Enr_Year3+Enr_Year4+Enr_Year5/5)
Enroll_5YrAvg,
21 LoPro, InstName, AcadYear
22 FROM ods.v_5YearProductivity; -- || sWHERE_in;
23
24 v_Prod5 ODS.PRODUCTIVITY_5_YEARS%ROWTYPE;
25
26 BEGIN
27 OPEN curGetRecs;
28 FETCH curGetRecs INTO v_Prod5;
29 WHILE curGetRecs%FOUND
30 LOOP
31 INSERT INTO ODS.PRODUCTIVITY_5_YEARS
32 VALUES v_Prod5;
33 END LOOP;
34 CLOSE curGetRecs;
This compiles with the WHERE statement commented out (line 22), but when
I include it, I get the following two error messages:
LINE/COL ERROR
-------- --------------------------------------------------
5/3 PL/SQL: SQL Statement ignored
22/32 PL/SQL: ORA-00933: SQL command not properly ended
I realize I'm fairly new to Oracle, but this shouldn't be that
difficult! Thanks for any help you can provide.
|
