java.sql.SQLException: ORA-06550: line 1, column 13: After granting user permission to EXECUTE package -
i've spent months developing java application oracle db back-end. using netbeans ide , oracle 12c on laptop database. pl/sql has been compiled no errors.
after extensive testing (logged in schema owner), attempted add user. created user, logged in sys_dba, using create user e566299 identified temppswrd
, granted permission using grant create session
, grant execute on c##fai_code.fai_admin_pkg e566299
, received grant succeeded
confirmation.
i login application, new user, no errors using following:
private static connection getdbconn(string user, string password) throws sqlexception{ oracledatasource ods = null; connection dbconn = null; user = "c##" + user; ods = new oracledatasource(); ods.seturl("jdbc:oracle:thin:@//localhost:1522/orcl.global.ds.xxxxxxxx.com"); ods.setuser(user); ods.setpassword(password); dbconn = ods.getconnection(); return dbconn; }
then throws error on first package procedure call:
java.sql.sqlexception: ora-06550: line 1, column 13: pls-00201: identifier 'fai_admin_pkg.check_user_followed' must declared ora-06550: line 1, column 7: pl/sql: statement ignored
at oracle.jdbc.driver.t4cttioer.processerror(t4cttioer.java:450) @ oracle.jdbc.driver.t4cttioer.processerror(t4cttioer.java:399) @ oracle.jdbc.driver.t4c8oall.processerror(t4c8oall.java:1017) @ oracle.jdbc.driver.t4cttifun.receive(t4cttifun.java:655) @ oracle.jdbc.driver.t4cttifun.dorpc(t4cttifun.java:249) @ oracle.jdbc.driver.t4c8oall.dooall(t4c8oall.java:566) @ oracle.jdbc.driver.t4ccallablestatement.dooall8(t4ccallablestatement.java:210) @ oracle.jdbc.driver.t4ccallablestatement.dooall8(t4ccallablestatement.java:53) @ oracle.jdbc.driver.t4ccallablestatement.executeforrows(t4ccallablestatement.java:938) @ oracle.jdbc.driver.oraclestatement.doexecutewithtimeout(oraclestatement.java:1075) @ oracle.jdbc.driver.oraclepreparedstatement.executeinternal(oraclepreparedstatement.java:3820) @ oracle.jdbc.driver.oraclepreparedstatement.execute(oraclepreparedstatement.java:3923) @ oracle.jdbc.driver.oraclecallablestatement.execute(oraclecallablestatement.java:5617) @ oracle.jdbc.driver.oraclepreparedstatementwrapper.execute(oraclepreparedstatementwrapper.java:1385) @ faidb.faidb.checksubscription(faidb.java:549) @ faidb.faidbui.run(faidbui.java:186) @ faidb.faidblogin$3.run(faidblogin.java:133) @ java.awt.event.invocationevent.dispatch(invocationevent.java:311)
i'm @ loss, i've tried every possible combination of grant wording, caps, lowercase, single quotes, double quotes, schema owner name prefix c##fai_code.fai_admin_pkg
, without fai_admin_pkg
, nothing allow new user execute package.
i've spent considerable time on project , near panic no 1 able use it.
question:
why can't newly created user execute package after being granted permission?
edit:
i nullpointerexception
on last line call rset.close();
, result set never initialized doesn't throw sqlexception
public vector<string> fillbox() throws sqlexception, nullpointerexception{ callablestatement callstatement = null; resultset rset = null; string fillbox = "{call fai_admin_pkg.get_end_item_pn(?)}"; vector<string> boxfill = new vector<>(); try{ callstatement = conn.preparecall(fillbox); callstatement.registeroutparameter(1, oracletypes.cursor); callstatement.execute(); rset = (resultset) callstatement.getobject(1); boxfill = buildrsvector(rset); } finally{ callstatement.close(); rset.close(); } return boxfill; }
that called here:
vector<string> boxfill = new vector<>(); try{ boxfill = uiinst.fillbox(); }catch(sqlexception e){ joptionpane.showmessagedialog(frame, e.getmessage()); }catch(nullpointerexception e){ joptionpane.showmessagedialog(frame, e.getmessage()); e.printstacktrace(); }
here permissions new user...which not include execute on package?? i'm not understanding
problem solved. not having global synonym set or current_schema
new user's session set package owner results in calls owners package procedures requiring owner's prefix: <package owner>.<package>.<procedure>
. calls making under new user <package>.<procedure>
resulting in sqlexception
being thrown because compiler package doesn't exist.
additionally, research both global synonyms , user sessions avoid having use quick fix.
thanks help!
Comments
Post a Comment