SQL & PL/SQL :: Avoiding Insert Into Table
Nov 21, 2010
I've got situation where one of the procedure causing insert into table rows but some of them are not needed Temporary I need to disable this and exclude some type of data being inserted by changing procedure.
I wouldn't want to change the procedure is there a way to write a trigger that would not allow to insert a row into a table if condition is met? Something of a logic:
CREATE OR REPLACE TRIGGER avoid_insert
BEFORE (AFTER?) INSERT
if entity_id = 123 DO NOT DO THE INSERT (DO NOT DO ANYTHING)
View 6 Replies
ADVERTISEMENT
Jan 5, 2012
I have table like below.
Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male
but I need the output like below
Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4
I have tried to get the above O/p but getting along with null values.
SQL> ed
Wrote file afiedt.buf
1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /
MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4
7 rows selected.
how the get the above o/p with out null values.
View 9 Replies
View Related
Oct 16, 2010
I have two tables A and B
CREATE TABLE A(EMP_ID NUMBER, EMP_NAME VARCHAR2(100))
CREATE TABLE B(EMP_ID NUMBER, EMP_ATT1 VARCHAR2(10), EMP_ATT2 VARCHAR2(10))
INSERT INTO A VALUES(1, 'ONE');
INSERT INTO A VALUES(2, 'TWO');
INSERT INTO A VALUES(3, 'THREE');
[Code]....
This query returns all the matching row of A and B
SELECT A.EMP_ID, A.EMP_NAME, B.EMP_ATT1, B.EMP_ATT2
FROM A
INNER JOIN B ON A.EMP_ID=B.EMP_ID
The output for this shows:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
2 TWO 2ATT1.1 2ATT2.1
3 THREE 3ATT1 3ATT2
The requirement is to avoid duplicate rows even if matched:
EMP_ID EMP_NAME EMP_ATT1 EMP_ATT2
1 ONE 1ATT1 1ATT2
2 TWO 2ATT1 2ATT2
3 THREE 3ATT1 3ATT2
View 8 Replies
View Related
Jan 31, 2013
I have these records in a table:
CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips
My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:
CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips
That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.
View 4 Replies
View Related
Dec 7, 2010
Given two tables in a 1:N relationship, I want to retrieve, for each parent row, any child row which either satisfies some condition cond1 or, if no child rows matching cond1 exist, which satisfies another condition cond2.
I can easily write such a query using NOT EXISTS, but I am forced to repeat the condition cond1. This is inconvenient, as the repeated condition is quite long (involving other tables as well), and I would like to write it only once.
For example (using the HR schema included in Oracle 10g XE): I want to retrieve, for each department, all the "new" employees (hired after a certain date) or, if no "new" employees exist, the department manager.
SELECT d.department_id,
d.department_name,
d.manager_id,
e.employee_id,
e.first_name,
e.last_name,
[code].....
View 3 Replies
View Related
Jun 15, 2010
We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?
As example lets say the query is:
SQL> variable vn varchar2(20);
SQL> EXEC :vn :='ADAMS';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
1 row selected.
SQL> EXEC :vn :='KING';
PL/SQL procedure successfully completed.
SQL> select * from emp where ename=:vn;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
1 row selected.
But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?
View 9 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
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
Oct 26, 2010
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I am doing a simple INSERT INTO from the sql developer window. it shows that 1 row inserted, but when i query, it doesnt give th values.
insert into events_main(event_id,event_number,last_update_Date, last_updated_by)
values(162,999999, sysdate, 'rkhatiwala');
COMMIT ;
this does not give any error, but when i do
select * from events_main
where event_id = 162
it does not return anything.
View 20 Replies
View Related
Jun 5, 2005
I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.
1)INSERT #konfig
exec sp_configure.
2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go
how migrate for example code to Oracle?
View 11 Replies
View Related
Apr 9, 2012
Is it possible to insert new id without sequence id.
For e.g. :
1 , 'BOLT'
2 , 'CHAIN'
i need to insert 3rd and 4th row as
3 , 'screw'
4 , 'driver'
without using sequence (autonumber gen).
View 11 Replies
View Related
Oct 13, 2010
I basically get it how to insert selected records from html to table by converting into the xml format [URL] (Barbara and Michel know what Im talking about).
But now I cant get the hang of convert one html site where are a very usefull information for my job.
Its about the site[URL]
where I need to get informations about phone specs and insert into the table.
Informations begin with" rozmery telefónu106 x 48 x 16 mm"
and ends "podpora WCSS".
However I try to figure it out always I gonna get some ORA errors.
View 10 Replies
View Related
Jul 18, 2012
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?
View 2 Replies
View Related
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
Mar 14, 2013
I have a table like this:
CREATE TABLE CARGA_P_RECARGA_NEW1
(
TELE_NUM VARCHAR2(10) NOT NULL,
FECHA DATE NOT NULL,
[Code].....
Then I tried to insert some rows in that table, every insert statement is like this:
INSERT INTO CARGA_P_RECARGA_NEW1
VALUES
('3134769595','20/01/2013 07:22:50','1107','CONFB_20121121_20121122175002 60000000000000000090.TXT',0,16,'8327--7991284',1);
Every insert I executed had the month 01 because I expected to query results only from partittion p_0113 but nevermind how query I execute, the result is always the same. I mean if I excute this statement:
SELECT * FROM CARGA_P_RECARGA_NEW1 P_0113;
I get the same result when I execute any other like this:
SELECT * FROM CARGA_P_RECARGA_NEW1 P_0213
What is the error here ?
View 4 Replies
View Related
Jul 19, 2012
I have a table with an attribute set to auto increment
how can I insert into the table without specifying the value of that attribute?
View 16 Replies
View Related
Jul 9, 2013
Can we use result of with query_name ... to insert dataset into some table? I'm usung 11.2.0.3
View 5 Replies
View Related
