您好,请问在存储过程中创建的临时表为什么无法访问?
包:
CREATE OR REPLACE PACKAGE pkg_n_rights
AS
TYPE n_rights IS REF CURSOR;
PROCEDURE pro_n_getrights(pageIndex NUMBER, pageMax NUMBER, pageCount OUT NUMBER, roles OUT n_rights);
END pkg_n_rights;包体:
CREATE OR REPLACE PACKAGE BODY pkg_n_rights
AS
PROCEDURE pro_n_getrights(
pageIndex NUMBER,
pageMax NUMBER,
pageCount OUT NUMBER,
roles OUT n_rights)
AS
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE n_tempright(
id NUMBER,
rightid NUMBER,
name VARCHAR2(128))
ON COMMIT PRESERVE ROWS'; INSERT INTO n_tempright(rightid, name) SELECT id, name FROM RIGHT;
OPEN n_roles FOR
SELECT rightid, name FROM n_tempright
WHERE id BETWEEN pageMax*(pageIndex-1)+1 AND pageMax*pageInde
ORDER BY rightid; SELECT COUNT(*) INTO v_count FROM n_tempright;
pageCount := CASE WHEN MOD(v_count,pageMax) = 0 THEN pageCount/pageMax
ELSE v_count/pageMax+1 END;
EXECUTE IMMEDIATE 'DROP TABLE n_tempright'; EXCEPTION
WHEN others THEN
EXECUTE IMMEDIATE 'DROP TABLE n_tempright'; END;
END pkg_n_rights;RIGTH表
CREATE TABLE "RIGHT"(
id NUMBER PRIMARY KEY,
name VARCHAR2(128) NOT NULL)报错:
PL/SQL: SQL Statement ignored
17/21 PL/SQL: ORA-00942: 表或视图不存在
18/9 PL/SQL: SQL Statement ignored
18/14 PLS-00201: 必须说明标识符 'N_ROLES'
23/9 PL/SQL: SQL Statement ignored
23/43 PL/SQL: ORA-00942: 表或视图不存在您好,请问是否创建了临时表,就无法访问?这种游标是这样定义的吗?谢谢。
包:
CREATE OR REPLACE PACKAGE pkg_n_rights
AS
TYPE n_rights IS REF CURSOR;
PROCEDURE pro_n_getrights(pageIndex NUMBER, pageMax NUMBER, pageCount OUT NUMBER, roles OUT n_rights);
END pkg_n_rights;包体:
CREATE OR REPLACE PACKAGE BODY pkg_n_rights
AS
PROCEDURE pro_n_getrights(
pageIndex NUMBER,
pageMax NUMBER,
pageCount OUT NUMBER,
roles OUT n_rights)
AS
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE n_tempright(
id NUMBER,
rightid NUMBER,
name VARCHAR2(128))
ON COMMIT PRESERVE ROWS'; INSERT INTO n_tempright(rightid, name) SELECT id, name FROM RIGHT;
OPEN n_roles FOR
SELECT rightid, name FROM n_tempright
WHERE id BETWEEN pageMax*(pageIndex-1)+1 AND pageMax*pageInde
ORDER BY rightid; SELECT COUNT(*) INTO v_count FROM n_tempright;
pageCount := CASE WHEN MOD(v_count,pageMax) = 0 THEN pageCount/pageMax
ELSE v_count/pageMax+1 END;
EXECUTE IMMEDIATE 'DROP TABLE n_tempright'; EXCEPTION
WHEN others THEN
EXECUTE IMMEDIATE 'DROP TABLE n_tempright'; END;
END pkg_n_rights;RIGTH表
CREATE TABLE "RIGHT"(
id NUMBER PRIMARY KEY,
name VARCHAR2(128) NOT NULL)报错:
PL/SQL: SQL Statement ignored
17/21 PL/SQL: ORA-00942: 表或视图不存在
18/9 PL/SQL: SQL Statement ignored
18/14 PLS-00201: 必须说明标识符 'N_ROLES'
23/9 PL/SQL: SQL Statement ignored
23/43 PL/SQL: ORA-00942: 表或视图不存在您好,请问是否创建了临时表,就无法访问?这种游标是这样定义的吗?谢谢。
我的解决方法是意思到2点:
1.Oracle中的临时表不要随意删除,因而无需每次建立会话的时候创建临时表;只有在临时表不存在的时候(Oracle崩溃的时候?)才创建临时表;
2.创建临时表在PL/SQL中,需要使用EXECUTE IMMEDIATE,同时对此临时表
的所有操作都需要使用EXECUTE IMMEDIATE?才能不出错
3.权限我已经为它分配了DBA以及CREATE ANY TABLE,其他ANY TABLE的系统权限。所以权限应该是需要分配的。谢谢各位。