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 endedI realize I'm fairly new to Oracle, but this shouldn't be that difficult! Thanks for any help you can provide.
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.
Dig Deeper on Oracle and SQL
Related Q&A from Mike Ault, Senior Oracle Consultant, Burleson Consulting
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to...continue reading
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists.continue reading
We're using the following string to connect to the Oracle DB Server through the OraOLEDB Driver. We want to read WSID from this string in the ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.