We help IT Professionals succeed at work.

convert access boolean field value to oracle field value

bmutch
bmutch asked
on
Medium Priority
580 Views
Last Modified: 2013-12-19
We have a dynamic series of insert queries that get run on an Access DB from dotnet program. We have duplicated the tables in Oracle (don't ask why) and these table need to get updated with the same queries. One problem I've seen so far is the boolean (Yes/No) Access fields we have converted to char(1) fields in Oracle so when it tries to insert using the query:
INSERT INTO ITEMMASTER
(StoreNum, BatchNumber, FileType, SKU, DESCRIPTION, ACTIVE, PRD_LVL_CHILD, PRD_LVL_PARENT)
VALUES (427, '2009051819010600', 'I', 29254, 'Kool Family B1G1F Sng Pk-Test-Inactive', -1, 27480, 205)

for example the -1 doesn't fit in the char(1) field. Can we do something like put a trigger on the ACTIVE field and convert the -1 value into '1' so it will fit?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you could use NUMBER(1) instead of CHAR(1) ,  or use ABS(-1) to save 0 and 1 instead of 0 and -1,,,
When using oracle, a char(1) that equals 'Y' or 'N' is the most common way to represent a boolean value.

You can translate into this using Decode(value, '0', 'N', '-1', 'Y', null)

in your example that would be Decode(-1, '0', 'N', '-1', 'Y', null)

Author

Commented:
This is going to be occurring in vb code and it will be a pain to have to parse the query string and replace the value, I will have to hunt and peck at the string to find the "boolean" field location for the given table in the string, I am reading these queries in from a text file, then I have to run each query in Oracle via ado.net. So is there any way to convert the value in a trigger?
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
What she said, for example:

create table test (b varchar2(2));

create or replace trigger tr_iu_test
before insert or update on test
for each row
begin  
  select decode(:new.b, '-1', 'N', '0', 'Y', null) into :new.b from sys.dual;
end;
/
or even better:

create or replace trigger tr_iu_test
before insert or update on test
for each row
begin  
  select decode(:new.b, '-1', 'N', 'N', 'N', '0', 'Y', 'Y', 'Y', null) into :new.b from sys.dual;
end;
/
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>What she said ...
correction needed:
>What he said ...
without offense, btw :)

ops, appologies :)

Author

Commented:
thanks , can you possibly explain this in the trigger:
for each row ...

this doesn't mean it checks each row of the table every time there is an insert does it?
Concerned about performance.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok, very helpful, thanks both.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.