RE: Datatype Conversion!!!

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 18 Dec 2000 15:23:30 -0500
Message-Id: <10714.124862@fatcity.com>

Ahmed,

This is a pretty simple trigger -- and you won't even have a mutating tables error! ;-)

Warning: untested, may not compile, but should give you the idea.

create or replace trigger biur_pd_valid_data -- Stands for before insert update row
before insert or update of valid_data on pd_valid_data for each row
declare

	cursor get_measure_unit is	-- Cursor to get the measure unit
		select measure_unit
		from pd_keyword_cat_char
		where keyword = :new.keyword
		and categroy_code = :new.categroy_code
		and char_code = :new.char_code;	-- I'm guessing on your
foreign key, here.
	munit	pd_keyword_cat_char.measure_unit%type;
begin
	open get_measure_unit;
	fetch get_measure_unit into munit;
	close get_measure_unit;

	if munit is not null then	-- The data in valid_data must be
numeric
		-- This will throw an exception if the data is not numeric.
		--
		:new.valid_data := to_char(to_number(:new.valid_data));
	end if;

end biur_valid_data;

-----Original Message-----
From: Ahmed Baig [mailto:abaig_at_saudioger.com] Sent: Thursday, December 14, 2000 5:15 AM To: Multiple recipients of list ORACLE-L Subject: Datatype Conversion!!!

Hi List,

  We have got a situation where we need to change the datatype of a field at runtime.Following is the scenario for it

         Table  1				         Table 2
 pd_keyword_cat_char   			     pd_valid_data
 
  Keyword       varchar2(3)		           Keyword       varchar2(3)
  categroy_code number(5,0)                 categroy_code number(5,0)
  char_code       varchar2(5)                   char_code       varchar2(5)
  measure_unit   varchar2(5)                   valid_data       varchar2(70)
  must_flag        varchar2(1)                   date_created   date

  printing_order  number(2,0)                  created_by      varchar2(30)
  date_created   date                             date_modified  date
  created_by      varchar2(30)                  modified_by     varchar2(30)
  date_modified  date
  modified_by     varchar2(30)
 

Requirement: On the form based on each of the above table we would like the VALID_DATA field of the PD_VALID_DATA table to accept only number data if the MEASURE_UNIT field of the PK_KEYWORD_CAT_CHAR table has data in it,else the VALID_DATA field has to accept only character data.

Thanks in Advance
Afaq Baig    

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ahmed Baig
  INET: abaig_at_saudioger.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Mon Dec 18 2000 - 14:23:30 CST