Q

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.

This Content Component encountered an error

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.
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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close