SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies


ADVERTISEMENT

Forms :: How To Stop Inserting Data If Commit Command Applied

Feb 17, 2010

How Can We Stop to insert Data if Master Block have the record and detail block have no record but commit command applied i want that commit command can not be work if detail block have no record

View 7 Replies View Related

Forms :: How To Stop From Inserting If Condition Not Met

Feb 7, 2010

I have master detail datablocks. Master block is course and detail block is trainee. students can schedule their courses in a location at particular time.

lets assume student1 has scheduled course A1 in location LA1 from 9am to 12 pm. Now second student shouldn't be able to schedule same course at same time in same location. He can schedule at different time duration. or else same time duration but different location. Or same time, same location but different course.

so, what i did is, i wrote code in when-validate-record of trainee block to display a message saying " cannot schedule course.choose another time." if he try to insert same time duration or same location .

when i am trying to insert a record with time which is already scheduled and in same location, it displays the message but it is also saving the record. i don't want to save the record.

how should i do this? i tried to give ROLLBACK in this trigger but it doesn't accept. i tried pre-insert trigger also but it is also displaying message but saving the record

my code in when-validate-record is:

declare
cursor c1 is
select start_time,end_time,loc
from trainee
where cname=:course.cname;
[code].......

View 5 Replies View Related

SQL & PL/SQL :: Inserting Data From One Table To Another?

Oct 23, 2010

I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0

TEST22:
-----------
|sno |sname |
-----------
| | |
-----------

TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------

Required outcome is

TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------

Also this should be applicable for more than one value in the column col2 of table TEST22P.

ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------

I used decode & pivot insert for this,but the result is a failure.

SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;

SQL>
sno sname
--------
1 null
null arun

AND

SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier

View 4 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 Replies View Related

PL/SQL :: Inserting Data Into Updated Table

Jul 22, 2012

I have a table created with the following code:

CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),

[Code]...

I have altered the table to have the following:

ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);

I am trying to insert new data into said table that was updated:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;

Then I get the following error:

Error starting at line 1 in command:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:   
*Action:

View 3 Replies View Related

PL/SQL :: Extracting Data From XML And Inserting Into Table

Aug 8, 2013

I have an XML  of the following format:

<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students> 

Now i have to insert this xml into DB , the table consist of following columns  ( row number , property name , value )  Expected out put is   (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is

1. how to get the tag names  and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?

View 8 Replies View Related

PL/SQL :: Inserting Data Into Nested Table

Jul 22, 2013

I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.

CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);

---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........

View 4 Replies View Related

Performance Very Slow Inserting Data Into Table

Jun 25, 2013

I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query

INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;

Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.

View 4 Replies View Related

Forms :: Inserting Into A Table Using Data From A Text Box

Feb 16, 2010

I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.

I tried:

begin
insert into StaffName values ('addstaffmember');
end;

Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.

View 5 Replies View Related

SQL & PL/SQL :: FORALL For Inserting Data (with Some Constant Value) Into Table

May 13, 2011

using FORALL for inserting data into table.

Below pl/sql works fine when we write all the cursor data together:

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

but I want to insert including some constant value while inserting the data like

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

How to do this by using forall, or how to do without hiting performance ( more than 5000 rows i have to write.

View 5 Replies View Related

Forms :: Reading Excel Data And Inserting Into Table Using 6i?

Feb 16, 2004

how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.

View 3 Replies View Related

SQL & PL/SQL :: Trigger Not Inserting The Records?

Jun 21, 2012

I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.

create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE

[Code]....

if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex

View 2 Replies View Related

RAC & Failsafe :: Inserting Large Data Locks The Destination Table In RAC

Oct 18, 2010

Scenario:

Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.

What we are performing is:

Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;

Issues:

my_table_rpt get locked

We have found the workaround by disable locking in destination and subsequent to the insert enable locking

ALTER TABLE my_table_rpt DISABLE TABLE LOCK;

Insert the data to the reports database table

Then

ALTER TABLE my_table_rpt ENABLE TABLE LOCK

Question:

Why does the large destination table (my_table_rpt) get locked in the RAC environment?

View 2 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies View Related

SQL & PL/SQL :: Inserting Values For All Column Names In Trigger

Aug 10, 2010

I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.

Code in SQL SERVER :-

CREATE TRIGGER INSERT_AUDIT ON T_AUDIT
FOR INSERT AS
INSERT INTO T_AUDIT_LOG
SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,*
FROM INSERTED

Converted into Oracle:-

CREATE OR REPLACE TRIGGER INSERT_AUDIT
BEFORE INSERT
ON T_AUDIT
FOR EACH ROW
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );

END;

how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.

the last 3 columns when are c_data1,c_data2,c_data3 as :-
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3);
But this gives error:-
PLS-00049: bad bind variable 'NEW.C_DATA1'
PLS-00049: bad bind variable 'NEW.C_DATA2'
PLS-00049: bad bind variable 'NEW.C_DATA3'

how can I convert this code.

View 7 Replies View Related

Forms :: Inserting Values Using PreInsert Trigger?

Sep 30, 2010

I am facing a problem while inserting primary keys using a sequence. Following is my case

I have a Master Table, say M_A, and 2 detail tables D_1 and D_2. I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.

I created a pre-insert trigger, say preInsertTRIG

proc_ABC(pri_key_master OUT VARCHAR2,
pri_key_detail1 OUT VARCHAR2,
fk_detail1 OUT VARCHAR2,
pri_key_detail2 OUT VARCHAR2,
[Code] ........

I am able to insert the P.K of the master table as well as P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables. I know there are other simple methods available in Forms, but I have to do it by this procedure only

View 5 Replies View Related

SQL & PL/SQL :: Lock Inserting Transaction - Error During Execution Of Trigger

Apr 30, 2013

i create this trigger to lock inserting transaction for any data greater than 30-04-2013 on a certain table

CREATE OR REPLACE TRIGGER INVALID_DATE_VALUE BEFORE INSERT or UPDATE on ra_cust
FOR EACH ROW
BEGIN
IF :NEW.TRX_DATE >'30-APR-2013'
THEN
-- RAISE_APPLICATION_ERROR('ora-0000','DATE CANNOT FALL IN RANGE...Invalid Date');
RAISE_APPLICATION_ERROR(-20000, 'IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN ''30-04-2013''');
END IF;
END;

and this trigger created successfully

when i try to insert data in the table ra_cust even in a range less than 30-apr-2013

i got this error

ORA-20000: IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN '30-04-2013'
ORA-06512: at "ARASK_HAGAR.INVALID_DATE_VALUE", line 5
ORA-04088: error during execution of trigger 'ARASK_HAGAR.INVALID_DATE_VALUE'

View 2 Replies View Related

Inserting Data Into Two Tables

Feb 15, 2009

I have two tables

table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2

table2

col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key

now my question is how to insert data in to the two tables

View 2 Replies View Related

Inserting Blank Data

Jul 17, 2009

I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.

The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.

What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.

View 6 Replies View Related

SQL & PL/SQL :: Inserting Data Using DBLINK

Feb 21, 2013

I am inserting XMLTYPE data using DBLINK I am getting the following error.

INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY

ORA-22804: remote operations not permitted on object tables or user-defined type columns

Source table structure

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

Target table structure(another DB)

Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE

how to insert XMLTYPE data using DBLINK.

View 16 Replies View Related

SQL & PL/SQL :: For Loop - Inserting Data?

Mar 7, 2012

create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);

I want to insert data from stg1, stg2 into stg

Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2

I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;

it gives me table does not exist error:

so by stgi, i mean it should take stg1, stg2 etc

View 4 Replies View Related

Inserting Data To Tables On Another User?

Mar 13, 2007

how can I insert data into tables on another user. They both are in the same table-space.

View 1 Replies View Related

Inserting Restriction By Existing Data

Nov 8, 2010

In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.

that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.

View 2 Replies View Related

Performance In Inserting Data From Staging

Feb 13, 2013

I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)

I am using below command:

INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO
SELECT DATA from Staging

CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.

View 2 Replies View Related

SQL & PL/SQL :: Inserting Data In Repeating Tables

Apr 19, 2011

How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.

There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.

If student has 3 addresses then Its seq no is 3.

I am inserting through a procedure where multiple students data is to be inserted.

how to take care of this sequence no.

View 15 Replies View Related

SQL & PL/SQL :: Inserting Data From Clob To Varchar2?

Aug 21, 2006

I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.

Below are the structures of both tables;

SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)

[code]....

The Table contains 5000 records. how to convert CLOB to VARCHAR2.

I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.

View 10 Replies View Related

SQL & PL/SQL :: Inserting Excel Data Into Oracle?

Sep 24, 2011

I want to insert excel data into Oracle.

Excel File Name : Product 01
Excel columns
File Send on 13/02/2011

Arrival Date Product Code Gate Pass Quantity Inpection
01/02/2011 00002 Y 2 Y
03/02/2011 00001 Y 10 Y
04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y

File Send on 14/02/2011

Arrival Date Product Code Gate Pass Quantity Inpection
01/02/2011 00002 Y 2 Y
03/02/2011 00001 Y 10 Y
04/02/2011 00005 Y 14 Y 03/02/2011 00006 Y 74 Y
---New Updated Data
05/02/2011 00002 Y 2 Y
06/02/2011 00001 Y 10 Y
05/02/2011 00005 Y 14 Y 05/02/2011 00006 Y 74 Y

I just want to insert data according to my structure But if again the same file send with updated data it will only update the new data because previous data is imported.

Oracle Structure
Arrival Date Date,
Product Code char(5),
Quantity Number

View 9 Replies View Related

Performance Tuning :: Inserting Data Using FOR ALL

Mar 6, 2011

CREATE OR REPLACE procedure fast_proc (p_rows out number)
is
TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE INDEX BY BINARY_INTEGER
lt_object_id object_id_tab;
CURSOR c IS

[Code]....

Warning: Procedure created with compilation errors.

Errors for PROCEDURE FAST_PROC:

LINE/COL ERROR
-------- ---------------------------------------------------------
13/7 PL/SQL: SQL Statement ignored
13/22 PL/SQL: ORA-03001: unimplemented feature

I am not able to do INSERT but I am able to do UPDATE/DELETE? What is this inbuilt functionality?

View 3 Replies View Related







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