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
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
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
I want to find the length of a numeric datatype field in my table. How can I find it?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.