SQL & PL/SQL :: Unable To Insert Rows Into Table

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


ADVERTISEMENT

SQL & PL/SQL :: Unable To Insert Rows

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

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 View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

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

SQL & PL/SQL :: Unable To Retrieve Rows From A Table

Mar 7, 2012

I have created a table called ct_temp and it's structure is:

create table ct_temp(id char(2),srs number(3),amt number);

Result : Table Created

I now insert the following rows into the table:

id srs amt

1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10

Commit

Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)

criteria : I then want to retrieve the following:

for srs = 62, all values that is greater than 10,000

Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab

where decode(srs,62,ab.amt)>10000

Works like a charm and retrives the results.

Now comes the big issue

I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.

Typically I wrote the query as:

select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)

I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".

I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.

I have tried the following in the office:

using EXISTS operator = no luck
using INLINE VIEW = no luck

Is there any way around?

View 26 Replies View Related

SQL & PL/SQL :: To Insert The Rows Into A Table Target

Oct 3, 2011

I have a table named SOURCE with about 1.000.000 rows looking like this

date varchar2 varchar2 varchar2
DATE ID1 ID2 AMOUNT
---------- ----------- --------- --------
2010-07-03 1403 1403 1500
2010-07-13 2015438 etc 188608 6074
2010-08-28 1151927 410,4222 1750
2010-08-28 13622012 41026 178.99
2010-08-28 1600246 John 65

I want to insert the rows into a table TARGET

date integer integer number
DATE ID1 ID2 AMOUNT

There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.

View 3 Replies View Related

Precompilers, OCI & OCCI :: Unable To Insert XML Data Into Type Table?

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

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

Server Utilities :: Control File - Insert Rows Into Table By Defaulting Date?

Jan 15, 2013

My Table structure

column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)

By Data file

asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds

I still want to insert row 2 and row 4 into table by defaulting the date. how can I handle this in control file?

View 14 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

Forms :: Unable To Insert Records

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

SQL & PL/SQL :: How To Insert Parametric Rows

Oct 14, 2010

I have to insert many computed rows (not coming from a select...where...) into a target table.

I wrote a code like the following example; it works but it seems to me not efficient:

WHILE l_data < l_dt_end
LOOP
l_value := MY_FUNCTION( l_data );
l_color := MY_COLOR( l_data );
INSERT INTO MY_TARGET
( value, color )
VALUES( l_value, l_color );
l_data := l_data + 1;
END LOOP;
COMMIT;
EXCEPTION
...

ROLLBACK;Which is the best way to write this?

View 9 Replies View Related

PL/SQL :: Trigger For To Insert Other Rows

May 2, 2013

I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error

First insert the user from application

INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC,  CD_TIPO_ACAO,  CD_ACAO_IMEDIATA,  DS_ACAO,  CD_RESPONSAVEL,  DT_ORIGINAL,  DT_ABERTURA,  NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT

[Code]....

END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08

View 2 Replies View Related

Forms :: Unable To Insert Mathematical Symbols?

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

Forms :: Two Blocks - Unable To INSERT Record

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

Forms :: Unable To Insert Checkbox Checked Records?

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

SQL & PL/SQL :: Unable To Insert Hindi Language Characters In Oracle 9i

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

SQL & PL/SQL :: Unable To Insert Georgain Characters Using 6i Forms And Reports?

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

SQL & PL/SQL :: Unable To Get Stable Set Of Rows From Source Tables

Apr 9, 2012

Find the code below,

DECLARE
L_DE_TGUP_ID BBP_ALLOC.DATA_ELEMENT.DATA_ELEMENT_ID%TYPE;
L_ZERO_EXP EXCEPTION;
L_DATA NUMBER;
BEGIN
SELECT DATA_ELEMENT_ID INTO L_DE_TGUP_ID FROM BBP_ALLOC.DATA_ELEMENT WHERE DATA_ELEMENT_CD = 'TGUP';
SELECT DECODE(UPPER('0.0028'),'',0,'0',0,'NULL',0) INTO L_DATA FROM DUAL;
[code]......

Here the relationship between the source and target tables is one to one. but still i am getting "unable to get the stable set of rows from the source tables" error. Source query retrieves only 2 rows which are distinct. but still getting this error.

View 14 Replies View Related

Insert Rows Based On Text Values?

Mar 22, 2007

I am an Oracle beginner and I am having some trouble with the following insert query.

I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.

INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);

The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"

View 1 Replies View Related

Insert Separated Data As Individual Rows

Sep 3, 2008

I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.

I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?

View 3 Replies View Related

SQL & PL/SQL :: Insert All Rows Values Fetched By Cursor?

Aug 12, 2010

I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.

what parameter or code I should used ?

I have marked with color row as below.

DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Insert Into Select Or Cursor For 100k Rows

Jan 22, 2012

I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.

CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....

View 5 Replies View Related

Forms :: FRM-40508 / ORACLE Error / Unable To INSERT Record

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

SQL & PL/SQL :: MERGE INTO - Unable To Get Stable Set Of Rows In Source Tables

Aug 30, 2010

When i am running the below merge statement it gives the error as below

MERGE INTO non_tab tt
USING (SELECT non_tab.acct,
non_tab.mis,
non_tab.n_gaap_skey,
non_tab.run,
non_tab.mit_ul AS t0,
CASE
WHEN (( CASE
[code]...

ORA-30926: unable to get a stable set of rows in the source tables.

View 5 Replies View Related

Server Utilities :: Insert Rows In Two Tables Using Sql Loader?

Aug 7, 2012

I am trying to insert rows in two tables using sql loader.

I have two tables in database as

SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

I created controlfiles as

[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'

[code]....

when i run sql loader as

[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 2.

no rows is inserted..the log file contain entries as

[oracle@DBTEST sqldri]$ cat datafile.log
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad

[code]....

View 7 Replies View Related

Server Administration :: Unable To See / Insert Chinese Characters In Oracle Database

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

SQL & PL/SQL :: Merge Statement - Unable To Get Stable Set Of Rows In Source Tables

Sep 15, 2013

When I am executing merge statement I am getting the below error.

ORA-30926: unable to get a stable set of rows in the source tables

MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,

[Code]....

The query is fectching below data.

SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131

[Code]...

Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.

When I run the same merge statemment second time I am getting the below error.

ORA-30926: unable to get a stable set of rows in the source tables

So that I have used partition by cluase in the slect statement and I am able to resolve the error.

But it's inserting only 14 records not all 15. How to process all 15 records without the error..

View 16 Replies View Related

Number Of Rows Inserted Is Different In Bulk Insert Using Select Statement

Jul 1, 2010

I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.

strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.

EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.

On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.

View 3 Replies View Related

Client Tools :: Unable To Insert Euro Symbol In Database With Character Set WE8MSWIN1252?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved