Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: An error occured upgrading the repository!

  1. #1
    Join Date
    Jan 2006
    Posts
    12

    Default An error occured upgrading the repository!

    Couldn't execute SQL: ALTER TABLE R_NOTE MODIFY (VALUE_STR CLOB )


    ORA-22858: invalid alteration of datatype









    java.lang.reflect.InvocationTargetException: Error creating or upgrading repository:



    Couldn't execute SQL: ALTER TABLE R_NOTE MODIFY (VALUE_STR CLOB )



    ORA-22858: invalid alteration of datatype









    at be.ibridge.kettle.repository.dialog.UpgradeRepositoryProgressDialog$1.run(UpgradeRepositoryProgressDialog.java:66)
    at org.eclipse.jface.operation.ModalContext.runInCurrentThread(ModalContext.java:346)
    at org.eclipse.jface.operation.ModalContext.run(ModalContext.java:291)
    at org.eclipse.jface.dialogs.ProgressMonitorDialog.run(ProgressMonitorDialog.java:447)
    at be.ibridge.kettle.repository.dialog.UpgradeRepositoryProgressDialog.open(UpgradeRepositoryProgressDialog.java:74)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.create(RepositoryDialog.java:450)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.access$800(RepositoryDialog.java:64)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog$5.handleEvent(RepositoryDialog.java:267)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.open(RepositoryDialog.java:291)
    at be.ibridge.kettle.repository.dialog.RepositoriesDialog$4.widgetSelected(RepositoriesDialog.java:317)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:90)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.repository.dialog.RepositoriesDialog.open(RepositoriesDialog.java:405)
    at be.ibridge.kettle.spoon.Spoon.openRepository(Spoon.java:2332)
    at be.ibridge.kettle.spoon.Spoon$19.handleEvent(Spoon.java:745)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:581)
    at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:4687)
    Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException:
    Couldn't execute SQL: ALTER TABLE R_NOTE MODIFY (VALUE_STR CLOB )



    ORA-22858: invalid alteration of datatype






    at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1598)
    at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1547)
    at be.ibridge.kettle.core.database.Database.execStatements(Database.java:1717)
    at be.ibridge.kettle.repository.Repository.createRepositorySchema(Repository.java:3349)
    at be.ibridge.kettle.repository.dialog.UpgradeRepositoryProgressDialog$1.run(UpgradeRepositoryProgressDialog.java:61)
    ... 27 more
    Caused by: java.sql.SQLException: ORA-22858: invalid alteration of datatype



    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
    at oracle.jdbc.driver.T4CStatement.execute_for_rows(T4CStatement.java:474)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
    at be.ibridge.kettle.core.database.Database.execStatement(Database.java:1570)
    ... 31 more

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: An error occured upgrading the repository!

    Hi Alin,


    This problem was documented yesterday, with a solution, perhaps you can take a look at it? It's bug #2688.



    You also might want to take a look at the Repository Upgrade Guide



    Hope this helps! All the best,



    Matt

  3. #3
    Join Date
    Jan 2006
    Posts
    12

    Default RE: An error occured upgrading the repository!

    I already tried to manually alter the table, but the upgrade procedure ignores my changes.
    My bad, I missed the Repository Upgrade Guide.
    Now I'm trying to create a new repository, using a new oracle user/schema, but kettle only wants to upgrade it (when I choose Create or Upgrade) and fails with


    Unable to load user with login admin[img]/wiki/images/newpage.gif[/url] from the repository



    An error occurred executing SQL:
    SELECT ID_USER FROM R_USER WHERE LOGIN = ?
    ORA-00942: table or view does not exist












    be.ibridge.kettle.core.exception.KettleException:
    Unable to load user with login admin[img]/wiki/images/newpage.gif[/url] from the repository



    An error occurred executing SQL:
    SELECT ID_USER FROM R_USER WHERE LOGIN = ?
    ORA-00942: table or view does not exist









    at be.ibridge.kettle.repository.UserInfo.<init>(UserInfo.java:108)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.create(RepositoryDialog.java:425)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.access$800(RepositoryDialog.java:64)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog$5.handleEvent(RepositoryDialog.java:267)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.repository.dialog.RepositoryDialog.open(RepositoryDialog.java:291)
    at be.ibridge.kettle.repository.dialog.RepositoriesDialog$3.widgetSelected(RepositoriesDialog.java:297)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:90)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.repository.dialog.RepositoriesDialog.open(RepositoriesDialog.java:405)
    at be.ibridge.kettle.spoon.Spoon.openRepository(Spoon.java:2332)
    at be.ibridge.kettle.spoon.Spoon$19.handleEvent(Spoon.java:745)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:928)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3348)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2968)
    at be.ibridge.kettle.spoon.Spoon.readAndDispatch(Spoon.java:581)
    at be.ibridge.kettle.spoon.Spoon.main(Spoon.java:4687)
    Caused by: be.ibridge.kettle.core.exception.KettleDatabaseException:

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: An error occured upgrading the repository!

    Are you sure it's a new schema?

    The create or upgrade choice is made based on the existance of table R_USER.



    This upgrade procedure seems to look sound:


    <pre>
    ALTER TABLE r_database_attribute rename COLUMN VALUE_STR TO VALUE_STR_OLD;
    ALTER TABLE r_database_attribute ADD VALUE_STR CLOB;
    UPDATE r_database_attribute SET VALUE_STR = VALUE_STR_OLD;
    ALTER TABLE r_database_attribute DROP COLUMN VALUE_STR_OLD;
    </pre>


    Of course, in your case, it's the R_NOTE table that is causing the problem, so the script becomes:
    <pre>
    ALTER TABLE r_note rename COLUMN VALUE_STR TO VALUE_STR_OLD;
    ALTER TABLE r_note ADD VALUE_STR CLOB;
    UPDATE r_note SET VALUE_STR = VALUE_STR_OLD;
    ALTER TABLE r_note DROP COLUMN VALUE_STR_OLD;
    </pre>
    HTH,



    Matt

  5. #5
    Join Date
    Jan 2006
    Posts
    12

    Default RE: An error occured upgrading the repository!

    Yes, I'm sure it's a new user, with no tables under its schema.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: An error occured upgrading the repository!

    Maybe it&#39;s a permissions problem or something?
    Just to make sure I created a repository on my 10.2. database and it worked fine.
    In any case, you should do fine with the varchar2(2000) data type... as long as you don&#39;t store meta-data longer than 2000 characters long ;-) That&#39;s the very reason why we store our data in CLOBs now.

    All the best,

    Matt

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default RE: An error occured upgrading the repository!

    Statements to create repository user (run with dba rights: system or sys user)

    create user rep230 identified by abcd default tablespace users temporary tablespace temp;
    grant connect, resource to rep230;

    All the best,

    Matt

  8. #8
    Join Date
    Jan 2006
    Posts
    12

    Default RE: An error occured upgrading the repository!

    I used &#39;create like&#39; to create the new user from the old one and I guess that&#39;s why I had this problem. I created a fresh new user and now it works.

    Thanks a lot,
    Alin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.