Hi,
My '88 Bonneville, (3800 V6, same engine as yours) developed the same problem.
Likely, your cam sensor itself is fine; it's the magnet on the camshaft which is going bad. This was a common problem, but difficult to fix because the engine must be removed to get the camshaft out.
This cam sensor circuit is only used to initially time the EFI system on initial start up. So long as it produces a reading when cold when you crank the engine, the EFI will work properly, even if the signal is lost as the engine warms up.
The problem you may have is a 'hot' start because, at that time, you will have no signal. At this time, the EFI system will go into a default mode where, instead of firing fuel injectors sequentially (one at a time) it will fire them two at a time, (because the EFI computer is unsure of the exact start position of the camshaft)
I drove my Bonneville thousands of miles on this default mode, with no bad effects. I noticed there was occasionally a very slight (most drivers would never notice) hesitation when accelerating.
Gas mileage was not affected, and full throttle acceleration was slightly faster (!) than during normal operation, probably due to a slightly richer mixture.
Also, my emissions were as clean as ever.
Personally, the repair for this problem would easily run over $1000. This is a lot of money to solve a problem which has no real-world symptoms except for the 'check engine' light. IMO just drive the car and do not bother fixing the problem. It may be reasonable to scan for codes at each oil change to see if any additional codes have appeared, (indicating possible unrelated problems)
You an easily scan for codes yourself on these GM cars, you do not need a garage to do this for you.