我这里是能行的,你用的是oracle什么版本,你有可能没有DBMS_SQL package,或没有执行这个包的权限
SQL> create or replace procedure test
2 (
3 t_name varchar2,
4 t_filter varchar2,
5 t_sort varchar2) IS
6 v_cursor number;
7 v_str varchar2(50);
8 begin
9 v_cursor:=dbms_sql.open_cursor;
10 v_str:='select * from '||t_name||''' where '''||t_filter||''' order by '''|
|t_sort||'''';
11 begin
12 dbms_sql.parse(v_cursor,v_str,dbms_sql.v7);
13 dbms_sql.close_cursor(v_cursor);
14 exception
15 when others then
16 if sqlcode!=-942 then
17 raise;
18 end if;
19 end;
20 end test;
21
22 /过程已创建。SQL>
SQL> create or replace procedure test
2 (
3 t_name varchar2,
4 t_filter varchar2,
5 t_sort varchar2) IS
6 v_cursor number;
7 v_str varchar2(50);
8 begin
9 v_cursor:=dbms_sql.open_cursor;
10 v_str:='select * from '||t_name||''' where '''||t_filter||''' order by '''|
|t_sort||'''';
11 begin
12 dbms_sql.parse(v_cursor,v_str,dbms_sql.v7);
13 dbms_sql.close_cursor(v_cursor);
14 exception
15 when others then
16 if sqlcode!=-942 then
17 raise;
18 end if;
19 end;
20 end test;
21
22 /过程已创建。SQL>
begin
test('classes','num_credits=4','course');
end;varchar参数需要用单引号括起来!
还不行
SQL>
SQL> begin
2 test('classes','num_credits=4','course');
3 end;
4 /
begin
*
ERROR 位于第1行:
ORA-06502: PL/SQL:数字或值错误
ORA-06512: 在"BBGISPROD.TEST", line 10
ORA-06512: 在line 2
String Selecttmp="select :xlmc from sjglzxt.ST_XL_GTTZ";
OracleDataAdapter mytjCommand=new OracleDataAdapter(Selecttmp,myConnection);
mytjCommand.SelectCommand.Parameters.Add(new OracleParameter("xlmc",OracleType.NChar,10));
mytjCommand.SelectCommand.Parameters["xlmc"].Value=attribute.SelectedItem.Value;
DataSet dstj=new DataSet();
mytjCommand.Fill(dstj,"tj");value.DataSource=dstj.Tables["tj"].DefaultView;
value.DataBind();
test('classes','num_credits=4','course');
若是字符型:
test('classes','num_credits=''4''','course');
v_str:='select * from '||t_name||''' where '''||t_filter||''' order by ''' || t_sort || '''';应该是
v_str:='select * from '||t_name||' where '||t_filter||' order by ' || t_sort ;2. 你那样使用变量应该是可以的。
SQL>
SQL> begin
2 test('classes','num_credits=4','course');
3 end;
4 /
begin
*
ERROR 位于第1行:
ORA-06502: PL/SQL:数字或值错误
ORA-06512: 在"BBGISPROD.TEST", line 10
ORA-06512: 在line 2
然后再执行
SQL> begin
2 test('classes','num_credits=4','course');
3 end;
4 /看看打印出来的SQL是什么!
1 create or replace procedure test
2 (
3 t_name varchar2,
4 t_filter varchar2,
5 t_sort varchar2) IS
6 v_cursor number;
7 v_str varchar2(50);
8 begin
9 v_cursor:=dbms_sql.open_cursor;
10 v_str:='select * from '||t_name||' where '||t_filter||' order by ' || t_sort ;
11 begin
12 DBMS_OUTPUT.PUT_LINE('SQL: '||v_str);
13 dbms_sql.parse(v_cursor,v_str,dbms_sql.v7);
14 dbms_sql.close_cursor(v_cursor);
15 exception
16 when others then
17 if sqlcode!=-942 then
18 raise;
19 end if;
20 end;
21* end test;
SQL> /过程已创建。SQL> set serveroutput on size 1000000
SQL> begin
2 test('classes','num_credits=4','course');
3 end;
4 /
begin
*
ERROR 位于第1行:
ORA-06502: PL/SQL:数字或值错误
ORA-06512: 在"BBGISPROD.TEST", line 10
ORA-06512: 在line 2
begin不能去掉
1 create or replace procedure test
2 (
3 t_name varchar2,
4 t_filter varchar2,
5 t_sort varchar2) IS
6 v_cursor number;
7 v_str varchar2(50);
8 begin
9 v_cursor:=dbms_sql.open_cursor;
10 v_str:='select * from '||t_name||' where '||t_filter||' order by ' || t_sort ;
11 DBMS_OUTPUT.PUT_LINE('SQL: '||v_str);
12 dbms_sql.parse(v_cursor,v_str,dbms_sql.v7);
13 dbms_sql.close_cursor(v_cursor);
14 exception
15 when others then
16 if sqlcode!=-942 then
17 raise;
18 end if;
19 end;
20* end test;
SQL> /警告:已创建的过程出现编译错误。SQL> show error
PROCEDURE TEST出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
20/1 PLS-00103: Encountered the symbol "END"
难道你在pl/sql developer中不能单步跟踪调试吗,这样发现问题不是很easy吗?