Project and Portfolio Management Practitioners Forum
cancel

Error when upgrade 7.5 >> 8.0 sqlscript RML_SETUP_IN_RML postupgrade

Highlighted
Bruno_Iemma
Frequent Contributor.

Error when upgrade 7.5 >> 8.0 sqlscript RML_SETUP_IN_RML postupgrade

Hi, i´m upgrading PPM to 8.0 and get this error.

I try to debug this in TOAD editor, but icant find the error

Regards,

Bruno.-

 

Task:      sqlscript RML_SETUP_IN_RML
Started:   Wed Mar 21 11:58:48 ART 2012
[sqlscript]            This step has not been run, will run nowDECLARE
[sqlscript]
[sqlscript]   l_cursor_id NUMBER;
[sqlscript]   l_row_count NUMBER;
[sqlscript]   l_sql_stmt VARCHAR2(2000);
[sqlscript]   sql_num     number;
[sqlscript]   sql_errm   NVARCHAR2(512);
[sqlscript]   l_present_lang nls_session_parameters.value%type;
[sqlscript]   l_present_territory nls_session_parameters.value%type;
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]   CURSOR CUR_TABLES_AND_VIEWS IS
[sqlscript]    SELECT TABLE_NAME
[sqlscript]      FROM USER_TAB_PRIVS_RECD
[sqlscript]   where table_name not like 'BIN$%';
[sqlscript]
[sqlscript]
[sqlscript]   BEGIN
[sqlscript]     l_cursor_id := dbms_sql.open_cursor;
[sqlscript]
[sqlscript]     BEGIN
[sqlscript]       -- Grant execute on krml_execute
[sqlscript]       sql_num := 10;
[sqlscript]
[sqlscript]        select value
[sqlscript]              into l_present_lang
[sqlscript]              from nls_Session_parameters
[sqlscript]              where parameter='NLS_LANGUAGE';
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]             select value
[sqlscript]               into l_present_territory
[sqlscript]               from nls_Session_parameters
[sqlscript]         where parameter='NLS_TERRITORY';
[sqlscript]
[sqlscript]       l_sql_stmt := 'ALTER SESSION SET NLS_LANGUAGE= ''AMERICAN'' ';
[sqlscript]            dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]            l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]
[sqlscript]
[sqlscript]      l_sql_stmt:= 'ALTER SESSION SET NLS_TERRITORY= ''AMERICA''' ;
[sqlscript]          dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]            l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]       l_sql_stmt := 'grant execute on  KRML_EXECUTE to ppm';
[sqlscript]       dbms_output.put_line(l_sql_stmt);
[sqlscript] --      dbms_output.flush();
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('granted execute on KRML_EXECUTE to ' || 'ppm');
[sqlscript]
[sqlscript]     EXCEPTION
[sqlscript]       WHEN OTHERS THEN
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         dbms_output.put_line('Error when grant execute on KRML_EXECUTE: '
[sqlscript]                               || CHR(10) || sql_errm || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
[sqlscript]
[sqlscript]         raise;
[sqlscript]     END;
[sqlscript]
[sqlscript]     BEGIN
[sqlscript]       sql_num := 30;
[sqlscript]       l_sql_stmt := 'drop synonym krml_utils';
[sqlscript]       dbms_output.put_line('SQL STMT ' || l_sql_stmt);
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         -- check to skip synonym not found error
[sqlscript]         --if sql_num <> 1434 then
[sqlscript]         --  dbms_output.put_line('Error when drop synonym for KRML_UTILS, KNTA_I18N_RESOURCE: ' || sql_errm  || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         --  raise;
[sqlscript]        --end if;
[sqlscript]     END;
[sqlscript]     BEGIN
[sqlscript]       sql_num := 40;
[sqlscript]       l_sql_stmt := 'drop synonym knta_i18n_resource';
[sqlscript]       dbms_output.put_line('SQL STMT ' || l_sql_stmt);
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         -- check to skip synonym not found error
[sqlscript]         --if sql_num <> 1434 then
[sqlscript]         --  dbms_output.put_line('Error when drop synonym for KRML_UTILS, KNTA_I18N_RESOURCE: ' || sql_errm  || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         --  raise;
[sqlscript]        --end if;
[sqlscript]     END;
[sqlscript]     BEGIN
[sqlscript]       sql_num := 43;
[sqlscript]       l_sql_stmt := 'drop synonym knta_clob_utils';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         -- check to skip synonym not found error
[sqlscript]         --if sql_num <> 1434 then
[sqlscript]         --  dbms_output.put_line('Error when drop synonym for KRML_UTILS, KNTA_I18N_RESOURCE: ' || sql_errm  || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         --  raise;
[sqlscript]        --end if;
[sqlscript]     END;
[sqlscript]     BEGIN
[sqlscript]       sql_num := 47;
[sqlscript]       l_sql_stmt := 'drop synonym knta_note_util';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         -- check to skip synonym not found error
[sqlscript]         --if sql_num <> 1434 then
[sqlscript]         --  dbms_output.put_line('Error when drop synonym for KRML_UTILS, KNTA_I18N_RESOURCE: ' || sql_errm  || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         --  raise;
[sqlscript]        --end if;
[sqlscript]     END;
[sqlscript]     BEGIN
[sqlscript]       sql_num := 49;
[sqlscript]       l_sql_stmt := 'drop synonym knta_util';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         -- check to skip synonym not found error
[sqlscript]         --if sql_num <> 1434 then
[sqlscript]         --  dbms_output.put_line('Error when drop synonym for KRML_UTILS, KNTA_I18N_RESOURCE: ' || sql_errm  || CHR(10) || 'Line: ' || sql_num);
[sqlscript]         --  raise;
[sqlscript]        --end if;
[sqlscript]     END;
[sqlscript]
[sqlscript]     BEGIN
[sqlscript]       -- Create synonym for package krml_utils
[sqlscript]       sql_num := 50;
[sqlscript]       l_sql_stmt := 'create synonym krml_utils for ppm.krml_utils';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('synonym KRML_UTILS created');
[sqlscript]
[sqlscript]       -- Create synonym for package knta_i18n_resource
[sqlscript]       sql_num := 60;
[sqlscript]       l_sql_stmt := 'create synonym knta_i18n_resource for ppm.knta_i18n_resource';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('synonym KNTA_I18N_RESOURCE created');
[sqlscript]
[sqlscript]       -- Create synonym for package knta_clob_utils
[sqlscript]       sql_num := 63;
[sqlscript]       l_sql_stmt := 'create synonym knta_clob_utils for ppm.knta_clob_utils';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('synonym knta_clob_utils created');
[sqlscript]
[sqlscript]       -- Create synonym for package knta_note_util
[sqlscript]       sql_num := 67;
[sqlscript]       l_sql_stmt := 'create synonym knta_note_util for ppm.knta_note_util';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('synonym knta_note_util created');
[sqlscript]
[sqlscript]       -- Create synonym for package knta_util
[sqlscript]       sql_num := 69;
[sqlscript]       l_sql_stmt := 'create synonym knta_util for ppm.knta_util';
[sqlscript]       dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]       l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]       dbms_output.put_line('synonym knta_util created');
[sqlscript]
[sqlscript]
[sqlscript]     EXCEPTION
[sqlscript]       when others then
[sqlscript]         sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]         dbms_output.put_line('Error when create synonym for KRML_UTILS, KNTA_I18N_RESOURCE, knta_clob_utils, knta_note_util, knta_util: '
[sqlscript]                               || CHR(10) || sql_errm || CHR(10) || 'Line: ' || sql_num);
[sqlscript]          DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
[sqlscript]         raise;
[sqlscript]     END;
[sqlscript]
[sqlscript]     BEGIN
[sqlscript]
[sqlscript]         l_cursor_id := dbms_sql.open_cursor;
[sqlscript]
[sqlscript]
[sqlscript]         FOR tbl in CUR_TABLES_AND_VIEWS LOOP
[sqlscript]
[sqlscript]
[sqlscript]             -- drop synonym and create synonym
[sqlscript]             BEGIN
[sqlscript]                l_sql_stmt := 'drop synonym '  || tbl.table_name;
[sqlscript]                dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]                l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]             EXCEPTION
[sqlscript]                when others then
[sqlscript]                   sql_num := - sqlcode;
[sqlscript]                   sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]                   -- check to skip synonym not found error
[sqlscript]                   if sql_num = 1434 then
[sqlscript]                     dbms_output.put_line('Warning: ' || tbl.table_name || '> ' || sql_errm);
[sqlscript]                   else
[sqlscript]                     dbms_output.put_line('Error: ' || sql_errm);
[sqlscript]                     DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
[sqlscript]                     raise;
[sqlscript]                   end if;
[sqlscript]             END;
[sqlscript]
[sqlscript]             BEGIN
[sqlscript]                dbms_output.put_line('Info: create synonym ' || tbl.table_name);
[sqlscript]                l_sql_stmt := 'create synonym ' || tbl.table_name ||
[sqlscript]                 ' for ' || 'ppm.' || tbl.table_name;
[sqlscript]                dbms_sql.parse(l_cursor_id, l_sql_stmt, 1);
[sqlscript]                l_row_count := dbms_sql.execute(l_cursor_id);
[sqlscript]             EXCEPTION
[sqlscript]                when others then
[sqlscript]                   sql_num := - sqlcode;
[sqlscript]                   sql_errm := substr(sqlerrm, 1, 500);
[sqlscript]
[sqlscript]                   dbms_output.put_line('Error: ' || tbl.table_name || '> ' || sql_errm);
[sqlscript]                   DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
[sqlscript]                   raise;
[sqlscript]             END;
[sqlscript]
[sqlscript]         END LOOP;
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]       l_sql_stmt := 'ALTER SESSION SET NLS_TERRITORY= ''' ||  l_present_territory  || '''';
[sqlscript]      execute immediate l_sql_stmt;
[sqlscript]        dbms_output.put_line(chr(10) || l_sql_stmt);
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]       l_sql_stmt:= 'ALTER SESSION SET NLS_LANGUAGE= ''' || l_present_lang || '''';
[sqlscript]      execute immediate l_sql_stmt;
[sqlscript]            dbms_output.put_line(chr(10) || l_sql_stmt);
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]
[sqlscript]         dbms_sql.close_cursor(l_cursor_id);
[sqlscript]
[sqlscript]         dbms_output.put_line(chr(10) || 'RML setup (RML Schema part) complete successfully.');
[sqlscript]
[sqlscript]     EXCEPTION
[sqlscript]
[sqlscript]       WHEN OTHERS THEN
[sqlscript]
[sqlscript]         dbms_sql.close_cursor(l_cursor_id);
[sqlscript]         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
[sqlscript]         RAISE;
[sqlscript]
[sqlscript]     END;
[sqlscript]
[sqlscript]  END;

[sqlscript] java.sql.SQLSyntaxErrorException: ORA-04042: procedure, function, package, or package body does not exist
[sqlscript] ORA-06512: at line 66
FAILED:    Wed Mar 21 11:58:48 ART 2012
Duration:  00h:00m:00s

BUILD FAILED
D:\PPM\upgrade_800\xml\PostUpgrade.xml:94: java.sql.SQLSyntaxErrorException: ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 66


Total time: 0 seconds
java.sql.SQLSyntaxErrorException: ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 66

 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
 at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
 at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
 at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
 at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
 at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
 at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:298)
 at com.kintana.sqlminus.commands.PLSQLCommand.execute(PLSQLCommand.java:63)
 at com.kintana.sqlminus.ExecutionDriver.executeBlock(ExecutionDriver.java:251)
 at com.kintana.sqlminus.ExecutionDriver.execute(ExecutionDriver.java:211)
 at com.kintana.upgrade.xml.commands.SqlScriptCommand.runFiles(SqlScriptCommand.java:147)
 at com.kintana.upgrade.xml.commands.SqlScriptCommand.execute(SqlScriptCommand.java:56)
 at com.kintana.upgrade.xml.elements.SqlScriptTask.execute(SqlScriptTask.java:195)
 at org.apache.tools.ant.Task.perform(Task.java:364)
 at org.apache.tools.ant.Target.execute(Target.java:341)
 at org.apache.tools.ant.Target.performTasks(Target.java:369)
 at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
 at org.apache.tools.ant.Project.executeTarget(Project.java:1185)
 at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:40)
 at org.apache.tools.ant.Project.executeTargets(Project.java:1068)
 at com.kintana.upgrade.xml.ItgCheema.runBuild(ItgCheema.java:432)
 at com.kintana.upgrade.xml.ItgCheema.start(ItgCheema.java:300)
 at com.kintana.ismp.bean.actions.CheemaRunner.action(CheemaRunner.java:98)
 at com.kintana.ismp.bean.actions.KintanaAction.execute(KintanaAction.java:90)
 at com.installshield.wizard.RunnableWizardBeanContext.run(RunnableWizardBeanContext.java:21)

1 REPLY
dirkf
Acclaimed Contributor.

Re: Error when upgrade 7.5 >> 8.0 sqlscript RML_SETUP_IN_RML postupgrade

Hi Bruno,

 

you have an issue with the RML-Schema.

 

The install fails while configuring the RML schema.


1) Check if you have any invalid objects on the schema:

select object_name, object_type from user_objects where status != 'VALID';

 

2) Run the following script directlyfrom sqlplus:
\install_800\common\rml>@RMLSetupInRMLSchema.sql PPM_DB_username

It is looking for the krml_execute package , this should be present in the RML schema.

 

Best regards,

Dirk