14 Replies Latest reply: May 6, 2010 6:11 AM by 769039 RSS

    ORA-01403: No Data found error

    769039
      Hi,

      I am running a sql script and at some stage it is failing and displaying below errors:

      Exception:
      java.sql.SQLException: ORA-01403: no data found
      ORA-06512: at "SYSTEM.KNTA_MESSAGE", line 301
      ORA-06512: at "SYSTEM.KCRT_AUDIT", line 486
      ORA-01031: insufficient privileges
      ORA-06512: at line 90

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
           at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
           at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
           at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687)
           at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)
           at com.kintana.sqlminus.commands.PLSQLCommand.execute(PLSQLCommand.java:63)
           at com.kintana.sqlminus.ExecutionDriver.executeBlock(ExecutionDriver.java:244)
           at com.kintana.sqlminus.ExecutionDriver.execute(ExecutionDriver.java:206)
           at com.kintana.upgrade.xml.commands.SqlScriptCommand.runFiles(SqlScriptCommand.java:135)
           at com.kintana.upgrade.xml.commands.SqlScriptCommand.execute(SqlScriptCommand.java:47)
           at com.kintana.upgrade.xml.elements.SqlScriptTask.execute(SqlScriptTask.java:188)
           at org.apache.tools.ant.Task.perform(Task.java:341)
           at org.apache.tools.ant.Target.execute(Target.java:309)
           at org.apache.tools.ant.Target.performTasks(Target.java:336)
           at org.apache.tools.ant.Project.executeTarget(Project.java:1339)
           at org.apache.tools.ant.Project.executeTargets(Project.java:1255)
           at com.kintana.upgrade.xml.ItgCheema.runBuild(ItgCheema.java:420)
           at com.kintana.upgrade.xml.ItgCheema.start(ItgCheema.java:291)
           at com.kintana.ismp.bean.actions.CheemaRunner.action(CheemaRunner.java:91)
           at com.kintana.ismp.bean.actions.KintanaAction.execute(KintanaAction.java:84)
           at com.installshield.wizard.RunnableWizardBeanContext.run(RunnableWizardBeanContext.java:21)

      Below query is being executed and causing above error. Could anyone please suggest how it can be resolved.

      *****Start of Query********
      DECLARE
      x_msg knta_messages.display_text%TYPE;

      l_cursor NUMBER;
      l_sql_stmt VARCHAR2(20000 char);
      l_trig_exists number;
      l_return_code NUMBER;
      x_msg_type number;
      x_msg_name knta_messages.message_name%TYPE;

      cursor itypes is
      select REQUEST_TYPE_ID, REQUEST_TYPE_NAME
      from KCRT_REQUEST_TYPES
      where REQUEST_TYPE_ID > 10000;

      cursor rhtypes is
      select REQUEST_HEADER_TYPE_ID, REQUEST_HEADER_TYPE_NAME
      from KCRT_REQUEST_HEADER_TYPES
      where REQUEST_HEADER_TYPE_ID > 10000;

      UPGRADE_EXCEPTION EXCEPTION;

      BEGIN

      --if this returns non-zero, the trigger exists and should be dropped
      SELECT count(*)
      INTO l_trig_exists
      FROM user_triggers
      WHERE trigger_name = 'KCRT_REQUESTS_AUDIT';

      --create the statement to drop it
      IF(l_trig_exists > 0) THEN
      l_sql_stmt := 'DROP TRIGGER KCRT_REQUESTS_AUDIT';
      --execute the drop statement
      l_cursor := DBMS_SQL.Open_Cursor;
      DBMS_SQL.Parse( l_cursor, l_sql_stmt, DBMS_SQL.V7 );
      l_return_code := DBMS_SQL.Execute( l_cursor );
      DBMS_SQL.Close_Cursor( l_cursor );
      COMMIT;
      END IF;

      --detail triggers
      FOR it IN itypes LOOP

      KCRT_AUDIT.BUILD_AUDIT_TRIGGER_DETAIL
      (P_REQUEST_TYPE_ID => it.REQUEST_TYPE_ID,
      P_LAST_UPDATED_BY => 20, -- upgrade_data user
      O_MESSAGE_TYPE => x_msg_type,
      O_MESSAGE_NAME => x_msg_name,
      O_MESSAGE => x_msg);

      IF(x_msg_type != KNTA_Constant.SUCCESS) THEN
      dbms_output.put_line('Error while Generating Audit Trigger for Request Type: '||it.request_type_name);
      RAISE UPGRADE_EXCEPTION;
      END IF;

      END LOOP;

      dbms_output.put_line('DONE. ALL the Request Type Triggers Generated.');
      --header triggers
      FOR rht IN rhtypes LOOP

      KCRT_AUDIT.BUILD_AUDIT_TRIGGER_RHT
      (P_REQUEST_HEADER_TYPE_ID => rht.REQUEST_HEADER_TYPE_ID,
      P_LAST_UPDATED_BY => 20, -- upgrade_data user
      P_DELETE => 'N',
      O_MESSAGE_TYPE => x_msg_type,
      O_MESSAGE_NAME => x_msg_name,
      O_MESSAGE => x_msg);

      IF(x_msg_type != KNTA_Constant.SUCCESS) THEN
      dbms_output.put_line('Error while Generating Audit Trigger for Request Header Type:'||rht.request_header_type_name);
      RAISE UPGRADE_EXCEPTION;
      END IF;

      END LOOP;

      dbms_output.put_line('DONE. ALL the Request Header Type Triggers Generated.');
      EXCEPTION
      WHEN UPGRADE_EXCEPTION THEN
      dbms_output.put_line('Trigger Upgrade Error. Error in Generating Triggers');
      dbms_output.put_line(x_msg_type);
      dbms_output.put_line(SUBSTR(x_msg_name, 1, 250));
      RAISE;

      WHEN OTHERS THEN
      dbms_output.put_line('Error in Generating Triggers');
      dbms_output.put_line(x_msg_type);
      dbms_output.put_line(SUBSTR(x_msg_name, 1, 250));
      RAISE;

      END;
      *****End of Query*****************

      Could anyone please look into it and suggest something helpful. Any kind of help would be much appreciated.

      Cheers,
      Mandy
        • 1. Re: ORA-01403: No Data found error
          769039
          Could anyone please please look into it and suggest me the solution.

          Thanks in advance.

          Cheers,
          Mandy
          • 2. Re: ORA-01403: No Data found error
            Hoek
            Looking at your error messages, seems like some privilege problem:
            ORA-01031: insufficient privileges
            The following rings an alarm bell:
            ORA-06512: at "SYSTEM.KNTA_MESSAGE", line 301
            ORA-06512: at "SYSTEM.KCRT_AUDIT", line 486
            Did you create objects as SYSTEM, on the SYSTEM schema?
            You should never do that. SYSTEM is 'reserved' by Oracle.
            Can you explan why this approach is taken?

            (Also, creating or dropping objects on the fly is usually a recipe for disaster sooner or later.)
            • 3. Re: ORA-01403: No Data found error
              Saubhik
              By looking at this "java.sql.SQLException: ORA-01403: no data found", I would like to suggest you to find some SELECT INTO clauses in the KCRT_AUDIT.BUILD_AUDIT_TRIGGER_DETAIL or KCRT_AUDIT.BUILD_AUDIT_TRIGGER_RHT.
              • 4. Re: ORA-01403: No Data found error
                769039
                Actually first this had been asked to do on SYSTEM schema. But now I've created a separate schema.

                As you said Hoek that the privileges are not sufficient yes this could be the reason.

                Moreover I was running below query to grant system privileges but getting some error.

                grant select on v_$parameter to ppm_user; - - for this getting ERROR at line 1: ORA-00942: table or view does not exist
                grant execute on dbms_stats to ppm_user;-- for this getting ERROR at line 1: ORA-01031: insufficient privileges
                grant select on v_$mystat to ppm_user;-- for this getting ERROR at line 1:ORA-00942: table or view does not exist
                grant select on v_$process to ppm_user;-- for this getting ERROR at line 1:ORA-00942: table or view does not exist
                grant select on v_$session to ppm_user;-- for this getting ERROR at line 1: ORA-00942: table or view does not exist

                Could you please suggest how this can be resolved. I currently having DBA roles.

                Cheers,
                Mandy
                • 5. Re: ORA-01403: No Data found error
                  Hoek
                  As what user are you connected? as SYSDBA?
                  You will need to grant the privileges, connected as the owner of the objects you want to make available to ppm_user.

                  Also, don't use v_$parameter (owned by SYS), etc., but use v$parameter (grant acces to the Datadictionary Views, omit the underscores).

                  Spot the difference:
                  SQL> select object_name
                    2  ,      object_type
                    3  ,      owner
                    4  from   all_objects
                    5  where  object_name in ('V_$PARAMETER','V$PARAMETER', 'DBMS_STATS');
                  
                  OBJECT_NAME                    OBJECT_TYPE         OWNER
                  ------------------------------ ------------------- -------------------------
                  DBMS_STATS                     PACKAGE BODY        SYS
                  DBMS_STATS                     PACKAGE             SYS
                  V_$PARAMETER                   VIEW                SYS
                  DBMS_STATS                     SYNONYM             PUBLIC
                  V$PARAMETER                    SYNONYM             PUBLIC
                  
                  5 rows selected.
                  • 6. Re: ORA-01403: No Data found error
                    769039
                    I've logged as admin user which is having DBA role. First I've created this user and then granted the DBA role.

                    I don't know how to check what user we connected as?

                    It would be helpful if you can let me know how can below things be achieved as suggested by you.

                    "You will need to grant the privileges, connected as the owner of the objects you want to make available to ppm_user."


                    Cheers,
                    Mandy
                    • 7. Re: ORA-01403: No Data found error
                      Hoek
                      Let's try the following first:

                      grant select on v$parameter to ppm_user;

                      Does that work?
                      • 8. Re: ORA-01403: No Data found error
                        722368
                        Mandy,

                        even though admin user has the dba role assigned, the role may not have been granted to admin user with option "with grant". So admin user in trun cannot grant privileges to other users.

                        Login with sys user as sysdba

                        $sqlplus
                        username : / as sysdba

                        After connection, run the grant statements.

                        Thanks,
                        Siva
                        • 9. Re: ORA-01403: No Data found error
                          769039
                          I've run this:
                          grant select on v$parameter to ppm_user;

                          but it is giving below error:
                          ERROR at line 1:
                          ORA-01031: insufficient privileges

                          Mandy
                          • 10. Re: ORA-01403: No Data found error
                            Hoek
                            Well, Siva's reply should definatly work. However, usual a DBA should do this for you, so I suggest you also inform your DBA about this.
                            • 11. Re: ORA-01403: No Data found error
                              769039
                              Hi Sive,

                              I ran the following query but stil error is ocuring.

                              SQL> show user;
                              USER is "SYSTEM"
                              SQL> connect sys/my_secret_password as sysdba
                              Connected.
                              SQL>
                              SQL> grant select on v$parameter to ppm_user;
                              grant select on v$parameter to ppm_user
                              *
                              ERROR at line 1:
                              ORA-02030: can only select from fixed tables/views

                              SQL> show user
                              USER is "SYS"

                              Please suggest what is next to do.

                              Mandy
                              • 12. Re: ORA-01403: No Data found error
                                722368
                                Hi,

                                after connecting as sysdba, issue the following.
                                sql> grant select any dictionary to ppm_user

                                Thanks,
                                Siva
                                • 13. Re: ORA-01403: No Data found error
                                  Saubhik
                                  Hi, You are doing a fundamental mistake. The following example may clear your doubts.
                                  SQL> conn sys@xe as sysdba
                                  Enter password: ******
                                  Connected.
                                  SQL> grant select on v$parameter to hr;
                                  grant select on v$parameter to hr
                                                  *
                                  ERROR at line 1:
                                  ORA-02030: can only select from fixed tables/views
                                  
                                  
                                  SQL> grant select on v_$parameter to hr;
                                  
                                  Grant succeeded.
                                  
                                  SQL> conn hr@xe
                                  Enter password: **
                                  Connected.
                                  SQL> select NUM,name from v$parameter;
                                  
                                         NUM
                                  ----------
                                  NAME
                                  --------------------------------------------------------------------------------
                                           2
                                  tracefile_identifier
                                  
                                          25
                                  • 14. Re: ORA-01403: No Data found error
                                    769039
                                    Thanks Saubhik,

                                    yes it is working now. The sql script mentioned in my first thread is now working fine. I follow in the same way as you suggested and got success.

                                    Thanks to Hoek and Siva as well for their valuable inputs to guide me through this.

                                    Cheers,
                                    Mandy