| Oracle Form Column Problem [message #466404] |
Mon, 19 July 2010 08:29  |
beginner88
Messages: 45
Registered: June 2010
|
Member |
|
|
Hello
I am facing a problem while creating a program using oracle forms.
These two table i am using in creating following program .
SQL> desc electricity ;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(40)
PREV_READING NUMBER(20)
PRESENT_REDAING NUMBER(20)
TOTAL_UNITS NUMBER(30)
AMOUNT NUMBER(30)
This Is ID table .
SQL> desc id
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
JAN NUMBER(10)
FEB NUMBER(10)
MAR NUMBER(10)
APR NUMBER(10)
MAY NUMBER(10)
JUNE NUMBER(10)
JULY NUMBER(10)
AUG NUMBER(10)
SEP NUMBER(10)
OCT NUMBER(10)
NOV NUMBER(10)
DEC NUMBER(10)
What i want that whenever user writes a value in "PRESENT_Reading" Column then it stores value in "ID table" .
For example if current month is July ,Then when user write a value in present Reading column ,it stores it in JULY Column of Id table .
i Write following trigger (Key_commit) On present_reading Column .
if sysdate=to_char(sysdate,'MON','Jan') Then
:electricity.PRESENT_REDAING := :ID.Jan ;
elsif
sysdate=to_char(sysdate,'MON','FEB') Then
:electricity.PRESENT_REDAING := :ID.Feb ;
elsif
sysdate=to_char(sysdate,'MON','MAR') Then
:electricity.PRESENT_REDAING:= :ID.MAR ;
....so on...
But when i run my form it does not work ,and raised unhandled Exception .
I think i need to change some properties of PRESENT_READING column of electricity table .
Any guidance would be appreciated 
Thanks .
[Updated on: Mon, 19 July 2010 08:32] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle Form Column Problem [message #466415 is a reply to message #466410] |
Mon, 19 July 2010 08:59  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well you say you need to assign a value to the appropriate column of the id column based on current value of PRESENT_READING but your sample code is doing the exact opposite.
Why not just use the pre-insert/update triggers?
Better yet, why not just add a date column to electricity and drop the id table.
You can get everything id holds with a simple query on electricity that way.
Having the additional table doesn't add anything useful, it just makes things more complicated.
|
|
|
|
|
|
| Re: Oracle Form Column Problem [message #466567 is a reply to message #466565] |
Tue, 20 July 2010 03:02 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Oracle
PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
|
|
|
|
| Re: Oracle Form Column Problem [message #466573 is a reply to message #466567] |
Tue, 20 July 2010 03:31 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I can't see any way you can get an archive of readings with that. What if you get more than one reading in a month?
And what about years?
Sticking a date column on electricity will give you all the traceability you should need. It'll certainly be more accurate and easier to code than using your id table as it stands.
And if you just moved the code you have in the first post into the pre-insert then it's still doing the opposite of what you say you want.
And shouldn't the month columns be the same size as PRESENT_REDAING? That's probably why you're getting a 6502 error.
EDIT: typo
[Updated on: Tue, 20 July 2010 03:31] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
