Home » SQL & PL/SQL » Client Tools » TOAD update via ROWID (oracle 9.2 )
TOAD update via ROWID [message #462687] Fri, 25 June 2010 12:26 Go to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member

I'm placing this here since the only tool I have is TOAD to hack database. I have discovered a bad record with the system but as the date field is '0000' and the values are all hieroglyphics which I can't place in UPDATE statement. Only fields able to determine are that it's a journal entry. I tried the blow code only to be forced out of TOAD via ORA-03113: end-of-file on communication channel error is a generic error.
How can I update the bad date fields with such limited access? Is the ROWID acceptable?

Update ft_os
set ost_upd_dt = '06-dec-2010'
where ost_tran_code = 'JEN'
and ost_doc_ref = 'IS'
Re: TOAD update via ROWID [message #462688 is a reply to message #462687] Fri, 25 June 2010 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>set ost_upd_dt = '06-dec-2010'
With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

post SELECT & results that shows problem you report
Re: TOAD update via ROWID [message #462698 is a reply to message #462688] Fri, 25 June 2010 14:01 Go to previous messageGo to next message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got TOAD you should have sqlplus installed since you generally need the oracle client software to run TOAD.
Sqlplus is probably more forgiving with corrupted data.

And yes you can certainly use rowid if you know what it is.
Re: TOAD update via ROWID [message #462752 is a reply to message #462687] Sat, 26 June 2010 13:25 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
Thank you Blackswan and cookiemonster advice taken.

Follow up on original issue. I have determined that many column of the FT_OS table have bad data in them. By bad data, the following apply.

Alphas in NUMBER fields.
Dollars || Text in date fields.

Any offense you can imagine happened!

Found the ROWID field, but unable to update all the fields due to above noted issues. I tried to delete by ROWID and by a unique key, but received a flood of ORA messages from TOAD and SQLPlus.

ORA-06502; ORA-06512; ORA-04088

Not sure how to ZAP this record. It appears the end user did not report an issue, and reentered the journal entry correctly. The ledgers balance, which actually makes sense, as no real business keys are identifiable. However, this record is now creating ORA-03114 errors during queries.

How does on KILL such a bug!
Re: TOAD update via ROWID [message #462753 is a reply to message #462752] Sat, 26 June 2010 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>Alphas in NUMBER fields.
>Dollars || Text in date fields.

Forgive me, but I don't believe either line is true.
Only can occur when application is mal-implemented by using VARCHAR2 to store other datatypes.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

It is really, really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: TOAD update via ROWID [message #462754 is a reply to message #462687] Sat, 26 June 2010 13:54 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
Sorry for the disbelief, but I'm here looking at the issue. Yes the program burped big time creating the following images. As you can see the table definition is standard, a bit wordy, but what ERP is not.


table def
TABLE FT_OS (
OST_SYS_ID NUMBER (12),
OST_KEY_NO NUMBER (10) NOT NULL,
OST_COMP_CODE VARCHAR2 (12),
OST_TRAN_CODE VARCHAR2 (12),
OST_SM_CODE VARCHAR2 (12),
OST_DOC_NO NUMBER (10),
OST_SEQ_NO NUMBER (10),
OST_ACNT_YEAR NUMBER (2),
OST_DOC_DT DATE,
OST_DOC_CAL_YEAR NUMBER (4),
OST_DOC_CAL_MONTH NUMBER (2),
OST_DUE_DT DATE,
OST_MAIN_ACNT_CODE VARCHAR2 (12),
OST_SUB_ACNT_CODE VARCHAR2 (12),
OST_DIVN_CODE VARCHAR2 (12),
OST_DEPT_CODE VARCHAR2 (12),
OST_HEAD_NO_1 NUMBER (1),
OST_ANLY_CODE_1 VARCHAR2 (12),
OST_HEAD_NO_2 NUMBER (1),
OST_ANLY_CODE_2 VARCHAR2 (12),
OST_ACTY_CODE_1 VARCHAR2 (12),
OST_ACTY_CODE_2 VARCHAR2 (12),
OST_CURR_CODE VARCHAR2 (12),
OST_LC_AMT NUMBER,
OST_FC_AMT NUMBER,
OST_DRCR_FLAG VARCHAR2 (1) DEFAULT 'C',
OST_DOC_REF VARCHAR2 (40),
OST_DOC_REF_DT DATE,
OST_OTH_REF VARCHAR2 (15),
OST_LC_ADJ_AMT NUMBER,
OST_FC_ADJ_AMT NUMBER,
OST_LC_PDC_AMT NUMBER,
OST_FC_PDC_AMT NUMBER,
OST_LC_UNP_AMT NUMBER,
OST_FC_UNP_AMT NUMBER,
OST_LC_UNDEP_AMT NUMBER,
OST_FC_UNDEP_AMT NUMBER,
OST_LC_ORG_AMT NUMBER,
OST_FC_ORG_AMT NUMBER,
OST_REF_KEY_NO NUMBER (10),
OST_REF_COMP_CODE VARCHAR2 (12),
OST_REF_ACNT_YEAR NUMBER (2),
OST_REF_TRAN_CODE VARCHAR2 (12),
OST_REF_SEQ_NO NUMBER (10),
OST_REF_DOC_NO NUMBER (10),
OST_REF_DOC_DT DATE,
OST_REF_DOC_CAL_YEAR NUMBER (4),
OST_REF_DOC_CAL_MONTH NUMBER (2),
OST_REF_DUE_DT DATE,
OST_LAST_MATCH_DT DATE,
OST_TYPE VARCHAR2 (1),
OST_LC_AMT_2 NUMBER,
OST_LC_AMT_3 NUMBER,
OST_LC_ADJ_AMT_2 NUMBER,
OST_LC_ADJ_AMT_3 NUMBER,
OST_LC_PDC_AMT_2 NUMBER,
OST_LC_PDC_AMT_3 NUMBER,
OST_LC_UNP_AMT_2 NUMBER,
OST_LC_UNP_AMT_3 NUMBER,
OST_LC_UNDEP_AMT_2 NUMBER,
OST_LC_UNDEP_AMT_3 NUMBER,
OST_LC_ORG_AMT_2 NUMBER,
OST_LC_ORG_AMT_3 NUMBER,
OST_FLEX_01 VARCHAR2 (240),
OST_FLEX_02 VARCHAR2 (240),
OST_FLEX_03 VARCHAR2 (240),
OST_FLEX_04 VARCHAR2 (240),
OST_FLEX_05 VARCHAR2 (240),
OST_FLEX_06 VARCHAR2 (240),
OST_FLEX_07 VARCHAR2 (240),
OST_FLEX_08 VARCHAR2 (240),
OST_FLEX_09 VARCHAR2 (240),
OST_FLEX_10 VARCHAR2 (240),
OST_FLEX_11 VARCHAR2 (240),
OST_FLEX_12 VARCHAR2 (240),
OST_FLEX_13 VARCHAR2 (240),
OST_FLEX_14 VARCHAR2 (240),
OST_FLEX_15 VARCHAR2 (240),
OST_FLEX_16 VARCHAR2 (240),
OST_FLEX_17 VARCHAR2 (240),
OST_FLEX_18 VARCHAR2 (240),
OST_FLEX_19 VARCHAR2 (240),
OST_FLEX_20 VARCHAR2 (240),
OST_H_FLEX_01 VARCHAR2 (240),
OST_H_FLEX_02 VARCHAR2 (240),
OST_H_FLEX_03 VARCHAR2 (240),
OST_H_FLEX_04 VARCHAR2 (240),
OST_H_FLEX_05 VARCHAR2 (240),
OST_H_FLEX_06 VARCHAR2 (240),
OST_H_FLEX_07 VARCHAR2 (240),
OST_H_FLEX_08 VARCHAR2 (240),
OST_H_FLEX_09 VARCHAR2 (240),
OST_H_FLEX_10 VARCHAR2 (240),
OST_H_FLEX_11 VARCHAR2 (240),
OST_H_FLEX_12 VARCHAR2 (240),
OST_H_FLEX_13 VARCHAR2 (240),
OST_H_FLEX_14 VARCHAR2 (240),
OST_H_FLEX_15 VARCHAR2 (240),
OST_H_FLEX_16 VARCHAR2 (240),
OST_H_FLEX_17 VARCHAR2 (240),
OST_H_FLEX_18 VARCHAR2 (240),
OST_H_FLEX_19 VARCHAR2 (240),
OST_H_FLEX_20 VARCHAR2 (240),
OST_CR_UID VARCHAR2 (12),
OST_CR_DT DATE,
OST_UPD_DT DATE,
OST_UPD_UID VARCHAR2 (12),
OST_VALUE_DT DATE
  • Attachment: proof.pdf
    (Size: 75.69KB, Downloaded 770 times)
Re: TOAD update via ROWID [message #462755 is a reply to message #462754] Sat, 26 June 2010 14:02 Go to previous messageGo to next message
Littlefoot
Messages: 21505
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you want to do with those "invalid" records? You mentioned both UPDATE and DELETE. If it is DELETE, you don't need a query - delete them directly from TOAD's Schema Browser. The same goes for UPDATE - simply overtype those invalid values with valid ones.
Re: TOAD update via ROWID [message #462756 is a reply to message #462754] Sat, 26 June 2010 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
>Alphas in NUMBER fields.
>Dollars || Text in date fields.
Oracle absolutely, 100% precludes either of the above from occurring.

Since you choose to ignore Posting Guideline, I choose to waste no more time on this thread.
You're On Your Own (YOYO)!

[Updated on: Sat, 26 June 2010 14:09]

Report message to a moderator

Re: TOAD update via ROWID [message #462757 is a reply to message #462687] Sat, 26 June 2010 14:39 Go to previous messageGo to next message
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
Junior Member
I thank you for your time BlackSwan, suggestion noted.
However in the pdf, one can see oddity in NUMBER fields. But as requested, here is various information from system, and print screens from TOAD and SQLPlus.
I'm not able to UPDATE or DELETE the record, as Oracle is preventing translation of values in GUI of scheme browser. I can display them is query as noted in poof.pdf.

In advance I'll thank those who might take time to review this issue.
Re: TOAD update via ROWID [message #462762 is a reply to message #462757] Sat, 26 June 2010 17:45 Go to previous message
cookiemonster
Messages: 13686
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem you are having deleting the data is due to a trigger on the table - trg_ft_os according to the error message.
You could try disabling the trigger.
Previous Topic: Oracle Database Tools - colledge graduate work
Next Topic: unable to get all views in crystal report tool
Goto Forum:
  


Current Time: Fri Oct 18 11:39:19 CDT 2019