SQL & PL/SQL :: Unable To Insert Into Another Table?
Mar 25, 2011
i am facing a small problem (!!) while inserting into a table by selecting from another table.
let me simulate.
while execute the select query, i am able to view the records. but when the data inserted into the new table, empty column value is getting inserted. this problem with only one column.
Table 1 : data to be selected.
CREATE TABLE DRI_TEST2
(
FM_COMPANY VARCHAR2(4 BYTE)
)
[Code]....
if we select the select clause we will be able to see the records. but while inserting empty values are getting inserted.
View 15 Replies
ADVERTISEMENT
Jun 10, 2011
I am unable to insert the rows into table after creation of trigger because Mutating error was getting.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NAME VARCHAR2(10)
EMP_NO VARCHAR2(10)
SQL> select * from test;
EMP_NAME EMP_NO
---------- ----------
ORACLE PC729
JAVA PC047
C PC0729
SQL> create or replace trigger trig_test
2 after insert on test
3 for each row
4 declare
5 ename varchar2(10);
6 eno varchar2(10);
7 begin
8 select emp_name,emp_no into ename,eno from test
9 where emp_no ='1';
10 insert into test2(emp_name,emp_no) values(
11 ename,eno);
12 end;
13 /
Trigger created.
SQL> insert into test values ('PRO','1');
insert into test values ('PRO','1')
*
ERROR at line 1:
ORA-04091: table APPS.TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.TRIG_TEST", line 5
ORA-04088: error during execution of trigger 'APPS.TRIG_TEST'
SQL> spool off;
View 5 Replies
View Related
Dec 16, 2008
I want to insert XML data into my ( Oracle 11G Release 1 ) XMLType table using OCCI. I'm getting
ORA-01461: can bind a LONG value only for insert into a LONG column
My XML data size is around 1.5 to 2MB. I have also tried using setMaxParamSize before calling the setString method. But, still I'm getting the same exception.
How to resolve this issue?
View 2 Replies
View Related
Aug 4, 2010
In oracle 11i, unable to insert rows using insert statement. unable to retrieve data using select statement.
View 5 Replies
View Related
Oct 7, 2012
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.
View 3 Replies
View Related
Feb 20, 2009
I have inserted mathematical symbols e.g. quare root,pie etc etc. But when I come to query them they are represent by '?' mark.
View 5 Replies
View Related
Oct 16, 2012
I have this error. To put it simply I have two blocks.
Block1 contains two drop down list with PL/SQL statements for queries.
Block2 contains tabular form created from block wizard (I tried already in manual)
that will catch the result in Block1 queries.
Now I have a button with a trigger when-button-pressed that contains
BEGIN
INSERT INTO dummy1
VALUES ('hello',1,2,3);
COMMIT;
END;
My goal is to add into dummy1 values from :block2.item_name1, :block2.item_name2, :block2.item_name3 but to put it simply I tried these values but I received the same error.
When I run it and first things first click the button, the values will be added into dummy1 table but when I execute the block1 - dropdown list queries and try to press the button. I received the error.
View 10 Replies
View Related
May 23, 2011
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.
Insert only checkbox selected records.
View 11 Replies
View Related
Apr 17, 2013
I am unable to insert string of Hindi language characters with 1800 character length in column having data type VARCHAR2(4000).
I am getting error "ORA-01461: Can bind LONG value only for insert into a LONG column"
NLS parameters for database are :
PARAMETERVALUE
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
[Code]....
Is there any way to insert this string in VARCHAR2 column? I can't change data type to CLOB (with which I able to insert this string).
View 1 Replies
View Related
Jun 6, 2013
we unable to insert Georgian language in oracle forms and reports,while we insert it displasys as ? note my database characterset is utf8 and my Client PC NLS_char=american_america.utf8.
View 1 Replies
View Related
Jan 14, 2013
I have an interface composed of five elements:
1.display_item (primary key)
2-list_item (foreign key) / / dynamic list :this is the cause of the error
3-lis_item (foreign key) / / dynamic list
4-text_item
5-button (insert commit)
when I click the button, an error is displayed:
FRM-40508: ORACLE error: unable to INSERT record.in detail: ora-02291 integrity constraint Violated - parent key not found
Note:
-the elements of two lists already exist in the parent table!!
-I use a block based.
-button
commit_form();
View 17 Replies
View Related
Jun 14, 2013
We are getting problem with the Chinese character set. My current character set is as follows.
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
[code]....
My column description for the table product is as follows.
PART_NBRVARCHAR2 (30 Byte)
PART_DESCNVARCHAR2 (2000)
when trying to insert Chinese character using the insert command below
insert into product(part_nbr,part_desc,cust_name) values('322341',unistr('功'),'test');
I am getting the value when selecting the same record using the select command
select a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'322341¿test
When I running this command on TOAD
select a.rowid,a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'
and manually editing/inserting '功' character in output from select command above. After that I am able to get the same Chinese character when I am running select next time.
View 7 Replies
View Related
Mar 29, 2011
I am using oracle 9.2.0.6.0 on HP-UX.
I am unable to insert and even display euro symbol from server as well as windows client.
Following are the details of my database server
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
[code]....
1) When I try to insert € from Db server (using putty) using Alt+0128 it does not print anything (nothing gets typed on the screen). Not even junk characters Also following query does not print anything
SQL> select chr(128) from dual;
C
-
2) while I set NLS_LANG on client and try to insert €, Alt+0128 produces a question mark symbol And following query displays junk character
SQL> select chr(128) from dual;
C
-
Ç
3) Regardless when I inserted couple of rows and tried UNISTR following was the result
SQL> select unistr(v) from t;
Error 45 initializing SQL*Plus
Internal error
$
View 19 Replies
View Related
Jun 12, 2012
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
View 2 Replies
View Related
Jul 17, 2013
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
[code]....
View 2 Replies
View Related
Feb 20, 2013
I have a table with a BLOB column ;
I want read data from table and insert to another table with a cursor
My code is :
procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;
[code]....
when I run form , error FRM-40734 occurred
error in line " fetch .... "
View 1 Replies
View Related
Aug 24, 2013
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
View 2 Replies
View Related
Dec 28, 2010
I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..
[URL].......
how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?
assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?
create materialized view my_view refresh fast as select * from t1;
create materialized view log on my_view;
and then schedule in a dbms_jobs?
View 1 Replies
View Related
Mar 25, 2013
following is the requirement
External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....
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
View 1 Replies
View Related
Mar 9, 2004
Trying to auto insert the newest records from one table into another Table. I have a vendor provided table that is part of my database (running Oracle 9i) so I can't change the underlying structure to it or their process stops fluxing. However, I can add a trigger to it. What I want to do is this:
When the vendor's software inserts a new row (through their own automated process) I want to insert data from that same new record into another table of my own. (where of course I can re-format it, etc., and make the data my own)
The original vendor table does not have a insertion timestamp field to work off of.What is the best way to trigger an insert off the latest inserted record? It works to replace all the records in the entire vendor table but I only want to insert one record at a time.
View 2 Replies
View Related
Aug 2, 2012
I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .
If their is any problem in insert in to table t2 , the truncate command should not work .
Truncate command should work only after successfully insert command .
View 3 Replies
View Related
Aug 8, 2012
how to adjust a total (counter) after a record is inserted into a table.
the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:
create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));
[Code]..
IDNUMBER APPEALCODE BATCHNO TRA TRANSNUM SPLIT_TRANSNUM
---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822
create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));
[Code]...
APPEALCODE TOTAL# TOTAL$
-------------------- ---------- ----------
DVFG1206 100 2500
during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed appealtable.total# will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).
mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.
I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.
CREATE OR REPLACE TRIGGER PPO_Payment
AFTER INSERT ON paytable
FOR EACH ROW
DECLARE
p_cnt NUMBER :=0;
[Code]...
show errors
View 1 Replies
View Related
Sep 6, 2013
I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.
(Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).
View 8 Replies
View Related
Aug 5, 2008
The first problem I have is inserting data from one table to another WHILE incrementing a sequence:
Insert into PROG
(prog_cd, nm, typ_id, grp_id, clas_id, cat_id, shrt_nm, fl, prog_id)
values
((select one, two, three, four, five, six, seven, eight from LOAD), prog_seq.nextval);
I want to load the table PROG with the data in LOAD and increment a seq and put the value in prog_id, which is a field in PROG.
When I run it, I get:
ORA-00947: not enough values
View 4 Replies
View Related
Sep 14, 2012
For a table tab the values are:
tab
id() value1 value2 value3 vale4 value5
001 state wb
001 year 2012
001 dist kol
001 age 27
Now I want to insert this data in another table tab1 and the output of that table will be
id value1 value2 value3 value4
001 wb 2012 kol 27
find out how i can insert the date like this.
View 9 Replies
View Related
Mar 22, 2011
why am i getting this?
SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');
TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD
but the table exists
SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;
COUNT(*)
----------
3704
SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';
no rows selected
why is it showing no rows even if the table exists
View 3 Replies
View Related
Nov 15, 2012
DB version- 11.2.0.2
We have a partitioned table in the production database. The following command retrieves 3 rows (which indicates there are 3 partitions for this table)
select * from dba_tab_partitions where table_name='<table_name>' and table_owner='<owner>'
but when i try to generate the DDL for the table it does not have any partition information in it
select dbms_metadata.get_ddl('TABLE','<table_name>','<owner>') from dual;
The output is similar to
create table ....
storage clause...
tablespace name;
(There are no partition information in this)
I tried to create a backup table as select * from the existing table but still no partition information seems to get transferred. The new table has NO partitions.
View 7 Replies
View Related
Jul 30, 2009
i have 2 identical tables....the trick is with regards to one column say column A,the first table TableA is constantly having data inserted and data updated, what im trying to do is create a before insert or update trigger that looks at column A and if column A=20 it changes this to 5 and inserts this into the tableB, everything else with the exception of Column A will be the same:-
this trigger i have done works but doesnt change the value of column A (INPUT_NADI) as i dont know how to do this:-
CREATE OR REPLACE TRIGGER UPDATE_TRG_NP_NE1_T0
BEFORE INSERT ON TESTNSS.NP_NE1_T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO NP_NE1_T0
VALUES
[code]....
View 6 Replies
View Related
Oct 27, 2011
I have one table with two columns col1 and col2 Values in table are like this
Row1 -- (1,'A')
Row2 (1,'B')
Row3 (1,'C')
Row 4 (2,'D')
Row 4 (2,'E')
Row 4 (2,'F')
Now I have second table which has FOUR columns
COL1,COL2,COL3,col4
I want to insert value in second table based on value in first column of T1 Like for rows in T1 with col1 as value 1 , insert the value of second column in second table I want second table like
ROW 1 ('1','A','B','C')
ROW 2 ('2',D','E',F)
how it can be done I tried insert into command but not able to frame the command
View 15 Replies
View Related
Jun 21, 2010
I would like to know how to get an insert template for any given table.
For example
SQL> @insertTemplate
Enter Table Name: emp
INSERT INTO emp VALUES
(NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- EMP_NAME NUMBER(15,0)
,NOT NULL -- SALARY NUMBER(15,0)
,NULL -- HIRE_DATE DATE
) ;
For any given table the script should create an insert template like above?
View 13 Replies
View Related
