convert access boolean field value to oracle field value

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?
bmutchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use NUMBER(1) instead of CHAR(1) ,  or use ABS(-1) to save 0 and 1 instead of 0 and -1,,,
0
JonasMalmstenCommented:
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)
0
bmutchAuthor 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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So is there any way to convert the value in a trigger?
no, as the trigger will happen AFTER the "-1" is rejected by the basic data type test against CHAR(1)

your only choice is to use VARCHAR2(2) instead, and then you could use the trigger to remove the "-" from "-1"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JonasMalmstenCommented:
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;
/
0
JonasMalmstenCommented:
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;
/
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What she said ...
correction needed:
>What he said ...
without offense, btw :)

0
JonasMalmstenCommented:
ops, appologies :)
0
bmutchAuthor 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.
0
JonasMalmstenCommented:
It means that the trigger will execute for each row that you insert or update, not for each row in the whole table.

If you ommit "for each row", the trigger will only fire once for each insert statement (that can possibly affect multiple rows). Consequently, if you ommit it you will not be able to access the :new.b variable.
0
bmutchAuthor Commented:
ok, very helpful, thanks both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.