Thanks for the question, mahbuba.

Asked: January 19, 2002 - 9:57 pm UTC

Last updated: May 01, 2016 - 10:59 am UTC

Version: 8.1.6.0

Viewed 10K+ times! This question is

You Asked

We have a table named DETAIL_RECORD having c column 'REF_DGNFILE' with about 3400 records.

The field value of REF_DGNFILE are as below:

SQL> SELECT REF_DGNFILE FROM DETAIL_RECORD;

REF_DGNFILE
----------------------------------------
SS1106.CIT
SS1501.CIT
SS1506.CIT
SS1509.CIT
SS1510.CIT
dpc.d33
SS1511.CIT
SS1512.CIT
ss0600.d11
ss0600.cit
SC3.TIF

The structure of the table is as below:

SQL> DESC DETAIL_RECORD
Name Null? Type
------------------------------- -------- ----
RB_PRMRY NUMBER(10)
RB_SCNDRY NUMBER(10)
MSLINK NUMBER(10)
RB_LOCK NUMBER(5)
RB_FSC NUMBER(5)
RB_STATE NUMBER(5)
RB_COMPONENT NUMBER(5)
RB_VERSION NUMBER(5)
RB_OCCURRENCE NUMBER(5)
REF_DGNFILE CHAR(40)
FILETYPE NUMBER(5)
LOWER_LEFT_X NUMBER(10)
LOWER_LEFT_Y NUMBER(10)
UPPER_RIGHT_X NUMBER(10)
UPPER_RIGHT_Y NUMBER(10)

My QUESTION are :

1. My boss told me to update the above mentioned field value with a prefix 'LY_' for all
values of this field (as for example, Instead of 'SS1106.CIT', it should be 'LY_SS1106.CIT').

I tried in the following way but could have a luck because of lack of experiences.

Please,help me out.

SQL> ED
Wrote file afiedt.buf

1 UPDATE DETAIL_RECORDT
2 SET REF_DGNFILE = (SELECT 'LY'||REF_DGNFILE FROM DETAIL_RECORDT
3* WHERE LENGTH(REF_DGNFILE)>5)
SQL> /
SET REF_DGNFILE = (SELECT 'LY'||REF_DGNFILE FROM DETAIL_RECORDT
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

SQL> ED
Wrote file afiedt.buf

1 UPDATE DETAIL_RECORDT
2 SET REF_DGNFILE = (SELECT 'LY'||REF_DGNFILE FROM DETAIL_RECORD
3* WHERE DETAIL_RECORDT.RB_PRMRY= DETAIL_RECORD.RB_PRMRY)
SQL> /
UPDATE DETAIL_RECORDT
*
ERROR at line 1:
ORA-01401: inserted value too large for column

SQL> ALTER TABLE DETAIL_RECORD
2 MODIFY(REF_DGNFILE CHAR(150));

Table altered.

SQL> UPDATE DETAIL_RECORDT
2 SET REF_DGNFILE = (SELECT 'LY'||REF_DGNFILE FROM DETAIL_RECORD
3 WHERE DETAIL_RECORDT.RB_PRMRY= DETAIL_RECORD.RB_PRMRY);
UPDATE DETAIL_RECORDT
*
ERROR at line 1:
ORA-01401: inserted value too large for column

SQL> ALTER TABLE DETAIL_RECORDT
2 MODIFY(REF_DGNFILE VARCHAR2(1000));

Table altered.

SQL>
SQL> UPDATE DETAIL_RECORDT
2 SET REF_DGNFILE = (SELECT REF_DGNFILE FROM DETAIL_RECORD
3 WHERE DETAIL_RECORDT.RB_PRMRY= DETAIL_RECORD.RB_PRMRY);
SET REF_DGNFILE = (SELECT REF_DGNFILE FROM DETAIL_RECORD
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

2.How to make a prefix , suffix by default in the structure of the field while creating a table?

and Tom said...

1) yuck, why char(40), never use CHAR -- always use varchar2. CHAR is just a blank padded varchar2. It only makes life hard, wastes space.

You want to

update detail_recordt
set ref_dgnfile = 'LY_' || rtrim(ref_dgnfile);

thats all.

2) create or replace trigger detail_recordt_trigger
before insert on detail_recordt for each row
begin
if ( nvl(substr(:new.ref_dgnfile),'x') != 'LY_' )
then
:new.ref_dgnfile := 'LY_' || :new.ref_dgnfile;
end if;
end;

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Prefix or Suffix

mahbubarahman, January 21, 2002 - 4:25 am UTC

Updating is working fine but while creating the trigger for new records I have faced the following:

Plz, extend your experienced hands to help me.

SQL> create or replace trigger detail_recordt_trigger
  2     before insert on detail_recordt for each row
  3     begin
  4          if ( nvl(substr(:new.ref_dgnfile),'x')= 'LY_' )
  5          then
  6              :new.ref_dgnfile := 'LY_' || :new.ref_dgnfile;
  7          end if;
  8     /
  9  /

Warning: Trigger created with compilation errors.

SQL> INSERT INTO DETAIL_RECORDT(REF_DGNFILE) VALUES('3245-2004');
INSERT INTO DETAIL_RECORDT(REF_DGNFILE) VALUES('3245-2004')
            *
ERROR at line 1:
ORA-04098: trigger 'DETAIL_RECORDT_TRIGGER' is invalid and failed re-validation


1.Please, tell me with examples how to solve the above problem.

2.that means even if I create a New table where I want a   
  prefix to be added by default,
  I have to create a trigger for that.
  Is there any other way without trigger where my data      
  entry operators need not to include the prefix in       
  their entry making their job easier?

January 21, 2002 - 9:47 am UTC

1) lose the redundant / on line 8.  It should be the word:

   end;


when you get an error like that, simply:

SQL> show errors trigger detail_recordt_trigger

2) your client application could provide it instead.  If you want LOGIC behind the table, we do that with a trigger.  You can put the logic IN FRONT of the table by coding it in your application.  Totally 100% up to you.

need some more insights on suffixing a column value

santhosh, April 29, 2016 - 4:40 pm UTC

any other way for suffixing..
i mean like adding some check constraints kind of.. that constraint will add suffix..

i am trying to think of alternative than trigger.

April 29, 2016 - 5:25 pm UTC

A constraint won't add a suffix. It'll just ensure that data matches a condition.

If you don't want to use a trigger, place it in your code calling the insert/update.

Wondering

Ghassan, April 30, 2016 - 6:42 am UTC

Well this topic had started in 2002!
Is the requester db still 8.× ??
If not maybe the vertual col new feature could be another solution

May 01, 2016 - 10:59 am UTC

Good point.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Prefix or Suffix

need some more insights on suffixing a column value

Wondering

More to Explore

PL/SQL demos

PL/SQL docs

Prefix or Suffix in a field value

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Prefix or Suffix

need some more insights on suffixing a column value

Wondering

More to Explore

PL/SQL demos

PL/SQL docs