Ask the Expert

Problem with incoming parameter in dynamic SQL statement

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.

    Requires Free Membership to View

Maybe this example will help:
create or replace function get_count (tab_name varchar2) return number as
cur integer;
sql_text varchar2(200);
rowcount NUMBER;
row_proc integer;
BEGIN
        cur:=dbms_sql.open_cursor;
        sql_text:='select count(*) X from '||tab_name;
DBMS_OUTPUT.PUT_LINE(SQL_TEXT);
        dbms_sql.parse(cur,sql_text,dbms_sql.v7);
        dbms_sql.define_column(cur,1,rowcount);
        row_proc:=dbms_sql.execute(cur);
        dbms_sql.column_value(cur,1,rowcount);
DBMS_OUTPUT.PUT_LINE('ROW_COUNT:'||TO_CHAR(ROWCOUNT));
        return rowcount;
end;
/
You have to send the SQL text to be created as a CURSOR. You can't start with a cursor and then modify the statement.

This was first published in January 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: