I have a form with a datablock displaying 3 text item fields.But the actual oracle table have 8 columns.When ever I insert new data and save it, only the three records are being stored in backend whereas remaining column stores with null value.That is the basic function of the form.
I placed an another list item field in a control block which is non-database item.Now I want to store the list item value into a column along with the 3 columns.In a simple way, how to insert record manually along with the other records?
I am working on form builder 6i. I have multi record datablock 't1'.' I have 'Submit' Button, which contains 'commit_form'. I am able to insert records.
Now i have a check box in data block, i want to insert records which are checked. I followed below thing to achieve that.
ON-INSERT Trigger:
If check_box ='Y' THEN INSERT_RECORD end if.
SUBMIT BUTTON: commit_form.
Let us say, i have 5 records, 2 records are checked, after clicking SUBMIT button, it's inserting 2 records only. But in console window, it's showing '5 transactions are saved'. I want see '2 transactions are saved' message in console window.
How to get it or any other solution which will insert records which are checked and show correct message in console window.
I am getting this error FRM 40508 in inserting data to any table through forms 11g.
eg:
my test table having two columns : id and name
i wrote a simple insert statement insert into test values (:id, :name); commit;
when i run the application and insert the values, it shows the above error.
when I use the display errors , it shows that there is a violation : ora - 00001 (duplicate values)
however, there is no such value in the database.
The same scenario is repeating with every table I have.
I thing more, when I use the commit_form only without using any manual insert statement, then everything goes well. But with manual insert it all fails.
I want to update / insert records from one user to other user therefore I write this code in my form but it gives me error.
REPORT_DETAILS is a detail table of REPORT_PARAM, given code is working fine at master tables.
Form always goes in TOO_MANY_ROWS exception.
Correct my code, my requirement is: if data found then update else insert new record.
declare cursor c1 is select * from factory_data.report_details order by rd_rp_id; rec c1%rowtype; v_check varchar2(1) := null; begin for rec in c1 loop [code].......
I have a multi-record block with a check box(:B_ASTHEAD.CB_CHECK) for each record. user can select the record by clicking on the check box. when checked, value is 'Y' and when unchecked, value is 'N'.
when user clicks on 'OK' button (when button pressed trigger), the records which are selected(Checkbox) must be posted to the database table.
L_last_Rec number; L_first_Rec number; L_AST COP_DETL.AST%TYPE; BEGIN
[Code]....
The issue here is , check box selected records are not getting inserted into table when OK button is clicked and i could see that all checked checkboxes become unchecked immediately . But if i comment the --"if :B_ASTHEAD.CB_CHECK = 'Y' then " part, then insert works , but inserts all record.
I have datablock 'EMP', it's multi record block. I have a button 'CREATE', I have 'commit_form' in 'When button pressed' trigger, to commit records. It's working fine.
Now, i have a check box in the same block, i want to insert records which are checked only.
I am working in form 6i, EBS11i. I have a multi record data block, i am inserting checked records only using below logic.
ON-INSERT Trigger:
if checkbox_checked('block.checkbox') THEN insert_record; end if;
Requirement: Let us say, i have 4 records, i checked 2 records.. inserted them. Now if i want to insert other 2 unchecked records, it's not accepting, is it possible to insert records which are not checked after insertion.
I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
//when-validate-item declare i number; begin if :a.b is null then i:=show_alert('null'); else if :a.b between 1900 and 2013 then message('date valid'); else i:=show_alert('date_invalid'); :a.b:=null; raise form_trigger_failure; end if; end if; exception when others then i:=show_alert('exception'); end;
the problem for example when I entered a value "3000" I received two messages (the first on the alert "date_invalid" and the 2nd on the exception message) but normally I must received one message, the relative "date_invalid"
I know how to design master_detail form using wizard but i want to create it without using wizard.I have created two blocks emp and dept without using wizard ,and connected them to emp and dept table by changing their properties.Now i want to create relationship between these two blocks, My requirement is when i press execute_query button if deptno is 10 in dept block then emp block should show data for deptno 10 only.
I know it can be done using add relationhip option in dept block (which is master block)but how to do that.
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
I want to insert 10 records from table a to table b. If i m using statement level trigger how many record insert?In row level trigger how many record inserted?
Insert multiple record in table. I have a table of customers . It has column cus_name, cus_fruit, cus_date, cus_qty.
Select * from customers; cus_name cus_fruit cus_date cus_qty Maria Anders Apple 18-July-2013 10 Maria Anders Apricot 18-July-2013 20 Maria Anders Asparagus 18-July-2013 100 Maria Anders Avocado 18-July-2013 5 Ana Trujillo Apple 18-July-2013 10 Ana Trujillo Apricot 18-July-2013 20 Ana Trujillo Asparagus 18-July-2013 100 Ana Trujillo Avocado 18-July-2013 5
how I can insert record in one time in table. All table data same only change the cus_name.
Thomas Hardy Apple 18-July-2013 10 Thomas Hardy Apricot 18-July-2013 20 Thomas Hardy Asparagus 18-July-2013 100 Thomas Hardy Avocado 18-July-2013 5
After Insert record result. Select * from customers;
cus_name cus_fruit cus_date cus_qty Maria Anders Apple 18-July-2013 10 Maria Anders Apricot 18-July-2013 20 Maria Anders Asparagus 18-July-2013 100 Maria Anders Avocado 18-July-2013 5 Ana Trujillo Apple 18-July-2013 10 Ana Trujillo Apricot 18-July-2013 20 Ana Trujillo Asparagus 18-July-2013 100 Ana Trujillo Avocado 18-July-2013 5 Thomas Hardy Apple 18-July-2013 10 Thomas Hardy Apricot 18-July-2013 20 Thomas Hardy Asparagus 18-July-2013 100 Thomas Hardy Avocado 18-July-2013 5
I want to know how we can insert more than 3 million records from one table to another table. Can we use Bulk collect and forall to insert the all data.Can we use create table tableB as select * from tableA; From the above which is one is performance wise good.
I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.I had seen in posts that merge cannot be used with cursor.
SQL> create or replace 2 PACKAGE sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /******************************************************************** ****************** 5 PACKAGE: sis_load_cpl_sis_reb_pgm_hist 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_p h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) 8 ********************************************************************* ******************/ [code].......
Package created.
SQL> create or replace 2 PACKAGE BODY sis_l_cpl_sis_reb_pgm_hist_pkg 3 IS 4 /********************************************************************** ****************** 5 PACKAGE: sis_l_cpl_sis_reb_pgm_hist_pkg 6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_pur h_dtl,cmply_sis_sls_dtl, 7 cmply_sis_excl_dtl(intial load) [code].......
Warning: Package Body created with compilation errors.
SQL> sho err Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I have created below function to remove specific words/special characters from string. This function is producing expected result. Using this function i need to insert around 900000 records in name_compress table. Insert is taking around 7 mins, how we can tune this function so that insert will be executed within 1-2 mins.
Function -
CREATE OR REPLACE FUNCTION NAME_FN(IN_STRING1 VARCHAR2) RETURN VARCHAR2 IS V_OUTPUT VARCHAR2(300); V_OUTPUT1 VARCHAR2(300); V_OUTPUT2 VARCHAR2(300); V_OUTPUT3 VARCHAR2(300);
I am having trouble changing a non-displayed field when a displayed field is changed.
When a user wants to un-assign a territory they want to just null the ID field (non-database item). I then need to null the PIDM field(not visible, database item) for that one record.
I tried this, but I end up putting null on all the records instead of just the one record, which ends up un-assigning all the territories not just the one.
Is there a way to assign the null to the PIDM field associated to the ID field of that particular record.
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1 BRANCH = 2B EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28 [code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
