以下是代码:
SQL> create or replace PROCEDURE sp_tmp_test(NAME IN VARCHAR2, ages IN INT) AS
2 v_num INT;
3 v_name VARCHAR2(15);
4 BEGIN
5 SELECT COUNT(1) INTO v_num FROM employee WHERE dept = '&no';
6 --create temp table
7 EXECUTE IMMEDIATE 'create global temporary table tem_name(id raw(16) primary key,name varchar2(15),ages int) on commit delete rows';
8
9 EXECUTE IMMEDIATE 'insert into tem_name values(sys_guid(),:name,:ages)' USING NAME, ages;
10
11 EXECUTE IMMEDIATE 'select name from tem_name' INTO v_name;
12
13 dbms_output.put_line(v_name);
14
15 COMMIT;
16
17 END;
18 /Procedure createdSQL> show errors;
No errors for PROCEDURE DUANZH.SP_TMP_TESTSQL> exec sp_tmp_test('jake',34);begin sp_tmp_test('jake',34); end;ORA-01031: insufficient privileges
ORA-06512: at "DUANZH.SP_TMP_TEST", line 7
ORA-06512: at line 1是权限不足么,但是我单独在SQL或者Command Window中创建临时表是没有问题的,哪里出错了?
SQL> create or replace PROCEDURE sp_tmp_test(NAME IN VARCHAR2, ages IN INT) AS
2 v_num INT;
3 v_name VARCHAR2(15);
4 BEGIN
5 SELECT COUNT(1) INTO v_num FROM employee WHERE dept = '&no';
6 --create temp table
7 EXECUTE IMMEDIATE 'create global temporary table tem_name(id raw(16) primary key,name varchar2(15),ages int) on commit delete rows';
8
9 EXECUTE IMMEDIATE 'insert into tem_name values(sys_guid(),:name,:ages)' USING NAME, ages;
10
11 EXECUTE IMMEDIATE 'select name from tem_name' INTO v_name;
12
13 dbms_output.put_line(v_name);
14
15 COMMIT;
16
17 END;
18 /Procedure createdSQL> show errors;
No errors for PROCEDURE DUANZH.SP_TMP_TESTSQL> exec sp_tmp_test('jake',34);begin sp_tmp_test('jake',34); end;ORA-01031: insufficient privileges
ORA-06512: at "DUANZH.SP_TMP_TEST", line 7
ORA-06512: at line 1是权限不足么,但是我单独在SQL或者Command Window中创建临时表是没有问题的,哪里出错了?
在存储过程中需要显示授权,需要执行grant create any table to 用户;提醒下楼主: 结帖率:0.00%
lz可改为:
create or replace PROCEDURE sp_tmp_test(NAME IN VARCHAR2, ages IN INT)
authid current_user
as
v_num INT;
.....
即可。2.这样的过程只能执行一次,下次执行除非把临时表删了,否则就不能再执行了。
学习了!~