FAQ Database Discussion Community


In Oracle 11g how do you time weight average data hourly between two dates?

sql,oracle,oracle11g,sqlplus
I have been pulling my hair out over this query. How does someone time weight average (TWA) data by seconds, minute, hour and or by days between two dates in Oracle 11g? The query below will produce a sequence of events output (as the change of state occurs). My table...

How to get records missed after join on two tables

sql,oracle,oracle11g
TABLE AA ID NAME EMAIL TRAID STUSYID ----------------------------------------- 1 Ketan [email protected] 1 2 Pankaj [email protected] PL1 5 3 Adil [email protected] AS1 1 4 Lalit [email protected] 7 5 Adil [email protected] AS1 2 6 Adil [email protected] AS1 3 TABLE AB ID NAME EMAIL TRAID STUSYID ----------------------------------------- 1 Pankaj [email protected] PL1 5 2...

Oracle database table row count

database,oracle11g
I want to take a table row count of a particular table precisely in my application. Rather than running a SQL count query, Is there way to fetch a table row count from oracle database engine?

Oracle merge from another table gives me error ORA-00904 invalid identifier

sql,oracle11g
I have a table T1 containing data I have to either insert or update in another table T2. So I came up with the following merge: merge into Table1 T1 using (select a, max(b) b from Table2 group by a order by a) T2 on (T1.a=T2.a) when matched then update...

Merge table in Oracle with delete condition refering to source table

sql,database,oracle11g,merge
For the following question it is said that the answer should be C. But I think the correct answer is Answer D as NOT MATCHED block inserts all unmatching records to target table. Can anybody explain this? Thank you. Q)View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS...

PL SQL Retrieve Table data to XML TYPE

sql,oracle11g,oracle-apex,appendchild,xmltype
I wanted to know why my data is not filter in 3rd level by 2nd level primary key. In first level i'm passing Item Id as a PREFNO then after i'm retrieving data according to ITEM_ID. 2nd level finding primary key of LEVEL_ID using ITEM_ID and retrieving Dimensions according to...

Oracle SOA suite domain error

jdbc,oracle11g,database-schema,oracle-fusion-middleware,soa-suite
I'm trying to create SOA Suite 11g domain via Fusion Middleware Configuration wizard and I'm stuck at the step where I need to configure JDBC component schema. The walkthrough I'm reading specifically says that one should not modify username to the schemas because it will be automatically assigned. But, whenever...

Oracle 11G repeats output_put.put_line twice. How to solve this?

string,oracle11g,output,dbms-output
My loop is given me twice the results. As I only want it one time. What should I do? for i in 1..length(newStudentNumber) loop character := SUBSTR(newStudentNumber, i, 1); newStudentNumber := newStudentNumber || case character when 'A' then '16' when 'B' then '17' when 'C' then '18' when 'D' then...

Strange behaviour querying former large table with just one row now [duplicate]

sql,oracle,oracle11g
This question already has an answer here: Slow query execution in an empty table. (after deleting a large amount of inserts) 3 answers I have a quite simple table in a testing environment. Yesterday it had approx. a million entries. I deleted them ( delete from mytable; commit;). Actually...

delete an entry from a table A if that entry is already present in a table B

sql,oracle,oracle11g,delete
My question is somewhat similar to this one SQL Delete Rows Based on Another Table except for the fact two entries match if 4 columns match! So, I have: Table1: Field | Type | Null | Key | Default | Extra f1 | int(32) unsigned | NO | PRI |...

Sum the differences between dates

sql,oracle,oracle11g,date-arithmetic
I have 2 tables, master and detail, that both contain dates related to events. The first contain the master record's begin and end date. The second contains various gaps, that also have a beginning and an end, related to the master record and falling between its begin and end date....

Oracle Data source configuration in MULE(AnyPoint Studio)

oracle,oracle11g,mule,datasource,mule-studio
I use this config for oracle database in Mule: <spring:beans> <spring:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="shutdown" name="Bean"> <spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/> <spring:property name="url" value="jdbc:oracle:thin:@192.168.28.129:1521:orcl"/> <spring:property name="user" value="username" /> <spring:property name="password" value="123456" /> </spring:bean>...

Check string for numbers and characters in Oracle 11G

regex,string,oracle,oracle11g,numbers
I am working on something. I got to check a string for validation. This string has a country code and a university code (from their own tables in the database) and a bunch of numbers. Now I need to validate this string. I need to check this string on the...

Euro '€' symbol not inserted correctly during Oracle SQL LOAD

oracle,oracle11g,sqlplus,sql-loader,dbms-output
I'm loading a csv file using sqlldr. the file contains the symbol "€" which is inserted into a VARCHAR2 column. After the load, the database displays '¿' instead of the euro symbol. I have specified the characterset in the control file during the load: LOAD DATA CHARACTERSET WE8MSWIN1252 I'm runing...

PL/ SQL Loop No data found

sql,plsql,oracle11g,oracle-apex
I tried to take values for VarNewIdOne as a bulk collection. So i got values to bulk collection after i tried to equel that values to another variable (VarOldLevlIdOne) then getting out put as a NO data found. SELECT ITEM_ID bulk collect INTO VarNewIdOne FROM BIZZXE_V2_SCH.ITEMS WHERE PARENT_ITEM_ID = VarId;...

How can I add a new host to an existent Oracle ACL?

oracle11g,acl
I cannot find what command to use to add a host to an existent ACL on Oralce. Let's assume my ACL is called users.xml, how can I add host "remote.host.net" ? Notice I'm on Oracle 11gR2

Is there a group limit for Regep_replace in Oracle? (with regexp_replace issue)

regex,oracle,oracle11g,substring,reverse
At the moment I got a long input string that I want to reverse with regexp_replace. Normally I replace a strings position by using this (from within the begin clause in PL/SQL) variable:= regexp_replace(variable, '(.+) (.+) (.+) (.+)', '\4 \3 \2 \1'); In this case my variabele will be replaced...

how to select a unique record in a table which has no key constraints [duplicate]

sql,oracle,oracle11g
This question already has an answer here: Fetch the row which has the Max value for a column 29 answers I had a table named uniq_select_records with the following columns: sno number, item_name varchar2(50), start_date date, end_date date, action_flag char(1), last_modified_date date, creation_date date sno is a column which...

How to determine number of records in a subquery

sql,oracle,oracle11g
I'm fairly new to Oracle SQL and would like to know if anyone has any examples of how to do the following: I'd like to return the number of rows that exist in the following SQL Statement listed below. This SQL will be part of a larger SQL statement and...

How to make available Oracle 11g database on one PC to another PC

java,jdbc,oracle11g
I have created a Java Eclipse app with database tables from Oracle 11g. It works fine but only in my local pc. How can I make these tables work into another pc? Maybe I have to put them in the application folder? The app is connected with the db via...

Complex SQL join: group by fails

sql,oracle11g
I have query with left join and groupings. Query combines 4 tables, and groups by nodes. EDIT As measure is different based on point, i joined the subquery as z.measure : and p.start > '1.1.1999' and p.end > '1.1.1999' However the left join fails resulting with patterns in data: A...

Oracle managed driver ODP.NET with NHibernate 4.0 FLOAT (126) to C# DECIMAL/Double

nhibernate,oracle11g,fluent-nhibernate,decimal,buffer-overflow
I am trying to create a mapping using Fluent NHibernate with Oracle 11g. This is the mapping I am creating:- public class Abc : ClassMap<Abc> { public Abc() { Table("Abc"); DynamicUpdate(); Id(x => x.Id, "IdColumn").GeneratedBy.GuidComb(); Map(x => x.DecimalColumn, "DecimalColumn").Formula("TRUNC(DecimalColumn, 28)"); } } Now when I get the data using criteria...

Oracle Stored Procedure update rows if records exist

stored-procedures,oracle11g
I have following Oracle stored procedure. Either subcode or stylecode is passed in. Currently it is update rows if value is not null. I want to add logic so that make update only if rows are exists on the table, if not, I like to print message like "The subcode...

Differences in these 2 query

sql,oracle11g,oracle-sqldeveloper
Is this 2 query different? Im getting different results in Oracle SQL, can you explain the difference? ***First Query*** SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.COL_ID = TABLE2.COL_ID AND TABLE1.COL1 = 'ABC' AND TABLE2.COL1 = 'ABC' AND TABLE1.COL2 = 2015 AND TABLE1.COL3 = 'X'; ***Second Query*** SELECT * FROM TABLE1,...

why is auto-increment trigger creation in oracle failing?

oracle11g,triggers,auto-increment
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production I am trying to create a trigger for auto-incrementation. This solution is discussed all over the web, and is pretty much conceded to be the standard auto-increment solution for Oracle. For example this Stack Overflow article seems to be...

ODI Sql Spool with GetOption of Query Separator

oracle11g,separator,data-integration,oracle-data-integrator
Hello . I need to run a select using a separator for the fields to make a sql spool WHEN use static character ; So I do: select < % = odiRef.getColList ( " " , " [ EXPRESSION ] ',' || ' , ' || " , " "...

Oracle Tablespaces maxsize “unlimited” not really unlimited

oracle,oracle11g,tablespace
I recently needed to import a .dmp into a new user I created. I also created a new tablespace for the the user with the following command: create tablespace my_tablespace datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M autoextend on next 512K maxsize unlimited; While the import was running I got an error: ORA-01652...

Get counts based on a group of dates

sql,oracle11g
I have two tables: Table 1 which the fields o_id (Fk), dt I have Table 2 o_id (Fk), attr1 (Number) My objective is count how many occurrences of attr1 occur where the value is from 0-10, 11-20, 21+. I then need to take these occurrences and Group them by dt...

Oracle - Join two single column queries and perform a grouping

sql,oracle11g
I have a table which I'm running two of the following queries on: First query: select distinct q1."InstituteId" from ( select siid as "InstituteId" from ptable union all select riid as "InstituteId" from ptable ) q1 Second query: select q2."InstituteId" from ( select siid as "InstituteId" from ptable union all...

SQL calculation issue

sql,oracle11g
I am trying to find the cost per category by joining two tables I have wrote this query, but it seems to place the same resulting value for all categories. SELECT category, (select sum(count(partid) * p.cost) from PARTS p join order_line o using (part_id) group by category, p.cost) from parts...

like and regexp_like

sql,regex,oracle,oracle11g,regexp-like
For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

Obiee Formula to show TIMESTAMP only hours format

oracle,oracle11g,obiee
I have the following formula: TIMESTAMPADD(SQL_TSI_SECOND, CAST(("Agent Performance Facts"."ACD After Call Work Outbound Time (Seconds)" + "Agent Performance Facts"."Inbound Talk Time (Seconds)" + "Agent Performance Facts"."Inbound Hold Time (Seconds)") AS INTEGER), TIMESTAMP '1999-01-01 00:00:00') When report created I get time as the following example 1/1/1999 4:08:40 AM I want it...

Data Sync between 2 tables in Oracle Database

c#,oracle11g,sync
i have 2 tables namely table1 (id, name, address, contact_number, gender) and table2 (id, name, contact_number) used Oracle 11g XE. in table1 i have data : (1, john, Jakarta, 123345, male), (2, reina, Bandung, 568955, female). i wanna have data based on table1 to table2 like (1, john, 123345), (2,...

Calculated field returning same value SQL

oracle,oracle11g
I have an issue with the below sub-query: (select AVG(retail) from STOCK where category = 'TOYOTA' or category = 'HONDA') as AVERAGE_SALE_PRICE Entire query: select d.name, s.category,(select AVG(retail) where category = 'TOYOTA' or category = 'HONDA') as AVERAGE_SALE_PRICE from dealer d join stock s using (dealerID) The issue is that...

PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data

sql,oracle,oracle11g,pivot
I have a need to transfer the following data set: in which only the highlighted lines are the one of interest (Tag in ('LN','SN')) as I am only interested in SerialNumber and LotNumber of the products. I would like to convert the data set above into the following data set:...

Query to filter records based on specific conditions

sql,oracle,oracle11g
There is a table a_status_check with the following data: The requirement is: If status LC and BE both are present, then consider only LC. Otherwise, consider BE. Ignore other codes for that id. So, the result should be like: I tried DECODE and CASE functions with no luck. Can...

SQL switch from decode to case

sql,oracle,oracle11g,case,h2
I have a query that does something like this... SELECT * FROM ... ... ORDER BY DECODE(APR(ACC.RATE,'X'), 'FIRST RATE', 1, 'SECOND RATE', 2, 3); Because I am dealing with h2 database and h2 doesnt have decode and decode alias is very difficult to implement so I was wondering if I...

adding a space after each 4th number/digit Oracle 11G

regex,oracle,oracle11g,dbms-output
I am trying to get a space into every 4th number/digit (not character). This is what I come up with: newStudentNumber := regexp_replace(newStudentNumber, '[[:digit:]](....)', '\1 '); dbms_output.put_line(newStudentNumber); result: NL 2345 7894 TUE What I actually want: NL 1234 5678 944 TUE My code replaces the number at every 4th place...

Oracle XE data limit reached - how to reduce tablespace size?

oracle,oracle11g,resize,tablespace,data-files
I have an Oracle XE database with several tablespaces. One of these is used to store image data, and this tablespace has grown to a huge size. We decided that we didn't need to the images any more so deleted them all. This has freed up a lot of space...

DropDownList is not working properly when bound to database and add item manually

asp.net,c#-4.0,drop-down-menu,oracle11g
I have created one webpage in ASP.net C# front end and Oracle 11g back end. Webpage consist of 2 DropDownList, button and GridView. Among these 2 DropdownLists, DropDownList1 contain static value "QC Released" added through ListItem Collection Editor and other DropDownList2 is bounded to database. However, in DropDwonList2 I have...

Oracle Stored Procedure - How to compare type NUMBER with intergers?

sql,if-statement,stored-procedures,oracle11g
I have following stored procedure. Parameter FLAG_ passed in is only allowed to accept null, 0 or 1. But the condition checking (FLAG_ != 0 OR FLAG_ != 1) doesn't work. I guess this is because type for FLAG_ is NUMBER which include float. Is there a way to compare...

ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE

oracle,oracle11g,export,ddl,dbms-scheduler
I was trying to exported the complete scheduler chain created on Oracle 11g (including steps, rules, etc), when I got this error : ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE in function GET_DDL ORA-06512: at "SYS.DBMS_METADATA", line 5805 ORA-06512: at "SYS.DBMS_METADATA", line 8344 ORA-06512: at line 1 Message meaning...

SQL query transposing columns

sql,oracle11g
I have a table in the following structure: id | att1 | att2 | att3 ----------------------- 1 | a1 | b1 | c1 2 | a2 | b2 | c2 3 | a3 | b3 | c3 And I want to transpose the columns to become rows for each id....

Identifier 'EXTRACTVALUE' must be declaredCompilation failed

oracle,plsql,oracle11g,oracle-apex,plsqldeveloper
I tryed to retrive xml data to variable but giving me error saying "identifier 'EXTRACTVALUE' must be declaredCompilation failed" VarConverstionFact NUMBER; BEGIN VarConverstionFact := TO_NUMBER(ExtractValue(PXML, '/OBJECT/Item/ItemUomLevelsList/ItemUomLevels/ConversionFactor')); IF VarConverstionFact > 0 THEN SELECT BIZZXE_V2_SCH.SQ_ITEM_ID.NEXTVAL INTO varID FROM DUAL; END IF; END; This is save xml nodes <Item> <LocationId>0</LocationId> <Id>0</Id> <ItemId>0</ItemId>...

Oracle SQL random value from string set

sql,plsql,oracle11g
In Oracle SQL 11g I am trying to fill a table with procedure. For some columns I need to take data randomly from predefined set of strings. How do I define such set and take data from it by random order?

How can I modify this SELECT query that return to me a set of rows that contains the same data to obtain a single row?

sql,database,oracle,oracle11g,distinct
I am not so into database and I am working on a project that perform this SELECT query on an Oracle database: SELECT coda.desc_unita1, coda.pk_coda, coda.stato, coda.fk_tipo_doc, coda.descrizione, coda.num_doc, coda.data_in as data_in, coda.indirizzamento, coda.segnalazione, coda.autorizza , coda.fornitore, coda.data_app, coda.motivo_rifiuto, coda.tot_fattura, coda.data_doc, coda.fk_piva_mittente, coda.fk_piva_destinatario FROM (SELECT d.rag_soc1 desc_unita1, a.pk_coda, a.fk_tipo_doc, b.descrizione,...

Oracle SELECT * FROM LARGE_TABLE - takes minutes to respond

oracle,oracle11g
So I have a simple table with 5 or so columns, one of which is a clob containing some JSON data. I am running SELECT * FROM BIG_TABLE SELECT * FROM BIG_TABLE WHERE ROWNUM < 2 SELECT * FROM BIG_TABLE WHERE ROWNUM = 1 SELECT * FROM BIG_TABLE WHERE ID=x...

How to grant only local login access to user in Oracle 11g?

database,oracle,oracle11g
Hi i have readed some tutorials and official Oracle docs. I'm trying to create an user who only can access locally to the instance. For example in MySQL is so simple: If you want to create a user only in localhost: CREATE USER [email protected] IDENTIFIED BY 'pass'; But in Oracle...

EXECUTE IMMEDIATE PL/SQL?

sql,oracle,plsql,oracle11g
CREATE OR REPLACE TRIGGER P88 AFTER INSERT ON reparation FOR EACH ROW DECLARE vope number; BEGIN SELECT observation_reparation into vope from repartion; if(vope IS NULL)THEN EXECUTE IMMEDIATE 'ALTER TABLE ' || reparation.observations_Reparation || ' MODIFY libelle_piece NVARCHAR2(50)'; END IF; END; / I get this: error:table or view does not exist....

how to get previous day datetime at specific hour in oracle sql

oracle11g
How to get datetime in oracle of previous day at specific hour? Say for example today is June 1st and i want to get the datetime of previous day 6pm? i need to go back one day at specific hour....

Index scan results in a lot of physical reads (Oracle)

sql,indexing,oracle11g,pagination,query-performance
There have two tables with more than 20 million records. The database version is Oracle 11.2.0.3.13 Standard Edition SQL> select count(*) from atsd_tra_trade_print; COUNT(*) ---------- 20000000 SQL> select count(*) from atsd_mob_allocation_pos; COUNT(*) ---------- 20551780 We run the following queries on the tables. Query structures are similar. Query is intended for...

Oracle 11g Insert Statement into Multiple Tables

sql,oracle,oracle11g,triggers,sql-insert
I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements: INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE)); INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id,...

PLSQL generate random integer

sql,oracle,plsql,oracle11g,oracle-sqldeveloper
In Oracle Sql developer 11g, how do I generate a random integer and assign it to a variable? This is what I've tried so far: S_TB := SELECT dbms_random.value(1,10) num FROM dual; With this code I got error: S_TB := SELECT dbms_random.value(1,10) num FROM dual Error report - Unknown Command...

How do i format Oracle Output for this Block?

plsql,oracle11g
I'm stuck on this block of statements and don't know how to program the output when the code runs to show the amount of the payment as $10.00 and the balance remaining in the same format. My code also prompts me twice for idpledge... I've got some markup in the...

Select distinct lines by a field

sql,oracle11g,oracle10g
I am making a select that returns me a table likes this Name surname Jhon a Jhon b Jhon c Joe a Joe b Joe c But what I need to get is just one occurrence of Jhon and one of Joe with one of the surnames. I can only...

Default Prompt to first day of current month on OBIEE

oracle11g,obiee
I have a Analysis created in OBIEE 11G, by default I want it to have the default values in: is between Calendar Date [First day of current month] - [Yesterday]. For the first day i use the following (but didn't work): TIMESTAMPADD (SQL_TSI_DAY, (DAYOFMONTH (CURRENT_DATE) * -1) + 1, CURRENT_DATE)...

Oracle: do Truncates maintain Atomicity within a transaction?

database,oracle,oracle11g,transactions,oracle10g
Oracle 10g -- due to a compatibility issue with a 9i database, I'm pulling data through a 10g database (to be used by an 11g database) using INSERT INTO...SELECT statements via a scheduled job that runs every 15 minutes. I notice that TRUNCATE statements are much faster than DELETE statements...

Oracle cilent and Perl installation on Centos

oracle,perl,oracle11g,installation
I'm not abble to install DBD-Oracle. I have installed the oracle client following these steps: I have downloaded the instant Client ZIP file. Unziped the packages into directory "/opt/oracle/product/instantclient_12_1". Created the appropriate libclntsh.so and libocci.so links : cd /opt/oracle/product/instantclient_12_1 ln -s libclntsh.so.12.1 libclntsh.so ln -s libocci.so.12.1 libocci.so Setted the environment...

How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1

oracle,oracle11g,drop-table
When I'm trying to drop table then I'm getting error SQL Error: ORA-00604: error occurred at recursive SQL level 2 ORA-01422: exact fetch returns more than requested number of rows 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement...

Update query in oracle

sql,oracle11g,sql-update
I need to update all gmail.com email id in the table emails to yahoo.com. I have this below query which doesn't works. Can someone help? update emails set email = (select CONCAT(email, '@yahoo.com ') "email" from emails ) where email like '%@gmail.com%' ...

Oracle SQL how to convert time zone string to date

sql,oracle,oracle11g
I have following 2015-06-17T00:00:00.000+05:00 string. I want to convert this string to Date using oracle sql. I tried lot of format mask but none works for me : SELECT TO_DATE('2015-06-17T00:00:00.000+05:00','yyyy-mm-dd HH24:MI:SS TZR') FROM DUAL; Any idea which format mask should i apply for above conversion. Also please note that i...

Oracle next_day function returns wrong date

sql,oracle,oracle11g,date-arithmetic
I am running Oracle Database 11g Express Edition. When I issue: select next_day('04-JUN-15', 'Friday') from dual; I am getting correct result which is '05-JUN-15'. But when I am issuing: select next_day(DATE '15-06-04', 'Friday') from dual; I am getting pretty strange result which is '07-JUN-15'. At the same time both dates...

How to derive years of service for employees that have termed and returned several times

sql,oracle,oracle11g
I'm working with Oracle and have sample data to show you regarding my question (sample data is below). I'm trying to figure out how to derive years of service for employees that have terminated and returned several times (up to 6 times). The business rules are that if someone leaves...

exception is not coming with sql max function

oracle,plsql,exception-handling,oracle11g,max
I wrote the below query expecting NO_DATA_FOUND exception to be thrown when no rows found. BEGIN SELECT MAX(A_id) + 1 INTO id_variable from table_A; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT MAX(A_id) + 1 INTO id_variable from table_A_archive; END; there is no data in table_A but no exception was thrown and eventually...

Adding max(count) and min(count) to query

sql,oracle11g,max,min
I am trying to add max and min to the query below: SELECT category, COUNT(distinct VIN) COUNT FROM stock group by category order by category ASC The desired results are to show the category with the most items in the count and to show the category with the least items...

add $ and round 2 decimal places SQL

sql,oracle11g,format
I am looking to format an ouput which is created by a sub-query, this sub-query produces a calculated field which i would like to format as $XX.XX. Sub query: (select avg(retail) from cars where brand = 'FORD' or brand = 'TOYOTA') as AVG_BRAND_PRICE_01 I basically just want to add a...

ASP.net website is not working properly while configuring in Internet Information Services

c#,asp.net,oracle11g,iis-7
I have created small webpage in ASP.net using c# in front end and oracle 11g at back end. Webpage is running successfully on my local pc as well as in windows server 2008 r/2 in visual studio 2010. So, i have configured same wabpage in IIS in server 2008 r/2...

Load balancing not working OSB

oracle11g,soa,servicebus,osb,bus
I am trying to understand the Oracle Service Bus Load Balancing. I have: One weblogic server 10.3.6.0, with 3 Managed servers (lb,osb1,osb2): 2 Managed servers are in cluster configuration: osb1 - with port 7101 and osb2 - with port 7202 The manager Server called lb was configured as proxy server...

SQL Oracle: Combining consecutive rows

sql,oracle,oracle11g
| RecordId | high_speed | speed | DateFrom | DateTo | --------------------------------------------------------------- | 666542 | 60 | 10 | 09/11/2011 | 10/11/2011 | | 666986 | 20 | 20 | 11/11/2011 | 11/11/2011 | | 666996 | 0 | 0 | 13/11/2011 | 17/11/2011 | | 755485 | 0...

Get the first 10 rows for each group

sql,oracle,select,oracle11g,top-n
I have three tables: Customer(IdCustomer, Name) Product(IdProduct, Product) Order(IdProduct, IdCustomer, nbOrders) So the Order table stores how many times a customer has ordered a product. I need a view like this: TopOrder(Name, Product, nbCommands) But I only want 10 products for each customer, the ones he ordered the most and...

Difference between dba_SEGMENTS and dba_data_files

mysql,sql,database,oracle11g,oracle-sqldeveloper
Shouldn't these two queries give the same result? First is a sum of all segments while other is the sum of all data files. They are giving different results for me. While the first one gives me 149522MB, the other gives 214973.34 MB select sum(bytes)/1024/1024 MB from dba_segments; select sum(bytes)/1024/1024...

DB Index not being called

oracle,performance,oracle11g,oracle10g,database-performance
I know this question has been asked more than once here. But I am not able to resolve my issue so posting it again for help. I have a table called Transaction in Oracle database (11g) with 2.7 million records. There is a not-null varchar2(20) (txn_id) column which contains numeric...

Creating an Oracle User if it doesn't exist already

oracle,oracle11g,createuser
I am trying to create a script that will create users if they do not already exist. CREATE USER "Kyle" PROFILE "DEFAULT" IDENTIFIED BY "password" ACCOUNT UNLOCK WHERE NOT IN //Also tried 'WHERE NOT EXISTS' ( SELECT username FROM all_users WHERE username = 'Kyle' ) The following error is given:...

Query to show tables with LOGGING turned on?

oracle11g
Is there a way to query an ORACLE database to return tables that have LOGGING turned on? or off? I have searched the docs but cannot seem to find anywhere that answers this.

SQL query display min count rows only

sql,oracle11g,count,min
I am trying to only display the cost value of cars which belong to categories with the minimum category count totals This the query I am trying to use: select model, VIN,cost from stock Desired Output: Model - VIN - Cost STI XXXXXXXXXXXX $55,000 I understand that a having operator...

Why I can't perform this simple insert operation? How can I solve this date format issue?

sql,database,oracle,oracle11g,rdbms
I am not so into database and I have the following problem trying to implement a simple insert query that involve a date field on an Oracle database. So I have a table named FLUSSO_XMLSDI that have the following structure (this is the result of a select *): NUMERO_FATTURA DATA_EMISSIONE...

How to update rows based only on ROW_NUMBER()?

sql,oracle,oracle11g
Such SQL query: SELECT ROW_NUMBER() OVER (PARTITION BY ID, YEAR order by ID ), ID, YEAR from table t give me following query set: 1 1000415591 2012 1 1000415591 2013 2 1000415591 2013 1 1000415591 2014 2 1000415591 2014 How could I update records with ROW_NUMBER() equals to 2? Other...

How to Update the record if the time difference between existing record has been more then time minutes?

sql,oracle,oracle11g,timestamp-arithmetic
I have a column "STARTDATETIME" with datatype TimeStamp. Now i want to check that if existing record time is 10 minutes more then the current time then i will update the status of that row. String updateData = "update test_bank set status = 'FAILED' where '"CurrentDateTime()+"' > startdatetime"; ...

Order By Two Columns Simultaneously SQL

mysql,sql-server,oracle11g
I have a SQL question about how my query output is being ordered. Here is how the output looks now: REFERRED_FROM REFERRED_FROM_COUNT REFERRED_TO REFERRED_TO_COUNT sample sample number sample sample number sample sample number sample sample number sample sample number sample sample number And here is my desired output: REFERRED_FROM REFERRED_FROM_COUNT...

Calculating overlap between groups

sql,oracle,oracle11g,pivot
I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value. For example, the...

Rename column named TYPE, LEVEL in sqlplus

sql,oracle11g,sqlplus
This needs to be done in sql plus. Hi, I'm struggling to rename two columns in my table. They are named "TYPE" and "LEVEL". This needs to be done in sql plus with no exception. The following does not work (works in sql developer tho): alter table client rename column...

String sequential grouping in Oracle

sql,regex,oracle,oracle11g
My task is to group similar digits/characters in a given string, For e.g.: The output of the SQL for the string 4455599 should be 44 555 99 and is working with following query: with t(str) as ( select '4455599' from dual ) select listagg(str_grouped ,' ') within group (order by...

OJDBC connection with NetBeans

java,database,netbeans,oracle11g,ojdbc
I'm trying to establish a new connection with Oracle database with ojdbc7 driver and when I get to the "Customize Connection" step, i get the following message: Cannot establish a connection to jdbc:oracle:thin:@localhost:1521:XE using oracle.jdbc.OracleDriver (ORA-01017: invalid username/password; logon denied ) Can anyone explain what to do? I tried putting...

Calculate difference between two rows from two different table

sql,oracle,oracle11g
I have two tables with the structures as below table one ╔════╦═══════════╦═══════╦══╗ ║ ID ║ Date ║ value ║ ║ ╠════╬═══════════╬═══════╬══╣ ║ 1 ║ 1/1/2015 ║ 234 ║ ║ ║ 2 ║ 1/20/2015 ║ 267 ║ ║ ║ 3 ║ 1/25/2015 ║ 270 ║ ║ ╚════╩═══════════╩═══════╩══╝ second table...

Generate a .log file plsql / oracle

sql,plsql,oracle11g,plsqldeveloper
What i want do, is to generate a .log file that describes all my error, start time, end time, and so one. I found a way to have something like that but not in corect way. I want to generate that file automaticaly, without being required to define it manually....

Best approach for executing massive insert select from statements

sql,oracle,oracle11g,database-performance
I have over 100k insert select from statements which I will break into smaller batches. I came up with two approaches and not sure which one is more effective. Please give me your thoughts. I could use APPEND hint but I have to put a Commit after each insert statement...

How can I know if a field value that represent the primary key of a table is automatically generated when the user insert a new record into the table?

sql,database,oracle,oracle11g,oracle-sqldeveloper
I am not so into database and I have the following doubt. I am working on an Oracle database using Oracle SQL Developer as "IDE". Here I can examine a table named coda_rx that have some fields. One of this field is named PK_CODA (it is the first one) and...

ORA-06502 Error Only in SQLPLUS

oracle,plsql,oracle11g,sqlplus
I have an Oracle 11 database on a development server. I'm trying to execute a stored procedure: declare date_start varchar2(15); date_end varchar2(15); begin Fecha_inicio := '01/01/2014'; Fecha_fin := '01/01/2016'; FCBK.PKG_PACKAGE.Generate(Fecha_inicio,Fecha_fin); end; If I execute the procedure from SQL Developer or from SQL*Plus on my personal Windows machine, the procedure has...

Writing an analytic function to mark last date of some column

sql,oracle,oracle11g,row-number
Can you help me to write an analytic function that marks the last date a client's service was stopped. For example one client has 2-3 stops of his service, and I would like to count how many stops there are, and to mark last date of stopping. I'm using SELECT...

oracle sql error Case When Then Else

sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Check if a string value matches up with the content of an existing table in Oracle 11G

regex,oracle,if-statement,oracle11g
At the moment I am not working as efficient as I could be. For the problem I have I almost know certain that there is a smarter and better way to fix it. What I am trying to do: I got a string like this: 'NL 4633 4809 KTU' The...

Invalid operation for read only resultset when using select for update nowait in multithreaded environment

java,multithreading,jdbc,oracle11g
For an Oracle database, the following program will throw SQL exceptions only for some threads. Why downgrading resultSetConcurrency from CONCUR_UPDATABLE to CONCUR_READ_ONLY? In a single thread environment this is not happening. import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Main extends Thread { public static final...

How to mask date format in sql?

sql,oracle,oracle11g,date-arithmetic
There is open date 2015-05-19 10:40:14 and close date 2015-05-20 09:21:11 when I subtract them I am getting (close_date.date_value - open_date.date_value) some 9.45104166666666666666666666666666666667E-01 value I want to ignore the time 10:40:14 and 09:21:11 from 2 dates similarly I am subtracting (SYSDATE - open_date.date_value) and get the number of days in...

TABLE/CAST/MULTISET vs subquery in FROM clause

sql,oracle,oracle11g
The following query doesn't work. It is expected to fail since temp.col references something that is unavailable in that context. with temp as ( select 'A' col from dual union all select 'B' col from dual ) select * from temp, (select level || temp.col from dual connect by level...

How to REPLACE any form of the word null using decode in Oracle?

sql,oracle,oracle11g,oracle10g
SELECT DECODE( add1, null, '.', ' ', '.', '{null}','.', LTRIM( RTRIM( REPLACE( add1,'"','.'), ' '), ' ') ), code, add1 FROM addrlist I am trying to replace any null word (any case-insensitive word null or nuLL or NulL etc.) or blank value of add1 (varchar2) with a dot (.). The...

Oracle More than one materialized view on a materialized log

oracle,oracle11g
I would like to have more than one materialized view with refresh fast on commit. For "refresh fast on commit" you need a materialized view log. Obviously a refresh fast on commit needs the log. The question is can I have more than one materialized view accessing the log. Obviously...

Oracle authentication not working?

oracle,plsql,oracle11g,procedure,mod-plsql
I implemented a LogOut button which logouts user from webpage. Server is Oracle HTTP server. When clicked on LogOut it executes below procedure PROCEDURE log_me_off IS BEGIN -- Open the HTTP header owa_util.mime_header('text/html', FALSE, NULL); -- Send a cookie to logout owa_cookie.send('WDB_GATEWAY_LOGOUT', 'YES', path=>'/'); -- Close the HTTP header owa_util.http_header_close;...

SQL LISTAGG Min and Max Function Issue

sql,oracle,oracle11g,minmax,listagg
Need to find min and max date inside of list tag Like this in one column Min Date:01/01/2015 / Max Date:01/05/2015 SELECT D.ITEM_ID AS "ItemId", C.NAME AS "ItemName", D.UOM_ID AS "UomId", B.DESCRIPTION AS "Uom", sum(D.REQUIRED_QTY) AS "Quantity", LISTAGG(A.REASON, ', ') WITHIN GROUP (ORDER BY A.REASON) "ReasonType", LISTAGG(D.REQUIRED_DATE, ', ') WITHIN...

Retrieve meta-data of Object/Record present inside package - Oracle Dataabse

database,oracle,oracle11g
I have to retrieve meta-data of RECORD residing inside a package. If Object is created outside package I am able to retrieve its meta-data from "DBA_TYPE_ATTRS" table.However, it is not working if RECORD/OBJECT is inside package. create or replace Package sp_fun_package is TYPE pkg_table_row IS RECORD ( name varchar2(100), age...

How to update an table in oracle db using oledbconnection from asp.net c# code

c#,oracle11g,oledbconnection
I am trying to update an table in oracle 11g database from c# code using an oledbconnection. The query formation is ok and then hangs at execute. No error or exception is thrown. Its like an endless loop. Select and insert query are working fine but update fails.