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.

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 last published in January 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close