关于数据库创建视图时权限不足的解决办法

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/m0_38053639/article/details/81121941

在创建视图时弹出如下对话框(用的navicat软件,与其他平台上的没啥分别,错误码都是ORA-01031)

原因是当前登录的scott用户没有创建视图的权限,需要给scott用户授权

首先用scott用户登录,然后给scott用户授权

在控制台上输入grant create any view to scott;

如下

出现Grant succeeded.表示授权成功

然后再打开navicat或PL/SQL Dev创建视图即可。

到这里就完成创建视图的任务啦

 


本文用于个人学习以及初学者相互借鉴,有不足的地方望见谅。如有错误欢迎指出,大家一起进步!!

展开阅读全文

oracle 创建存储过程,权限不足

09-05

在insert地方会编译出错:“权限不足”,请大神指点rn[color=#FF0000]错误:[/color]rn[Warning] ORA-24344: 成功, 但出现编译错误rn73/28 PL/SQL: ORA-01031: 权限不足rn73/9 PL/SQL: SQL Statement ignoredrn102/28 PL/SQL: ORA-01031: 权限不足rn102/9 PL/SQL: SQL Statement ignoredrn108/28 PL/SQL: ORA-01031: 权限不足rn108/9 PL/SQL: SQL Statement ignoredrn127/17 PL/SQL: ORA-00904: "SUMDAY": 标识符无效rn123/9 PL/SQL: SQL Statement ignoredrn (1: 0): Warning: compiled but with compilation errorsrnrn[color=#FF0000]sql:[/color]rn[code=sql]create or replace procedure ScoreToVoucher(ScoreExchThemeID int, CardTypeID int, EmployeeCode varchar2) rnasrnV_CardCount Number;/*卡的数量*/rnV_MaxVoucherID varchar2(30);/*最大券号*/rnV_MaxBillNumber varchar2(20);/*卡积分兑换表的最大单据号*/rnV_EmployeeName varchar2(60);/*职员名称*/rnbeginrn V_CardCount:=0;/*初始化卡的数量*/rn select EmployeeName into V_EmployeeName from "000".tbEmployee;rn delete from temp_Procedure__Card; /*删除临时卡表数据*/rn delete from temp_Procedure_Voucher;/*删除临时表券数据*/rn /*根据规则ID,卡类型,获取所有卡*/rn insert into temp_Procedure__Cardrn select a.MemberCode,a.CardCode,a.AccountsCode,a.CurrentBalance,0 ExchMoney,b.ExchVParValue,'' BillNumber,0 Score rn from "000".tbCard a,"000".tbScoreExchTheme brn where rn a.CardState=2 /*卡状态*/rn and a.IsBlankoutByChangeCard=0 /*不是废卡*/rn and a.IsIntegral=1 /*积分卡*/rn and a.typeID=CardTypeID /*卡类型*/rn and a.PeriodOfValidity>to_char(sysdate,'YYYYMMDD')/*有效期*/rn and b.ScoreExchThemeID=ScoreExchThemeID rn and b.ExchMoneyForm=0/*兑换形式为“兑现金”*/rn and b.IsSection=0/*分段积分不考虑*/rn and case b.CardTypeID when -1 then a.typeID else b.CardTypeID end=a.typeIDrn and a.LevelID in (select CardLevelID from "000".tbScoreExchTheme_CardLevel where ScoreExchThemeID=ScoreExchThemeID)rn and a.CurrentBalance>(select min(Score) from "000".tbScoreExchRuleToMoney where ScoreExchThemeID=ScoreExchThemeID);/*当前积分大于规则的最小积分*/rnrn select count(*) into V_CardCount from temp_Procedure__Card;rnrn if V_CardCount>0 then rn rn /*循环规则,按照积分大小降序,从大到小*/rn for extRule in (select * from "000".tbScoreExchRuleToMoney where ScoreExchThemeID=ScoreExchThemeID order by Score Desc) looprn /*更新兑换金额和*/rn update temp_Procedure__Card set rn ExchMoney=ExchMoney+(CurrentBalance-mod(CurrentBalance,extRule.Score))*extRule.ExchMoney/extRule.Score,rn CurrentBalance=mod(CurrentBalance,extRule.Score),rn Score=CurrentBalance-mod(CurrentBalance,extRule.Score)rn where CurrentBalance>=extRule.Score ;rn end loop;rn delete from temp_Procedure__Card where ExchMoney<=0;/*删除没有兑换的*/rn select max(BillNumber) into V_MaxBillNumber from "000".tb201407_ScoreExch where substr(billNumber,1,4)='9959';/*兑换信息最大单据号*/rn update temp_Procedure__Card set BillNumber=LPad(V_MaxBillNumber + rownum,length(V_MaxBillNumber),'0');/*创建单据号*/rn select Max(substr(VoucherID,5,12)) into V_MaxVoucherID from "000".tbVoucher;rn rn /*插入券临时表*/rn insert into temp_Procedure_Voucherrn select * from (rn WITH T1 AS(rn SELECT BillNumber,CardCode,MemberCode,AccountsCode,ExchMoney,ParValue,SUM(ExchMoney/ParValue)OVER(ORDER BY CardCode,MemberCode,AccountsCode,ExchMoney,ParValue) RCrn FROM temp_Procedure__Cardrn ),T2 AS (rn SELECT ROWNUM RN FROM DUALrn CONNECT BY ROWNUM<=(SELECT SUM(ExchMoney/ParValue) FROM temp_Procedure__Card)rn )rn SELECT Lpad(V_MaxVoucherID + T2.RN, 4, '0') VoucherID,rn Lpad(V_MaxVoucherID+T2.RN, 4, '0')rn + To_char(Trunc(DBMS_RANDOM.value(1000, 9999))) VoucherCode,rn T1.MemberCode,rn T1.CardCode,rn T1.AccountsCode,rn T1.BillNUmberrn /*,rn row_number() over(partition by T1.BillNUmber order by T1.BillNUmber) as BillIndex*/rn FROM T1,rn T2rn WHERE T1.RC >= T2.RNrn AND T1.RC - T1.ExchMoney / T1.ParValue < T2.RN rn )aa;rn rn /*根据券临时表插入券正式表*/rn INSERT INTO "000".tbVoucherrn (VoucherID,rn VoucherCode,rn MemberCode,rn ParValue,rn PeriodOfValidity,rn VoucherState,rn DepartmentCode,rn CheckoutString,rn OutDate,rn vType,rn AccountsCode,rn CardNumber,rn ScoreExchThemeID,rn PrintTempCode)rn select a.VoucherID,a.VoucherCode,a.MemberCode,b.ExchVParValue,b.VouchRetuenEndDate,2 VoucherState,rn '9957',md5(a.VoucherID||b.VouchRetuenEndDate||to_char(b.ExchVParValue)||to_char(2)) CheckoutString,to_char(sysdate,'YYYYMMDD'),0,a.AccountsCode,a.CardCode,b.ScoreExchThemeID,b.PrintTempCode from temp_Procedure_Voucher a,"000".tbScoreExchTheme brn where b.ScoreExchThemeID=ScoreExchThemeID;rn rn /*增加券制作明细*/rn rn /*根据卡临时表更新卡的积分余额*/rn UPDATE "000".tbCard T1rn SET ( T1.CurrentBalance,T1.TotalIntegralExchange) = (SELECT T2.CurrentBalance,T1.TotalIntegralExchange+T2.Scorern FROM temp_Procedure__Card T2rn WHERE T1.CardCode = T2.CardCode and T2.ExchMoney>0) ;rn rn /*记录兑换信息*/rn /*主表*/rn insert into "000".tb201407_ScoreExchrn select a.BillNumber,b.ScoreExchThemeID,a.AccountsCode,'9959' ExchDept,to_char(sysdate,'YYYYMMDD'),to_char(sysdate,'YYYYMMDDhh24Miss') ExchDate,EmployeeCode,rn V_EmployeeName ExchEmployName,0 ExchForm,a.Score Score,a.ExchMoney ,CurrentBalance ScoreBalanceEndExch,'' CancelBillNumber,'' WsPreBillNumber,a.AccountsCode CAcountsCodern from temp_Procedure__Card a,"000".tbScoreExchTheme b;rn rn /*明细*/rn insert into "000".tb201407_ScoreExchDetail(BillNumber,Score,ExchMoney)rn select BillNumber,Score,ExchMoneyrn from temp_Procedure__Card;rn rn /*记录卡日合计*/rn merge into "000".tb201407_DCardSum Mrn using temp_Procedure__Card Nrn on (M.SumDay=to_char(sysdate,'YYYYMMDD') and M.Accounts=N.AccountsCode)rn when not matched then rn insert (SumDay,Accounts,IntegralExchange)rn values(to_char(sysdate,'YYYYMMDD'),N.AccountsCode,N.Score)rn when matched then rn update set IntegralExchange=IntegralExchange+N.Score;rn rn /*记录卡月合计*/rn merge into "000".tb201407_MCardSum Mrn using temp_Procedure__Card Nrn on (M.Accounts=N.AccountsCode)rn when not matched then rn insert (SumDay,Accounts,IntegralExchange)rn values(to_char(sysdate,'YYYYMMDD'),N.AccountsCode,N.Score)rn when matched then rn update set IntegralExchange=IntegralExchange+N.Score;rn rn else rn DBMS_OUTPUT.PUT_LINE('没有对应的卡数据!');rn end if;rn rn commit;/*提交*/rn EXCEPTION /*异常处理*/rn WHEN OTHERS THEN rn DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!'); rn ROLLBACK; rnrnend;rn[/code] 论坛

没有更多推荐了,返回首页