8 Replies Latest reply: Dec 27, 2012 10:52 AM by APC RSS

    SQLCode : ORA-01031: insufficient privileges

    959687
      {122612 17:54:51:Procedure pk_xop_call_track.pr_ins_call_track_info started, 12/26/2012 17:54:51
      122612 17:54:51:Call Stat: 0
      122612 17:54:51:SSN, login id: 002522649,NBKSN1C
      122612 17:54:51:Fai1ure Occured, pk_xop_call_track.pr_ins_call_track_info_back, SQLCode : ORA-01031: insufficient privileges}

      {i have received this error while running the procedure, plz help me to resolve it}
        • 1. Re: SQLCode : ORA-01031: insufficient privileges
          vijayrsehgal-Oracle
          Dear user, we do not have magic, we would need your code to understand where the code might have failed. While sharing your code please put the script in tags "{code}" without quotes.

          Till the time you share your code, remove any exception block you have to see which line fails and take corrective action to have privilege on the object.
          • 2. Re: SQLCode : ORA-01031: insufficient privileges
            ShishirTekadeR
            please share full code...


            Best Regards,
            Shishir Tekade.
            My Blog: http://shishirtekade.blogspot.com
            • 3. Re: SQLCode : ORA-01031: insufficient privileges
              959687
              {PROCEDURE pr_ins_call_track_info ( p_ssn                IN VARCHAR2
                                                , p_acct_num              IN VARCHAR2
                                                , p_ivr_plan_num          IN VARCHAR2
                                                , p_first_name            IN VARCHAR2
                                                , p_last_name             IN VARCHAR2
                                                , p_login_id              IN VARCHAR2
                                                , p_trans_type            IN PLS_INTEGER  -- 0 - Inquiry
                                                                                          -- 1 - Exercise
                                                                                          -- 2 - Sub sales
                                                , p_call_stat             IN PLS_INTEGER  -- 0 - Open Call
                                                                                          -- 1 - In Progress
                                                                                          -- 2 - End Call
                                                , p_exer_type             IN PLS_INTEGER
                                                , p_grant_date            IN DATE
                                                , p_grant_id              IN VARCHAR2
                                                , p_opts_exer             IN NUMBER
                                                , p_symbol                IN VARCHAR2
                                                , p_order_qty             IN NUMBER
                                                , p_vouchernum            IN VARCHAR2
                                                , p_ret_code              OUT PLS_INTEGER
                                                , p_user_id               IN VARCHAR2 DEFAULT USER ) IS

                      v_cnxt_key          VARCHAR2(50);
                      v_cnxt_value        VARCHAR2(20);

                      v_dqopt             DBMS_AQ.DEQUEUE_OPTIONS_T;
                      v_eqopt             DBMS_AQ.ENQUEUE_OPTIONS_T;
                      v_msgprop           DBMS_AQ.MESSAGE_PROPERTIES_T;
                      v_payload           SYS.MGW_BASIC_MSG_T;
                      v_text_body         SYS.MGW_TEXT_VALUE_T;
                      v_msgid             RAW(16);

                      v_message           VARCHAR2(1024);
                      v_return            PLS_INTEGER;

                      v_ivr_plan_num      tb_fc_compy.ivr_plan_num%TYPE;
                      v_timeout           PLS_INTEGER;

                      ex_invalid_key      EXCEPTION;

                  BEGIN

                      p_ret_code := 0;

                      pr_xop_log_errors('Procedure pk_xop_call_track.pr_ins_call_track_info started, ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
                      pr_xop_log_errors('Call Stat: ' || p_call_stat );
                      pr_xop_log_errors('SSN, login id: ' || p_ssn || ',' || UPPER(p_login_id));

                      v_cnxt_key      := LPAD(TRIM(p_ssn),9,0) || UPPER(TRIM(p_login_id));

                      BEGIN
                          SELECT num_content INTO v_timeout
                          FROM tb_fc_misc_contents
                          WHERE content_name = 'CallTrack - Timeout';
                      EXCEPTION
                          WHEN OTHERS THEN
                              v_timeout := 1200;
                      END;

                      IF sys_context('g_cnxt_calltrack', v_cnxt_key) IS NOT NULL THEN
                          v_cnxt_value := sys_context('g_cnxt_calltrack', v_cnxt_key);

                          IF p_call_stat = 0 THEN

                              pr_process_rec(v_cnxt_key, v_return);
                              pr_xop_log_errors('Context key to be removed ' || v_cnxt_key);
                              dbms_session.clear_context('g_cnxt_calltrack', NULL, v_cnxt_key);

                              v_cnxt_value    := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
                              dbms_session.set_context('g_cnxt_calltrack', v_cnxt_key, v_cnxt_value);
                              pr_xop_log_errors('Context Set ' || v_cnxt_key);
                              v_msgprop.delay := v_timeout;  -- 20 Mins
                          ELSIF p_call_stat = 1 THEN
                              v_msgprop.delay := v_timeout;  -- 20 Mins
                          ELSIF p_call_stat = 2 THEN
                              v_msgprop.delay := 0;     -- No delay
                          END IF;
                      ELSE
                          IF p_call_stat = 0 THEN
                              v_cnxt_value    := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
                              dbms_session.set_context('g_cnxt_calltrack', v_cnxt_key, v_cnxt_value);
                              pr_xop_log_errors('Context Set ' || v_cnxt_key);
                              v_msgprop.delay := v_timeout;  -- 20 Mins
                          ELSE
                              pr_xop_log_errors('No context.. should not happen for call stat 1, 2');
                              RAISE ex_invalid_key; -- should not happen
                          END IF;
                      END IF;

                        SELECT ivr_plan_num INTO v_ivr_plan_num
                        FROM
                        (
                           SELECT ivr_plan_num
                           FROM tb_fc_compy
                           WHERE compy_acronym = TRIM(REPLACE(REPLACE(p_user_id, 'CMS'), '_USER'))
                             UNION
                           SELECT bol_plan_num
                           FROM tb_xop_espp_compy
                           WHERE compy_acronym = TRIM(REPLACE(REPLACE(p_user_id, 'CMS'), '_USER'))
                        )
                        ;

                      -- Insert message into queue
                      v_message :=  LPAD(TRIM(p_ssn),9,0) ||                                --  1 -  9
                                      LPAD(NVL(TRIM(p_acct_num), ' '), 9, ' ') ||           -- 10 - 18
                                      LPAD(NVL(TRIM(v_ivr_plan_num), ' '), 10, ' ') ||      -- 19 - 28
                                      LPAD(NVL(TRIM(p_first_name), ' '), 15, ' ') ||        -- 29 - 43
                                      LPAD(NVL(TRIM(p_last_name), ' '), 40, ' ') ||         -- 44 - 83
                                      LPAD(NVL(TRIM(p_login_id), ' '), 30, ' ') ||          -- 84 - 113
                                      NVL(TO_CHAR(p_trans_type), ' ')   ||                  -- 114 - 114
                                      TO_CHAR(p_call_stat)   ||                             -- 115 - 115
                                      LPAD(NVL(TO_CHAR(p_exer_type), ' '), 4, ' ') ||       -- 116 - 119
                                      NVL(TO_CHAR(p_grant_date, 'YYYYMMDD'), '        ') || -- 120 - 127
                                      LPAD(NVL(TRIM(p_grant_id), ' '), 13, ' ') ||          -- 128 - 140
                                      LPAD(NVL(TRIM(p_opts_exer), ' '), 8, ' ') ||          -- 141 - 148
                                      LPAD(NVL(TRIM(p_symbol), ' '), 8, ' ' ) ||            -- 149 - 156
                                      LPAD(NVL(TRIM(p_order_qty), ' '), 8, ' ') ||          -- 157 - 164
                                      LPAD(NVL(TRIM(p_user_id), ' '), 20, ' ' ) ||          -- 165 - 184
                                      LPAD(NVL(TRIM(p_vouchernum), ' '), 30, ' ');          -- 185 - 214



                      INSERT INTO tb_xop_call_track (call_id
                                                     , call_key
                                                     , ssn
                                                     , psr_id
                                                     , call_stat
                                                     , msg_txt
                                                     , user_id
                                                     , ins_dttime
                                                     , upd_dttime
                                                     , wbsv_call
                                                     , wbsv_call_msg
                                                     )
                      VALUES ( sq_xop_call_id.NEXTVAL
                               , v_cnxt_value
                               , lpad(TRIM(p_ssn),9,0)
                               , TRIM(p_login_id)
                               , p_call_stat
                               , v_message
                               , p_user_id
                               , SYSDATE
                               , NULL
                               , NULL
                               , NULL
                             );

                      COMMIT;

                      v_msgprop.correlation := v_cnxt_key;

                      v_text_body := SYS.MGW_TEXT_VALUE_T(null, null);
                      v_text_body.small_value := v_message;
                      v_payload := SYS.MGW_BASIC_MSG_T(null, v_text_body, null);

                      BEGIN
                          DBMS_AQ.ENQUEUE(
                                  queue_name => 'MGW_AGENT.AQ_CALLTRACK'  --IN
                                  , enqueue_options => v_eqopt            --IN
                                  , message_properties => v_msgprop       --IN
                                  , payload => v_payload                  --IN
                                  , msgid => v_msgid                      --OUT
                                  );
                      EXCEPTION
                          WHEN OTHERS THEN
                              pr_xop_log_errors('Fai1ure enqueueing the message. Aborting... ' || SQLERRM);
                              p_ret_code := -100;
                              RAISE;
                      END;

                      p_ret_code := 0;
                      pr_xop_log_errors('Procedure pk_xop_call_track.pr_ins_call_track_info Ended, ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));

                      COMMIT;

                  EXCEPTION
                      WHEN ex_invalid_key THEN
                          pr_xop_log_errors('Fai1ure Occured, Context Key not found ' || v_cnxt_key || ' Call stat: ' || p_call_stat);
                      WHEN OTHERS THEN
                          pr_xop_log_errors('Fai1ure Occured, pk_xop_call_track.pr_ins_call_track_info_back, SQLCode : ' || SQLERRM );
                          p_ret_code := -100;
                  END pr_ins_call_track_info;}
              • 4. Re: SQLCode : ORA-01031: insufficient privileges
                vijayrsehgal-Oracle
                I have formatted your code and added a line, create the procedure using this code and execute. This will give you the line number on which you are getting error, take corrective action after that. I haven't tested this.

                PROCEDURE pr_ins_call_track_info(p_ssn          IN VARCHAR2,
                                                 p_acct_num     IN VARCHAR2,
                                                 p_ivr_plan_num IN VARCHAR2,
                                                 p_first_name   IN VARCHAR2,
                                                 p_last_name    IN VARCHAR2,
                                                 p_login_id     IN VARCHAR2,
                                                 p_trans_type   IN PLS_INTEGER -- 0 - Inquiry
                                                 -- 1 - Exercise
                                                 -- 2 - Sub sales
                                                ,
                                                 p_call_stat IN PLS_INTEGER -- 0 - Open Call
                                                 -- 1 - In Progress
                                                 -- 2 - End Call
                                                ,
                                                 p_exer_type  IN PLS_INTEGER,
                                                 p_grant_date IN DATE,
                                                 p_grant_id   IN VARCHAR2,
                                                 p_opts_exer  IN NUMBER,
                                                 p_symbol     IN VARCHAR2,
                                                 p_order_qty  IN NUMBER,
                                                 p_vouchernum IN VARCHAR2,
                                                 p_ret_code   OUT PLS_INTEGER,
                                                 p_user_id    IN VARCHAR2 DEFAULT USER) IS
                
                   v_cnxt_key   VARCHAR2(50);
                   v_cnxt_value VARCHAR2(20);
                
                   v_dqopt     dbms_aq.dequeue_options_t;
                   v_eqopt     dbms_aq.enqueue_options_t;
                   v_msgprop   dbms_aq.message_properties_t;
                   v_payload   sys.mgw_basic_msg_t;
                   v_text_body sys.mgw_text_value_t;
                   v_msgid     RAW(16);
                
                   v_message VARCHAR2(1024);
                   v_return  PLS_INTEGER;
                
                   v_ivr_plan_num tb_fc_compy.ivr_plan_num%TYPE;
                   v_timeout      PLS_INTEGER;
                
                   ex_invalid_key EXCEPTION;
                
                BEGIN
                
                   p_ret_code := 0;
                
                   pr_xop_log_errors('Procedure pk_xop_call_track.pr_ins_call_track_info started, ' ||
                                     to_char(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
                   pr_xop_log_errors('Call Stat: ' || p_call_stat);
                   pr_xop_log_errors('SSN, login id: ' || p_ssn || ',' || upper(p_login_id));
                
                   v_cnxt_key := lpad(TRIM(p_ssn), 9, 0) || upper(TRIM(p_login_id));
                
                   BEGIN
                      SELECT num_content INTO v_timeout FROM tb_fc_misc_contents WHERE content_name = 'CallTrack - Timeout';
                   EXCEPTION
                      WHEN OTHERS THEN
                         v_timeout := 1200;
                   END;
                
                   IF sys_context('g_cnxt_calltrack', v_cnxt_key) IS NOT NULL THEN
                      v_cnxt_value := sys_context('g_cnxt_calltrack', v_cnxt_key);
                   
                      IF p_call_stat = 0 THEN
                      
                         pr_process_rec(v_cnxt_key, v_return);
                         pr_xop_log_errors('Context key to be removed ' || v_cnxt_key);
                         dbms_session.clear_context('g_cnxt_calltrack', NULL, v_cnxt_key);
                      
                         v_cnxt_value := to_char(SYSDATE, 'YYYYMMDDHH24MISS');
                         dbms_session.set_context('g_cnxt_calltrack', v_cnxt_key, v_cnxt_value);
                         pr_xop_log_errors('Context Set ' || v_cnxt_key);
                         v_msgprop.delay := v_timeout; -- 20 Mins
                      ELSIF p_call_stat = 1 THEN
                         v_msgprop.delay := v_timeout; -- 20 Mins
                      ELSIF p_call_stat = 2 THEN
                         v_msgprop.delay := 0; -- No delay
                      END IF;
                   ELSE
                      IF p_call_stat = 0 THEN
                         v_cnxt_value := to_char(SYSDATE, 'YYYYMMDDHH24MISS');
                         dbms_session.set_context('g_cnxt_calltrack', v_cnxt_key, v_cnxt_value);
                         pr_xop_log_errors('Context Set ' || v_cnxt_key);
                         v_msgprop.delay := v_timeout; -- 20 Mins
                      ELSE
                         pr_xop_log_errors('No context.. should not happen for call stat 1, 2');
                         RAISE ex_invalid_key; -- should not happen
                      END IF;
                   END IF;
                
                   SELECT ivr_plan_num
                   INTO   v_ivr_plan_num
                   FROM   (SELECT ivr_plan_num
                           FROM   tb_fc_compy
                           WHERE  compy_acronym = TRIM(REPLACE(REPLACE(p_user_id, 'CMS'), '_USER'))
                           UNION
                           SELECT bol_plan_num
                           FROM   tb_xop_espp_compy
                           WHERE  compy_acronym = TRIM(REPLACE(REPLACE(p_user_id, 'CMS'), '_USER')));
                
                   -- Insert message into queue
                   v_message := lpad(TRIM(p_ssn), 9, 0) || -- 1 - 9
                                lpad(nvl(TRIM(p_acct_num), ' '), 9, ' ') || -- 10 - 18
                                lpad(nvl(TRIM(v_ivr_plan_num), ' '), 10, ' ') || -- 19 - 28
                                lpad(nvl(TRIM(p_first_name), ' '), 15, ' ') || -- 29 - 43
                                lpad(nvl(TRIM(p_last_name), ' '), 40, ' ') || -- 44 - 83
                                lpad(nvl(TRIM(p_login_id), ' '), 30, ' ') || -- 84 - 113
                                nvl(to_char(p_trans_type), ' ') || -- 114 - 114
                                to_char(p_call_stat) || -- 115 - 115
                                lpad(nvl(to_char(p_exer_type), ' '), 4, ' ') || -- 116 - 119
                                nvl(to_char(p_grant_date, 'YYYYMMDD'), ' ') || -- 120 - 127
                                lpad(nvl(TRIM(p_grant_id), ' '), 13, ' ') || -- 128 - 140
                                lpad(nvl(TRIM(p_opts_exer), ' '), 8, ' ') || -- 141 - 148
                                lpad(nvl(TRIM(p_symbol), ' '), 8, ' ') || -- 149 - 156
                                lpad(nvl(TRIM(p_order_qty), ' '), 8, ' ') || -- 157 - 164
                                lpad(nvl(TRIM(p_user_id), ' '), 20, ' ') || -- 165 - 184
                                lpad(nvl(TRIM(p_vouchernum), ' '), 30, ' '); -- 185 - 214
                
                
                   INSERT INTO tb_xop_call_track
                      (call_id, call_key, ssn, psr_id, call_stat, msg_txt, user_id, ins_dttime, upd_dttime, wbsv_call, wbsv_call_msg)
                   VALUES
                      (sq_xop_call_id.NEXTVAL,
                       v_cnxt_value,
                       lpad(TRIM(p_ssn), 9, 0),
                       TRIM(p_login_id),
                       p_call_stat,
                       v_message,
                       p_user_id,
                       SYSDATE,
                       NULL,
                       NULL,
                       NULL);
                
                   COMMIT;
                
                   v_msgprop.correlation := v_cnxt_key;
                
                   v_text_body             := sys.mgw_text_value_t(NULL, NULL);
                   v_text_body.small_value := v_message;
                   v_payload               := sys.mgw_basic_msg_t(NULL, v_text_body, NULL);
                
                   BEGIN
                      dbms_aq.enqueue(queue_name         => 'MGW_AGENT.AQ_CALLTRACK' --IN
                                     ,
                                      enqueue_options    => v_eqopt --IN
                                     ,
                                      message_properties => v_msgprop --IN
                                     ,
                                      payload            => v_payload --IN
                                     ,
                                      msgid              => v_msgid --OUT
                                      );
                   EXCEPTION
                      WHEN OTHERS THEN
                      
                         pr_xop_log_errors('Fai1ure enqueueing the message. Aborting... ' || SQLERRM);
                         p_ret_code := -100;
                         RAISE;
                   END;
                
                   p_ret_code := 0;
                   pr_xop_log_errors('Procedure pk_xop_call_track.pr_ins_call_track_info Ended, ' ||
                                     to_char(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
                
                   COMMIT;
                
                EXCEPTION
                   WHEN ex_invalid_key THEN
                      pr_xop_log_errors('Fai1ure Occured, Context Key not found ' || v_cnxt_key || ' Call stat: ' || p_call_stat);
                   WHEN OTHERS THEN
                      dbms_output.put_line(substr(dbms_utility.format_error_backtrace,1,1000)); -- new line added
                       pr_xop_log_errors('Fai1ure Occured, pk_xop_call_track.pr_ins_call_track_info_back, SQLCode : ' || SQLERRM);
                      p_ret_code := -100;
                END pr_ins_call_track_info;
                • 5. Re: SQLCode : ORA-01031: insufficient privileges
                  959687
                  Hi Friend,

                  I didnt find any changes in your code,.. plz tel me where there is a problem accured.
                  • 6. Re: SQLCode : ORA-01031: insufficient privileges
                    onedbguru
                    this is self-explanatory. The user trying to execute this
                    1) may not have permissions to execute the procedure
                    2) may not have permissions to read or write to the tables listed in the procedure.
                    • 7. Re: SQLCode : ORA-01031: insufficient privileges
                      APC
                      Vijay has added a line to the WHEN OTHERS part of the exception handler to display a formatted error stack. Because he has done this with DBMS_OUTPUT.PUT_LINE you will only see the stack if you run the procedure in an active client like SQL*Plus or PL/SQL Develoepr with DBMS_OUTPUT enabled.

                      Obviously it would be better if your logging routine did this sort of thing, but that is between you and your infrastructure code.

                      Cheers, APC
                      • 8. Re: SQLCode : ORA-01031: insufficient privileges
                        APC
                        Also, congratulations on posting a huge chuck of shonky code without using the {noformat}
                        {noformat} tags, despite somebody asking you to format your code.  If you want people to help you you really should do +everything possible+ to make it easy.  Posting code in a properly formatted way, so that it is easy to read, is more than just good manners: it is a wat to get other people to invest their time in trying to solve your problem.
                        
                        Your future co-operation will be appreciated.
                        
                        Cheers, APC