FAQ Database Discussion Community
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...
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...
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...
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...
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:...
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...
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...
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]m AS1 2 6 Adil [email protected] AS1 3 TABLE AB ID NAME EMAIL TRAID STUSYID ----------------------------------------- 1 Pankaj [email protected] PL1 5 2...
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...
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...
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...
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.
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...
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....
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...
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 |...
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...
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?
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...
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...
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...
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...
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
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...
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...
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...
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....
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...
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...
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...
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...
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 ] ',' || ' , ' || " , " "...
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...
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...
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....
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...
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...
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...
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....
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...
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...
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...
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...
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...
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,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;...
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,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...
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;...
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...
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"; ...
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...
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,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...
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...
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...
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,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...
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...
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....
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,...
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...
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...
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...
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...
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...
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,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...
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>...
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)...
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...
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...
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,...
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...
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...
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...
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....
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,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...
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...
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%' ...
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...
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...
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...
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...
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?
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...
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...
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...
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...
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...
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,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...
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...
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...
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,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...
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,...
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...
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:...