CREATE OR REPLACE PROCEDURE P_1(iv_ID integer)
is
v_sql VARCHAR (4096);
o_ret integer;
o_err varchar2(4096);
BEGIN
v_sql :=
'Create Table temp_fox AS Select * from T_AAA a where a.ID='
|| iv_ID
|| ';'; EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS
THEN
o_ret := -80;
o_err := v_sql; --SUBSTR (SQLERRM, 1, 200);
RETURN;
END;
is
v_sql VARCHAR (4096);
o_ret integer;
o_err varchar2(4096);
BEGIN
v_sql :=
'Create Table temp_fox AS Select * from T_AAA a where a.ID='
|| iv_ID
|| ';'; EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS
THEN
o_ret := -80;
o_err := v_sql; --SUBSTR (SQLERRM, 1, 200);
RETURN;
END;
是这里出了错,那个";"不能加上,SQL语句中是没有";"的,那是pl/sql的语句结束符
前两天刚刚有人问过
v_sql VARCHAR (4096);
iv_ID number:=2;
o_ret number:=2;
o_err VARCHAR (4096);
BEGIN
v_sql :=
'Create Table temp_fox AS Select * from T_AAA a where a.ID='
|| iv_ID;
EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS
THEN
o_ret := -80;
o_err := v_sql; --SUBSTR (SQLERRM, 1, 200);
RETURN;
END;
2 v_sql VARCHAR (4096);
3 iv_ID number:=2;
4 o_ret number:=2;
5 o_err VARCHAR (4096);
6 BEGIN
7 v_sql :=
8 'Create Table temp_fox AS Select * from T_AAA a where a.ID='
9 || iv_ID;
10 EXECUTE IMMEDIATE v_sql;
11 commit;
12 EXCEPTION
13 WHEN OTHERS
14 THEN
15 o_ret := -80;
16 o_err := v_sql; --SUBSTR (SQLERRM, 1, 200);
17 RETURN;
18* END;
19 /PL/SQL 过程已成功完成。