Home » SQL & PL/SQL » SQL & PL/SQL » inssuficent priviliges
inssuficent priviliges [message #256096] Fri, 03 August 2007 01:09 Go to next message
narendra.treddi
Messages: 6
Registered: August 2007
Location: mumbai
Junior Member

Hi,
Iam using the following procedure .

CREATE OR REPLACE PROCEDURE online_renewal AS
BEGIN
DECLARE
batch_date VARCHAR2(20);
var_err VARCHAR2(20);

CURSOR online_renew IS
select * from
(select pos_comm_info.LEAD_ID Enquiry_no,
pos_comm_info.CUST_ORDER_NUM order_no,
cards.PAYMODE,
pos_comm_info.CD_CODE DISTRIBUTOR_CODE,
pos_comm_info.LCO_CODE,
pos_comm_info.name customer_name,
IREN.USED_DATE renewal_date,
(PRODUCTS.price-PRODUCTS.service_tax) renewal_price,
pos_comm_info.LCO_MODEL , pos_comm_info.cust_login_id,
iren.sno
from cards,IREN,INEW,pos_comm_info,products
where
pos_comm_info.CUST_INSTALL_PACK_NO = INEW.sno
and IREN.USERNAME=INEW.USERNAME
and INEW.PICD in(30,37,47)
and upper(IREN.comments) not like (upper('%Failed in renewing the Account%'))
and IREN.SNo = CARDS.SNO
and cards.CARDS_PLAN = products.PID
and cards.PICD = products.PICD
and pos_comm_info.CATEGORY!='TTXL' -- Code added on 6-4-2007 by sujeet gautam on customer request to exclude TTXL orders
and upper(cards.status) = upper('OK')
and cards.picd in (34 ,38,48)
and ((cards.paymode >0 and cards.paymode <101)
or ( cards.paymode >= 200 and cards.paymode < 500 )
or ( cards.paymode >= 500 and cards.paymode < 1000)
or ( cards.paymode >= 4000 and cards.paymode < 6000)
or ( cards.paymode >= 6000 and cards.paymode < 9000))
AND iren.USED_DATE <> (SELECT NVL(MIN(ir2.USED_DATE),'1-JAN-2999') FROM iren ir2 WHERE ir2.username= iren.username and ir2.USERNAME not in
(SELECT USERNAME from INEW in1 where in1.USERNAME = ir2.USERNAME
AND PICD IN (34,38)
)
)
)
where renewal_date >= TO_DATE(batch_date,'dd-MM-yyyy')
AND renewal_date < ADD_MONTHS(TO_DATE(batch_date,'dd-MM-yyyy'),1);

BEGIN
SELECT TO_CHAR(ADD_MONTHS(COMM_BATCH_FOR_MONTH,1),'dd-mm-yyyy')INTO batch_date FROM COMM_BATCH_EXE_HISTORY
WHERE COMM_BATCH_ID = (SELECT MAX(COMM_BATCH_ID) FROM COMM_BATCH_EXE_HISTORY);

delete from MLY_DIST_RENEWAL_COMM;

FOR I IN online_renew LOOP
BEGIN
INSERT INTO MLY_DIST_RENEWAL_COMM ( username,
DIST_CODE ,
LCO_CODE,
BB_ORDER_NO,
RENEWAL_ORDER_NO,
customer_name,
RENEWAL_DATE ,
RENEWAL_PRICE ,
ONLINE_NORMAL, PAYMODE,
Enquiry_no,
LCO_MODEL,
s_no)
VALUES ( i.CUST_LOGIN_ID,
i.DISTRIBUTOR_CODE,
i.LCO_CODE,
i.order_no,
'',
i.customer_name,
i.renewal_date,
i.renewal_price,
'' ,i.PAYMODE,
i.Enquiry_no,
i.LCO_MODEL,
i.sno);

EXCEPTION
WHEN OTHERS THEN
var_err := SQLERRM;
DBMS_OUTPUT.PUT_LINE(var_err);
End;

END LOOP;
Execute Immediate ('Commit');
update MLY_DIST_RENEWAL_COMM set ONLINE_NORMAL = 'online' where ((paymode >0 and paymode <101)
or ( paymode >= 200 and paymode < 500 ));
Execute Immediate ('Commit');

update MLY_DIST_RENEWAL_COMM set ONLINE_NORMAL = 'normal' where
((paymode >= 500 and paymode < 1000)
or ( paymode >= 4000 and paymode < 6000)
or (paymode >= 6000 and paymode < 9000));
Execute Immediate ('Commit');
END;
DBMS_OUTPUT.PUT_LINE('Online renewal done !');
END;
/

while iam compiling it is compiling sucessfully , but while iam executing the above script it is showing the following error.
1.ora-01031 : inssuficient priviliges.

the same procedure when i executed at last time it is executed sucessfully. even i executed the proc with the backup tables which i have taken previously (i.e cards_june,iren_june etc)it is executing.
Aslo when i executed the query alone which is in the procedure is also executed sucessfully. but when i execute the entire procedure it is showing the above error. I request you pls resolve this issue.
Thanks,
Narendra.
Re: inssuficent priviliges [message #256098 is a reply to message #256096] Fri, 03 August 2007 01:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLES do not apply within PL/SQL procedures.
Re: inssuficent priviliges [message #256216 is a reply to message #256096] Fri, 03 August 2007 07:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
narendra.treddi wrote on Fri, 03 August 2007 02:09
Hi,

AND iren.USED_DATE <> (SELECT NVL(MIN(ir2.USED_DATE),'1-JAN-2999') FROM iren ir2 WHERE i



NVL requires that the expression and return value be of the same data type. You have a DATE column and a STRING. This is invalid.
FOO SCOTT>desc test0
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER(10)
 COL2                                               VARCHAR2(20)
 COL3                                               DATE

FOO SCOTT>/
select nvl(COL3,'1-jan-2999') from test0
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Second, such poor programming tactics to use a dummy date to signify a NULL value.
Re: inssuficent priviliges [message #257221 is a reply to message #256096] Tue, 07 August 2007 15:57 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
also why are you using execute immediate to do a simple commit. Do NOT use

Execute Immediate ('Commit');

Just use a simple

commit;


I would also suggest that only one commit be done at the very end of the procedure. If the procedure blows up, nothing is done. If it isn't everything completes. Committing multiple time is almost always a sign of bad programming. It will insure that you will have integrity errors.
Previous Topic: Predicting Sort usgae space
Next Topic: SQL Oracle update - append (merged by LF)
Goto Forum:
  


Current Time: Sat Dec 03 03:48:11 CST 2016

Total time taken to generate the page: 0.10535 seconds