plsql:创建函数报错!错误信息:ORA-01031: 权限不足

majormarshal 2005-01-05 11:32:12
我要在sql*plus 里面创建一个函数 :

CREATE OR REPLACE FUNCTION AverageGrade (
p_Department IN VARCHAR2,
p_Course IN NUMBER) RETURN VARCHAR2 AS
v_AverageGrade VARCHAR2(1);
v_NumericGrade NUMBER;
v_NumberStudents NUMBER;

CURSOR c_Grades IS
SELECT grade
FROM registered_students
WHERE department = p_Department
AND course = p_Course;

BEGIN
Debug.Reset;
Debug.Debug('p_Department', p_Department);
Debug.Debug('p_Course', p_Course);

SELECT COUNT(*)
INTO v_NumberStudents
FROM registered_students
WHERE department = p_Department
AND course = p_Course;

Debug.Debug('After select, v_NumberStudents', v_NumberStudents);

IF v_NumberStudents = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
p_Department || ' ' || p_Course);
END IF;

SELECT AVG(DECODE(grade, 'A', 5,
'B', 4,
'C', 3,
'D', 2,
'E', 1))
INTO v_NumericGrade
FROM registered_students
WHERE department = p_Department
AND course = p_Course;

SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
4, 'B',
3, 'C',
2, 'D',
1, 'E')
INTO v_AverageGrade
FROM dual;

RETURN v_AverageGrade;
END AverageGrade;

执行报错:ORA-01031: 权限不足

我登录sql*plus 使用的登录名称和口令都是我自己建的库的名称和口令。
建库语句如下:
CREATE TABLESPACE PLSQL_EXEMPLE DATAFILE 'PLSQL_EXEMPLE.ora' SIZE 10M;
CREATE USER "PLSQL" IDENTIFIED BY "TEST" DEFAULT TABLESPACE "PLSQL_EXEMPLE" TEMPORARY TABLESPACE "TEMPORARY_DATA" PROFILE DEFAULT QUOTA UNLIMITED ON "PLSQL_EXEMPLE" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PLSQL";
ALTER USER "PLSQL" DEFAULT ROLE ALL;

请教是何问题?该如何解决?
...全文
546 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
majormarshal 2005-01-06
问题已经解决,谢谢大家。
建库语句如下:
CREATE TABLESPACE plsql_exemple DATAFILE 'PLSQL_EXEMPLE.ora' SIZE 10 m;
CREATE USER "PLSQL" IDENTIFIED BY "TEST" DEFAULT TABLESPACE "PLSQL_EXEMPLE" TEMPORARY TABLESPACE "TEMPORARY_DATA" PROFILE DEFAULT QUOTA UNLIMITED ON "PLSQL_EXEMPLE" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PLSQL";
GRANT CREATE PROCEDURE TO "PLSQL";
ALTER USER "PLSQL" DEFAULT ROLE ALL;
回复
zgh2003 2005-01-05
需要授权:
SQL>conn sys as sysdba
SQL>grant create procedure to plsql
如果没有查询表的权限,同时还要授予查询相应表的权限。
回复
zakat 2005-01-05
你的这个用户可以创建其他的过函吗?如果不能,应该是你没有给它授权创建过函的权限。授权就可以了。
回复
majormarshal 2005-01-05
请问:在创建库时,完整的授权语句该如何书写?
即:在
CREATE TABLESPACE PLSQL_EXEMPLE DATAFILE 'PLSQL_EXEMPLE.ora' SIZE 10M;
CREATE USER "PLSQL" IDENTIFIED BY "TEST" DEFAULT TABLESPACE "PLSQL_EXEMPLE" TEMPORARY TABLESPACE "TEMPORARY_DATA" PROFILE DEFAULT QUOTA UNLIMITED ON "PLSQL_EXEMPLE" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PLSQL";
ALTER USER "PLSQL" DEFAULT ROLE ALL;
中还要再加入什么语句?

在我目前的情况下,还需要添加什么语句?

请给出完整的语句。。谢谢。。
回复
fuxia 2005-01-05
你没有给创建的用户授予足够的权限
回复
发帖
基础和管理
创建于2007-09-28

1.7w+

社区成员

Oracle 基础和管理
申请成为版主
帖子事件
创建了帖子
2005-01-05 11:32
社区公告
暂无公告