PL/SQL :: Avoid Record Creation With Similar Names
Oct 17, 2013
am trying to add a validation in Oracle forms using Oracle DB.in a Oracle table consider there is a record named 'Netbook' then if I try to create another record with name 'Netbooks', then a Oracle warning message should be displayed stating similar name 'Netbook' is already available. same way, if NETBOOKS is already available in table and if user try to create another record NETBOOK then same warning message netbooks is already available should be shown.
I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
I created a report ...whcih i complied and run on paper layout form.. it shown me on correct dta..but i change change layout size and postion on that viewer..
is it avoidable....if yes, then how ?? I wantto lock these object while previewing...
TAB1 with data no name 1 abc 2 abc 3 xyz 4 xyz 5 cvb
now I would like to create a form with name counts as
abc 2 xyz 2 cvb 1
I would like to create a datablock to display all the unique names, not sure where I should be writing the query to display unique names. I would like to create a textitem box to display the counts when the form is compiled, but not sure which trigger to use to write the query.
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again
The Grid infrastructure was deinstall one time and still the same issue.
I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.
Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor: SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID) FROM GAR_DW.ARTICULOS o10475528, GAR_DW.EMPRESAS o10475602, GAR_DW.L_DIA o10475639,
- Imagine I have an table with several ZIPCODE. - Imagine I want to return from that table the existence of several zipcodes I need to get. - Imagine that I need to return both zipcodes that exists and both zipcodes that not exist.
The solution I've found to do this, was using the Pivot method, like the sample below, if there is another way to return anything like that. Return the zipcodes that exist, and the zipcodes that does not exist also!
Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1100,'Portugal',2); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1150,'Portugal',2); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1000,'Portugal',1); Insert into GSCIS.AAA_ADDRESSZIPCODES (ZIPCODE,NAME,COD_PLACE) values (1200,'Spain',2);
Select using Pivot:
SELECT * FROM (SELECT distinct zipcode FROM aaa_addresszipcodes group by zipcode ) PIVOT ( count(zipcode) FOR zipcode IN (1000,1100,1200, 1150, 2000) )
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as ( select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all [code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all [code]...........
We have Oracle 8i, 9i, 10g and 11g. I installed oracle client 11gR2 and when I tried connecting to the database, its erroring out saying "ORA-03134 connections to this server version are no longer supported". What client version should I install in order to avoid this error?
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
I have a query that performs the sum of a field and divides by the number of occurrences, got me a problem because if there are no occurrences would cause a divide by zero error in Query.
The solution I found might not have been the best, but at the time was possible.
I put a Case, when the sum of the number of occurrence is zero then return zero, otherwise performs the division of the sum of the values by the number of occurrences.
select T2.name,sum(T1.area) from (select * from country) T1,T2i wrote very simple just to show you the problem, bu i think here we should add "group by". but is there any way to avoid group by?because i don't want to group any thing!
I have created a procedure which sends mail to the users by using the smtp operation. I am collecting the email id of the users and then concatenating the same using the loop.
Everything is working fine, but if some person is coming multiple times in the loop, then his/her name is shown multiple times in the CC/BCC part (Although mail is sent once), it should be restricted to one time.
what i am doing is:
I have declared id_collector and it is concetenating the email as per the condition loop
........ some code here.... id_collector:=id_collector||email_id||';'; end loop;
Now, I want to check before concatenating that email already exists or not.
Example:
suppose value of id_collector= 'abc@gmail.com;pqr@gmail.com;rst@gmail.com;xyz@gmail.com;' and again if email id comes - 'abc@gmail.com', then it should get rejected.
I think it can be done using reg_exp but I have messed up with that.
i am facing problem with date column.when i am trying to do execute a query this works fine
select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')
and when i am trying the below query its showing the only this year dates its not referring the previous years
select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')
when i am trying below query its showng the error ORA-01843: not a valid month
select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)
I'm creating oracle form to allow the user to register a new record in a table, I want to check the duplication in Four tables! which means, the user is able to save the record if it's NOT already registered in the other four tables.By checking P_ID (user parameter). this is my
Declare v_count number; begin select count(*) into v_count ---Checking the 1st table from 1_table where p1_id=:p_id; if (v_count>0) then message ('Duplicate'); [code]....
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from ( SELECT M.FIELD1, TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE, M.FIELDNAME AS FNAME, M.USERNAME,
While I am inserting and updating the values in the oracle database its showing a message 'FRM-40401 - No changes to save.' I don't want this message to be shown. How can I do that?
i have created form. one field is there "Payment_terms" at line level. for that i created LOV containing 3 fixed Payment terms. my requirement is that Avoid the user to select twice the same value from LOV.
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, Details are as follows.
explain plan for SELECT cbu_cid, cbu_cid_customer_en_nm, COUNT (billg_acct_no) AS billg_acct_no, SUM (subscriber_cnt) AS subscriber_cnt FROM daily_view WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM') AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469')) GROUP BY cbu_cid, cbu_cid_customer_en_nm HAVING SUM (subscriber_cnt) > 10 ORDER BY subscriber_cnt DESC; [code]....
I have tried with ALL_ROWS & PARALLEL.how to avoid above two indexes in a query.
