It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS
2 (
3 SELECT EMPNO, ROWNUM RN
4 FROM SCOTT.EMP T ORDER BY ROWNUM
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
I'm trying to Create a procedure that generates: Customer Description of Order Amount of Order Total Of Order
As this Generates More than One Row I thought i would need to use cursor in order to store the results and then use them from the object So this is what i Coded:
CREATE OR REPLACE PROCEDURE UP_CustOrders AS /*I Am Selecting A Cursor Cause It Return More Than One Row*/ CURSOR ReportCursor IS SELECT Company
[code]..
It appears It creates the procedure but im not sure why it has compilation errors Perhaps im missing some symbols? Here are the tables:
CREATE TABLE Customers (CustNum NUMBER(10) NOT NULL, Company VARCHAR2(20) NOT NULL, CustRep NUMBER(10),
There are two servers A and B ,and i am maintaining one table suppose table1 and another table suppose table2 , DML triggers are made for both of these tables.The action of DML trigger is that the movement we are inserting values , suppose a table1 on server A so automatically the same set of value in table2 on server Bso my problem is that the movement i inserted value on table1 on server A ,
the same set of values were inserted into table2 of server B and as the values were inserted intotable2 , the same set of values were inserted into the tabld1 because of the trigger. How to stop the non ending loop which occurs due to the simultaneous firing of triggers and insertion of same records into both the table endlessly...
version :---Personal Oracle Database 10g Release 10.2.0.3.0 TNS for 32-bit Windows: Version 10.2.0.3.0
$ srvctl add service -d dc1ext -s addval.dc1 -r "dc1ext1" -a "dc1ext2" -P BASIC $ srvctl start service -d dc1ext -s addval.dc1
begin DBMS_SERVICE.MODIFY_SERVICE( service_name=>'addval.dc1', aq_ha_notifications => true, failover_method=>DBMS_SERVICE.FAILOVER_METHOD_BASIC, [code]......
my attempt at verifying the TAF is working.
SQL> run 1 SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*) 2 FROM GV$SESSION 3* GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER
MACHINE FAILOVER_TYPE FAILOVER_M FAI COUNT(*) ---------------------------------------------------------------- ------------- ---------- --- ---------- anetapp02.dc2 NONE NONE NO 10 anetapp01.dc2 NONE NONE NO 10 app02.st2 NONE NONE NO 5 racdb02 NONE NONE NO 44 anetapp03 NONE NONE NO 10 anetapp04 NONE NONE NO 10 GNOME\APTSEA-6GKJHF1 SELECT BASIC NO 3 app01.st2 NONE NONE NO 5 anetapp05 NONE NONE NO 10 anetapp01 NONE NONE NO 10 anetapp03 NONE NONE NO 10
so it looks as thought the sessions are failing over, but my app guy says he get a few errors during the process.. i need it setup to move the current query over without returning an error.. do i not have this setup properly?
I have created a materialized view in schema SchemaBB of Server B with a trigger
CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70 BUILD IMMEDIATE REFRESH FAST ON DEMAND WITH PRIMARY KEY AS SELECT TEST_PK, TEST_TEXTO [code].........
All schemas have the appropriate grants.
When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.
But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).
i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..
i have written the following code in when_tree_node_selected trigger :
if condition then call_form(path,no_hide,do_replace);
I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,
I applied the Jan 09 CPU to my database this past Saturday and now my daily schema export is not running properly:
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in WE8MSWIN1252 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion)
About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user TEST . exporting PUBLIC type synonyms . exporting private type synonyms
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
I created one Form manually. how to Drag and drop from items in Data Blocks to Canvas. when I created text in Canvas it was automatically creating the item in Data block. but when i create the items in Data blocks and then trying to copy to canvas.
DECLARE TYPE SUBNO_TABLE_TYPE IS TABLE OF TRANS_DEICMAIN1.SUBNO%TYPE INDEX BY BINARY_INTEGER; SUBNO_TABLE SUBNO_TABLE_TYPE; K NUMBER := 1; S NUMBER := 1; [code]...
HERE CHECK1 IS A CHECKBOX.WHEN I AM USING THIS CODE ONLY ONE RECORD IS SAVED AT A TIME INSTEAD OF SAVING ALL RECORDS.
I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).
create table dest_test ( destination varchar2(4) not null, db_name varchar2(10) not null )
desc dest
[code]...
I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?
I have a form in which menu tree is populated with this sql
SELECT 1, Level, MenuLabel, MenuName, MenuId FROM MenuFile CONNECT BY PRIOR MenuId = ParentId START WITH menuId = :moduleid||'0000'
The result is attached in the CSV file enclosed in sheet mrnufile1As we can see in row 3 the item LPC has menuid BDME13. In my view This item should be in ROW 19.How can i change query do get the desired the result.I have also tried another sql query.
select * from menufile where applicationid = 'BD' order by parentid,menuid
I am using Apex 4.2, The Interactive report Search is not working properly, when i am entering complete date or time it is not showing any results...how to increase performance of the search...
SELECT sno,mid,mname FROM manage_date WHERE mname IN ('KIRAN-KUMAR', 'RAHUL-RAJ', 'KAUSHAL-SONI');
IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)
If I pass one name 'KIRAN-KUMAR' from .net It's working.
If I pass multiple names from .net query not returning any records.
Using Connect By on a query which has a nested from clause(The from clause fetches around 100k records) gives incorrect results but if the same nested queries are used to build a table and the Connect By is used on the table then the output is correct.
I put the nested queries in a 'WITH' clause and got the correct output also.
I am not sure how to give the code here as you would need dump to make them work. I am giving the a sample
--Non Working Code
SELECT con_item, prod_item, compsite, bcsite, ibrsite, res FROM (SELECT con_item, prod_item, compsite, bcsite, ibrsite, res FROM (SELECT bd.item AS con_item, bd.fromid AS compsite, bd.toid AS bcsite, bd.toid AS ibrsite,
Department EmpName ____________________________________ Sales empname1 Sales empname2 Marketing empname3 Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total ______________________ Sales 2 Marketing 1 Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM ( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee FROM Employer A, EmployeeInfo B WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2') GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on to_char(partner_user.partner_userid,'9999')=membership.created_by select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper.Well now i have 3 tables...
ps_operations
Name Null? Type ----------------------------------------- -------- -------- ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8) OPERATION_NBR NOT NULL VARCHAR2(10) EFFECTIVE_FROM_DT NOT NULL DATE DML_TS NOT NULL DATE DML_USER_ID NOT NULL VARCHAR2(30) OPERATION_DESC NOT NULL VARCHAR2(70) HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6) PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7) EFFECTIVE_TO_DT DATE EXTRACT_IND VARCHAR2(1) [code]...
I have never worked on CPK and UK....so i dont know how to use them to join the tables,.
am learning the Oracle PL/SQL language using CBT Nuggets tutorials and practicing what I learn. I seem to be getting confused with the ROUND function...For example, what am I doing wrong in this statement:
select avg(salary) from employees, ROUND (avg(salary) , 2;
Basically I am trying to round the result to two decimal places but get the error ORA - 00933 - SQL command not properly ended?
I enter a form, do nothing, and exit_form. It closes but reenter again. The second try, does not cause this effect and ends properly. Checked and commented code around the form, but no use.What could be happening ?
In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..
some info:- We rae using ODI 10g. Weblogic 10.3.5.0
How do you setup a default style for displaying the output columns in sqlplus? The columns look clumsy when queried. They display correctly in Toad but, I wanted to learn through sqlplus and I am relatively new to Oracle world. Here is the DESC of table
SQL> DESC EMP.GROUP_TYPE Name Null? Type ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- -------- -------------------------------------------------------- ------------------------------------------------------------ GROUP_TYPE
The other day, we had a query run amok in our 2-node production cluster. The 3 temp files for the temp tablespace were all still set to autoextend unlimited, something I forgot to change after a recent upgrade. I created 3 new temp files and tried to delete the huge temp files. I did this from sqlplus with this commad:
ALTER TABLESPACE PSTEMP DROP TEMPFILE '+DATA/isis/tempfile/pstemp.291.641298061';
The huge files are still in ASM storage. dba_temp_files reports that the status of them is AVAILABLE but they have no RELATIVE_FNO. Grid Control reports their status as OFFLINE and their size as 0. They are actually close to 20 GB each.
I tested the above alter statement in two test instances, also RACed with ASM storage and the temp files were successfully deleted, but they were much smaller in size. At this point, how do I delete the three 20GB files from ASM in our production instance? Why didn't they delete the first time?
