Forms :: Alpha Numeric Sequence Updating
May 18, 2012
We have document serial Nos increment program, which has the below query for selecting and Updating, which generates a Numeric Value;
Select Query:
Select doc_slno
from fin_jv_slno_mas
where jv_month = '05' and
jv_year = '2012'
Output of this Query: 800001
Update Query:
update fin_jv_slno_mas set doc_slno=docno+1 where
jv_year = '2012' and
jv_month = '05'
Output after Updation: 800002.
The above query is a normal Sequence updating for a Numeric Value; now we need to update it as Alpha Numeric as below
Example: 8A0001
How can we give the above update statement for Alpha Numeric updation?
View 16 Replies
ADVERTISEMENT
Jul 1, 2004
I have a function that will replace the contents of the input parameter and replace any non-numeric characters.� I just want to know if there is a more efficient way to code this (oracle 8i or higher).
function strip_non_numeric(p_string in varchar2) return varchar2 is
Result varchar2(100) := '' ;
x_length number;
begin
SELECT LENGTH(p_string) INTO X_LENGTH FROM DUAL;
FOR i in 1..X_LENGTH LOOP
[Code]....
View 6 Replies
View Related
Sep 22, 2009
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
View 3 Replies
View Related
Dec 22, 2003
I have a control file like following:
LOAD DATA
INFILE *
INTO TABLE member
REPLACE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
control_id,
name,
address
)
problem is that control_id value is not in data file and I have to assign to each row the same value generated from sequence or from unix variable.
For example, after I run sqlldr, I have to have records in the table like following:
control_id name address
---------- ---- -------
1847 Charlie 250 yonge st
1847 Peter 5 Brookbanks dr
1847 Ben 123 King st
.
.
.
How do I do that?
View 29 Replies
View Related
Sep 5, 2011
I am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.
CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....
I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..
View 33 Replies
View Related
Jan 18, 2012
I have migrated database from postgresql to oracle...All sequences are migrated with their default values...(Start with 1) I already have 213 entries in a table and I want to begin using this for 214th entry ( replace with "start with 214")
How can I automate the process of updating "Start with" value of sequence with the max no of entry on my table every time I migrate data....
I have created a trigger that will automatically insert the next number from the sequence into the id column.
create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/
View 2 Replies
View Related
Oct 10, 2011
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
View 14 Replies
View Related
Apr 22, 2013
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
View 13 Replies
View Related
Feb 7, 2013
I am on 11g.
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
VALID INPUT samples:
4
25
11F
361NG
8ABC
View 6 Replies
View Related
Jun 1, 2007
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
View 6 Replies
View Related
Dec 7, 2010
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
View 5 Replies
View Related
Jul 25, 2005
I have an issue in oracle form 6i. I want to create an input box that accepts only number. As the user press any key other than allowed for numbers it should not accept.
View 4 Replies
View Related
Apr 12, 2010
I've got a colleague that is trying to populate a list box item with a group of numeric values that represent highway route numbers.
The database view defines the route as a "number".
How can we create a numeric list box? The built-in functions populate_group, and populate_list both expect to get varchar2 parameters.
View 1 Replies
View Related
Mar 14, 2013
I have made a report with sql injection. there is no problem with the report. but when i run from the form with following string it gives error.
:global.where_clause := 'where request_type = 5 and request_status in (3, 4, 5) and list_approval_date is null';
And no error is there if i passed the string as
:global.where_clause := 'where request_status in (3, 4, 5) and list_approval_date is null';
REQUEST_TYPE is a database column and it is of number type.
View 9 Replies
View Related
Oct 2, 2010
i am generating html format mail from oracle 10g database.
For displaying data into html format, message body data is exceeding more than 32k.
ORA-06502: PL/SQL: numeric or value error:character string buffer too small
i am using long data type for message body data.
View 4 Replies
View Related
Mar 11, 2012
I am using Forms 6i,From my Form, user selects "sno" and upload BLOB image through OAF Upload utility. It stores the rowid of "SNO" and image...into Table "t1"...Now, i want to update the BLOB image into the original table"t2" using rowid..For this , i wrote the rowlevel trigger on Table "t1", in the trigger, i called a Procedure...In that procedure .. i am trying to update the BLOB column..It is not updating the BLOB column..But it is updating the Other values.. except BLOB column.
View 1 Replies
View Related
Mar 3, 2012
when I updated a record in my form check my screenshot: and clicked save button the result was:
as you can see in the message at the bottom, it has 18 records. And since my original data(subjects) are just 9. And also the grades that I input didn't display all.
This is my code in when-new-block-instance trigger:
DECLARE
CURSOR studgrade_cur IS
SELECT e.student_id, s.subject_code --, g.grade
[code]...
View 39 Replies
View Related
Mar 30, 2010
I have a form with two data blocks, one parent, one child block.
The parent is holds mineral lease info while the child holds the mineral owner info, such as addresses and phone numbers. One owner can be in the owner block multiple times (different owner types). The form only displays one owner at a time.
We have a separate master owner table which holds owner address. (We set it up this way because we get electronic info from mineral companies that we have to load each year).
As you tab through the owner block, it checks the FEIN against the master table and pulls updated address info from the master table.
I have a problem in which if an owner is on the lease multiple times, when you tab through the first instance, it pulls in the new address info, but when you go to the next instance, it won't update. If you requery, it seems that the first update actually updated all the owner records on that lease. How can I turn this off?
View 13 Replies
View Related
Sep 5, 2011
I create the sample for master/detail form. In detail for prdcode,rate,qty,amount is there. When select prdcode it fetching prdcode,rate in a record and if you type the qty the amout will come based on formula(property) :qty*:rate.
It is available on screen. But when i store the data, in backend table the amount is be a null.
View 9 Replies
View Related
Jun 8, 2013
I AM WORKING ON FORM I WANT TO SAVE THE RECORD IN TO TABLES TABLE1,TABLE2 AFTER UPDATING THE
TABLE1..BOTH THE TABLE HAVE SAME COLUMNS'.
WHAT IS THE TRIGGER FOR THAT
View 2 Replies
View Related
Oct 30, 2013
i m doin simple application, in which all data is retrieving from table in to form(all the
field are textbox and button on form) ename,dept,salary,dept_id etc.
i took one textbox field on form which is non table field
(no db field bt linked to another table..via LOV m retrieving value into this textbox which suppose dept_id ) and now after selecting one record from list box into textbox which is non table field.... after immdiately selecting i want to update one field into db field which all other form field contains...
so how to do this??which trigger i hav to use for this?
if :dept_id is not null then
begin
update dept
set loc =:emp.loc
where dept_id = :emp.dept_id;
forms_ddl('commit');
exception
when others then
null;
end;
end if;
it giving error.......if m using commit in this...i hav used post query trigger ...is dere
any other trigger hav to use
View 3 Replies
View Related
Dec 12, 2011
I am using OLE2 to update an excel spreadsheet from Oracle Forms 6i. If the excel spreadsheet is already open, the code runs through fine, and even asks if you want to replace the file when running the lines: OLE2.INVOKE(worksheet, 'Save');
OLE2.INVOKE(workbook, 'Save');
However, the file never gets updated. There is no error that appears. I would like to have the program check if the file is open before running the program;
View 1 Replies
View Related
Jan 31, 2013
SELECT COUNT(*)
INTO v_ctr
FROM leave_type_govt_service
WHERE (
:block_name.min BETWEEN minimum AND maximum
[code]......
This code in my forms really works for inserting a new record. But if I will update the record itself for example, in the table 'Type 1'
Minimum Maximum
1 5
6 10
11 15
and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
View 1 Replies
View Related
Feb 11, 2010
is it possible to change the forms trigger sequence.
for example, i am writing 'when_validate_item' in item level,block level,form level.First item level will fires and then block level and at last form level. But i want to fire the trigger first at form level then block level and at last item level.
View 6 Replies
View Related
Feb 1, 2011
i want to insert some data in the transactions table on after insert trigger. Which trigger should i use on form level to accomplish this task.
View 4 Replies
View Related
Oct 17, 2012
I have not used Oracle Forms and Reports for some time. The last version I used was Developer version 6.
Has there been major changes between Oracle Developer version 6 and Oracle Developer version 11 or is Oracle Developer basically the same with some incremental improvements? What would be a good way to update my skills from Oracle Developer version 6 and Oracle Developer version 11?
View 1 Replies
View Related
Jul 15, 2013
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
View 1 Replies
View Related
Jul 5, 2010
I have a non-base-table item which I want to update in the pre-update trigger of the current block.
If the content of the field exceeds 4000 characters, i get the error message
ORA-01461: can bind a LONG value only for insert into a LONG column.
The code is
update tab set long_col = :formsblock.long_col
where tab.tabpk = :formsblock.foreign_tabpk;
Workarounds would be,
1.) to delete the old dataset and insert the new one:
delete from tab
where tab.tabpk = :formsblock.tabpk;
insert into tab (tabpk, long_col) values
(:formsblock.foreign_tabpk, :formsblock.long_col);
or 2.) to change the Item from a non-database item to a database item and use the internal update of the forms-module, but both workarounds are not very satisfying.
Do you know another way to update the LONG-column within the pre-update trigger (or any other PL/SQL part of forms)?
View 2 Replies
View Related
Aug 22, 2013
I am using Forms 6i and developed a Master Detail Oracle Form for Oracle EBS 11i application.
I was asked to write a pl/sql program so that.. When a user updating an existing record at the same time another user query the same record and try to update the same time, the record should be locked and a message should popup saying the record is being updated by another user.
View 2 Replies
View Related
Jan 27, 2011
've form which is having 3 tabs in canvas, i want to open second tab first when we open form,
View 4 Replies
View Related