Unable to edit row/column data directly

Help
RobertE
2010-06-21
2012-12-13
  • RobertE

    RobertE - 2010-06-21

    Am attempting to runaway from TOAD and again support another SF project but have a possible deal breaker with TOra in front of me.  With TOAD I could run a query like the one below against our aging Oracle 8.1.7 db and within the query results I could edit/commit changes to the rows/columns.  Additionally within the Schema Browser I could edit/commit changes to the rows/columns.

    An example of the query used in TOAD:

    select VPRODUCT_TYPES.*,
    rowid from VPRODUCT_TYPES
    where VIDENT = 277771

    TOra does not allow myself to do anything but view the query results and while I can edit information returned in the Schema Browser going to commit produces this error:

    Mon Jun 21 11:08:22 2010
    ORA-20103: Process name missing in CLIENT_INFO.
    ORA-06512: at "PROD.VDC_BIUR_TRG", line 61
    ORA-04088: error during execution of trigger 'PROD.VDC_BIUR_TRG'

    What puzzles me is the fact that I can run an update query and that works.  Details:

    I am now on a newly installed Win7 x64 Ultimate system of which I have simply dumped our tnsnames.ora file to a dir and set TNS_ADMIN to the directory path.  Am connecting to the db with TOra via Oracle (TNS).

    Not sure if this would require installing the Instant Client or not but any direction/ideas would be appreciated.  I'd really prefer to not go back to TOAD even if they now have Toad for Oracle FREEWARE available after seeing the TOra interface. 

     
  • RobertE

    RobertE - 2010-06-21

    Failed to mention this in the above post (sorry have had a fever for five days)..  The version of TOra in use is 2.1.2.  Am not using the installer on this machine but rather extracted contents of the tora2.1.2-win32.zip archive.

     
  • Petr Vaněk

    Petr Vaněk - 2010-06-21

    DML in the result view is not implemented now. I don't know if somebody plans to implement it.

    ad 2nd bug you are getting: can you share content of your PROD.VDC_BIUR_TRG trigger, please? It's propably something there - I use schema browser updates almost daily.

     
  • RobertE

    RobertE - 2010-06-21

    thanks for the response subik.  here's the trigger content for VDC_BIUR_TRG

    BEGIN
        DECLARE
            /***************************************************************************
            || trigger : vdc_biur_trg   (b=Before, i=Insert, ,u=update, r=Row TRIGGER)
            || Purpose : This trigger updates the "ROW WHO" columns
            || Output  : INSERTING create_by, create_date, update_by, update_date,
            ||         :           create_process,         update_process
            ||         : UPDATING  update_by, update_date, update_process
            ||*************************************************************************/
            v_client_info VARCHAR2 ( 100 );
            n_user NUMBER ( 20 );
            v_process VARCHAR2 ( 30 );
            i_pipe_locn PLS_INTEGER;
            d_date DATE;
            e_client_info_err EXCEPTION
            ;
            e_user_err EXCEPTION
            ;
            e_process_err EXCEPTION
            ;
        BEGIN
            SELECT SYSDATE
                 , USERENV ( 'CLIENT_INFO' )
              INTO d_date
                 , v_client_info
              FROM DUAL;
            IF v_client_info IS NULL
            THEN
                RAISE e_client_info_err;
            END IF;
            i_pipe_locn := INSTR ( v_client_info
                         , '|' );
            IF i_pipe_locn = 0
            THEN
                -- missing   | means missing process name
                RAISE e_process_err;
            ELSIF i_pipe_locn > 20
            THEN
                -- misplaced | means invalid user name
                RAISE e_user_err;
            END IF;
            n_user := RTRIM ( LTRIM ( SUBSTR ( v_client_info
                             , 1
                             , i_pipe_locn - 1.0 ) ) );
            IF n_user IS NULL
            THEN
                RAISE e_user_err;
            END IF;
            v_process := RTRIM ( LTRIM ( SUBSTR ( v_client_info
                                , i_pipe_locn + 1.0
                                , 30 ) ) );
            IF v_process IS NULL
            THEN
                RAISE e_process_err;
            END IF;
            IF INSERTING
            THEN
                :NEW.CREATE_BY := n_user;
                :NEW.CREATE_DATE := d_date;
                :NEW.CREATE_PROCESS := v_process;
                :NEW.UPDATE_BY := n_user;
                :NEW.UPDATE_DATE := d_date;
                :NEW.UPDATE_PROCESS := v_process;
            ELSE
                :NEW.UPDATE_BY := n_user;
                :NEW.UPDATE_DATE := d_date;
                :NEW.UPDATE_PROCESS := v_process;
            END IF;
            EXCEPTION
                WHEN too_many_rows
                THEN
                    RAISE_APPLICATION_ERROR ( - 20100
                                , 'Too Many Rows returned on Select from DUAL.'
                                , FALSE );
                WHEN e_client_info_err
                THEN
                    RAISE_APPLICATION_ERROR ( - 20101
                                , 'CLIENT_INFO must be set for session.'
                                , FALSE );
                WHEN e_user_err
                THEN
                    RAISE_APPLICATION_ERROR ( - 20102
                                , 'User Login missing (or too long) in CLIENT_INFO.'
                                , FALSE );
                WHEN e_process_err
                THEN
                    RAISE_APPLICATION_ERROR ( - 20103
                                , 'Process name missing in CLIENT_INFO.'
                                , FALSE );
                WHEN others
                THEN
                    RAISE_APPLICATION_ERROR ( - 20104
                                , 'An unexpected error occurred resolving ROW WHO columns.'
                                , FALSE );
        END;
    END;
    
     
  • RobertE

    RobertE - 2010-06-21

    subik some additional info.  For myself to run an update query I start out by running the following:

    begin
      dbms_application_info.[b]set_client_info[/b]('2|sqlplus');
    end;
    

    Within TOAD I was able to go on my merry way throughout the program provided I ran this first thing.  I do this right out of the gate upon launching TOra but as mentioned I can't do anything within the Schema Browser as far as committing my changes goes.  In TOra is there a different way that I should fire this query off so that I can edit/commit within the Schema Browser?

     
  • RobertE

    RobertE - 2010-06-22

    Solved my Schema Browser matter.  Coming out of the gate with:

    exec dbms_application_info.set_client_info('2|sqlplus');
    

    Enabled the ability to commit changes made.  So while DML isn't there in the results view now that I have the means to edit/commit away through the Schema Browser in conjunction with running update queries I am quite happy with TOra.  Especially happy with the fact that I can jump back and forth between Linux and Windows with this.

     
  • Nathan Neulinger

    What happens if you run … set_client_info('TOra/2.1.2')

    Does that still work for you?

     
  • RobertE

    RobertE - 2010-06-22

    What happens if you run … set_client_info('TOra/2.1.2') Does that still work for you?

    that actually does not work.

     
  • RobertE

    RobertE - 2010-06-28

    Last question for everyone -> in the Schema Browser, with a field selected within a row/column, what keystroke do I use to enter into a field to edit its contents?  With TOAD if I hit enter I could then edit the contents of a field.  I've yet to be able to find the magic keyboard button or combo to get myself there.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks