7 Replies Latest reply: Mar 31, 2013 9:00 PM by bavani RSS

    Unable to run STARETL successfully

    bavani
      Hi All,

      I'm using Oracle Database 11g and RDB 3.1.

      When I run my etl I get the following error:

      <03.22.2013 09:00:36> staretl [INFO] (Progress) - Finished Script W_PROJECT_SECURITY_SS.sql
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_EPS_DS.sql failed
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_ACTIVITY_DS.sql failed
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_RESOURCE_DS.sql failed
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_PROJECT_DS.sql failed
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_WBS_DS.sql failed
      <03.22.2013 09:00:36> staretl [ERROR] (Message) - The SQL script W_ASSIGNMENT_DS.sql failed
      <03.22.2013 09:00:36> staretl [INFO] (Progress) - Step 23/48 Part 2/3 - FAILED (1) (0 hours, 0 minutes, 16 seconds, 567 milliseconds)

      When I look at the failed scripts individually I see this:

      whenever sqlerror exit sql.sqlcode
      set head off linesize 132 serveroutput on size 1000000 feed off
      BEGIN
      extract2staging('W_ACTIVITY_DS');
      END;
      /
      exit

      I don't understand what that means and I ran it individually and I get this error:

      ORA-06512: at "STARUSER.EXTRACT2STAGING", line 87

      Exception: Error during extract for table W_ACTIVITY_DS: ORA-00904: "ACTIVITY"."
      LASTUPDATED
      (100,ACTIVITY,W_ACTIVITY_DS,n/a,n/a)
      BEGIN
      *
      ERROR at line 1:
      ORA-00904: "ACTIVITY"."LASTUPDATEDATEX": invalid identifier
      ORA-06512: at "STARUSER.EXTRACT2STAGING", line 132
      ORA-06512: at line 2

      *I understand it as the column LASTUPDATEDATEX does not exist in ACTIVITY activity table. I have also run the publication services in EPPM before running STARETL. However, how do i solve this problem? I'm totally stuck I don't see much of this reported in the forum and I'm unable to find anything the guides. Appreciate if someone could shed some light here. Thanks.*
        • 1. Re: Unable to run STARETL successfully
          Sachin Gupta
          run the staretl process with "-r" option. This will regenerate the data. If it fails the first time, run it again with "-r" option. It regenerates the data successfully in second attempt.
          • 2. Re: Unable to run STARETL successfully
            Sachin Gupta
            Additionally verify that you have applied P6 EPPM R8.2 SP1 and also fixpack 16.
            • 3. Re: Unable to run STARETL successfully
              bavani
              Thanks Sachin, will try it and let you know if it works after applying the fixpack.
              • 4. Re: Unable to run STARETL successfully
                bavani
                Hi Sachin,

                I'm still getting the same error. I've run it with "-r" option and applied fixpack 16 on EPPM 8.2 SP 1.

                <03.29.2013 08:24:55> staretl [INFO] (Progress) - Finished Script W_EPS_HIERARCHY_DS.sql
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_WBS_DS.sql failed
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_RESOURCE_DS.sql failed
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_PROJECT_DS.sql failed
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_ASSIGNMENT_DS.sql failed
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_ACTIVITY_DS.sql failed
                <03.29.2013 08:24:55> staretl [ERROR] (Message) - The SQL script W_EPS_DS.sql failed
                <03.29.2013 08:24:55> staretl [INFO] (Progress) - Step 23/48 Part 2/3 - FAILED (1) (0 hours, 0 minutes, 15 seconds, 163 milliseconds)

                When I run the script individually, same error as well:

                ERROR at line 1:
                ORA-00904: "WBS"."LASTUPDATEDATEX": invalid identifier
                ORA-06512: at "STARUSER.EXTRACT2STAGING", line 132
                ORA-06512: at line 2

                I'm also NOT using partitioning as this is just an internal setup for R&D and demos. Appreciate if you could help me further. I went through all the pre-reqs and just can't seem to find a solution.
                • 5. Re: Unable to run STARETL successfully
                  Sachin Gupta
                  I am almost certain that you have not applied P6 EPPM R8.2 SP1 correctly. It has certain database scripts that needs to be run which will create the required views in PXRPTUSER with column LASTUPDATEDATEX. Try to rerun the scripts as per the instruction in the service pack 1. The script is particular is orpm_px_rpt_src.sql which needs to be run in PXRPTUSER schema.

                  Let me know if it does not work after that.
                  • 6. Re: Unable to run STARETL successfully
                    bavani
                    Thanks Sachin, will look into that thanks for specifying the script that helps.
                    • 7. Re: Unable to run STARETL successfully
                      bavani
                      You were right. Our P6 Admin ran the scripts and it works fine now! Thank you for being so detail!!!