Re: INSERT command question

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Fri, 07 Oct 2005 17:04:36 +0200
Message-ID: <1128697476.38@user.newsoffice.de>

Jim Kennedy schrieb am 07.10.2005 in
<R_adnb__hP-GH9veRVn-sA_at_comcast.com>:

> "Tarby777" <nick_williamson_at_mentorg.com> wrote in message
> news:1128692544.251221.221500_at_g44g2000cwa.googlegroups.com...

>> Hi all,
>>
>> I'm copying from one 9i table to another, and populating a UID column
>> at the same time, like this:
>>
>> INSERT INTO table_1 (col1,col2,uid_column)
>> (SELECT col1,col2,SYS_GUID() from table_2);
>>
>> The system-generated UID is fine, but I need to reformat it for
>> application reasons, so I'm taking a second pass through the table
>> after the INSERT, like this:
>>
>> UPDATE table_1 SET uid_column='UID' || SUBSTR(uid_column,1,6) || '-'
>> SUBSTR(uid_column,7,11) || '-' || SUBSTR(uid_column,18);
>>
>> What I'd like to do is reformat the UID in the INSERT command, rather
>> than having to have a separate UPDATE command that runs afterwards. Is
>> that possible? I appreciate that it's possible within an iterating
>> PL/SQL block, but I'd like to do it in a single command if I can...
>>
>> TIA
>> Nick
>>

> INSERT INTO table_1 (col1,col2,uid_column)
>    (SELECT col1,col2,
>              'UID' || SUBSTR(sysdual.guid,1,6) || '-'
>  SUBSTR(sysdual.guid,7,11) || '-' || SUBSTR(sysdual.guid,18)
>             from table_2,
>    (select SYS_GUID() as guid from dual) sysdual);

To avoid this I wrote a stored procedure that encapsuletes this. INSERT INTO table_1 (col1,col2,uid_column) (SELECT col1,col2,NewGUID() from table_2);

with
function NewGUID return varchar2 as

  lvsGUID varchar2(40);
  lvsResult varchar2(40);
begin
  --{5A7A7F02-40BA-4503-99F2-092C2B35B039}   select
    sys_guid
  into
    lvsGUID
  from
    dual;

  lvsResult:=substr(lvsGUID,1,8)||'-';
  lvsResult:=lvsResult||substr(lvsGUID,9,4)||'-';
  lvsResult:=lvsResult||substr(lvsGUID,13,4)||'-';
  lvsResult:=lvsResult||substr(lvsGUID,17,4)||'-';
  lvsResult:=lvsResult||substr(lvsGUID,-12);

  return
    '{'||lvsResult||'}';
end;

> I think that would work. Try it on a small test case. Jims version should work, but if you have this situation in more than 1 cases you IMO should use a central function for it.

> Jim
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Fri Oct 07 2005 - 10:04:36 CDT