FAQ Database Discussion Community


Need only one match from a Left Join

sql,oracle
Hello thanks for taking the time to read this. I have a table TBL_INCIDENT containing columns TXT_INC_ID and TXT_SERVICE, I am using a LEFT JOIN to join it to table TBL_ASMS containing TXT_APPLICATION_ID. The issue I am having is the join will have multiple matches and I only want the...

Why does the date doesn't match with what I have inserted into the database?

sql,database,oracle
I tried to display the sqlcommand after I saved into the database. Everything seemed to work pretty fine, but when I opened my table from TOAD, the dates are wrong. Here is my sql command: INSERT INTO USERTASK (USERTASKKEY, USERID, TASKKEY, TASKDATE, CREATEDATE, CREATEUSERID) VALUES (USERTASKSEQUENCE.NEXTVAL, 'admin2', '1', TO_DATE('05-06-2015','yyyy/mm/dd HH24:MI:SS'),...

Why I can't compare dates?

sql,oracle,oracle10g
I have this simple query: select sm.id, h.time from main_data sm INNER JOIN TA t on t.id = sm.id AND t.first=1 LEFT OUTER JOIN History h on h.id= sm.id WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.2015', 'DD.MM.YYYY')) AND trunc(TO_DATE('07.06.2015', 'DD.MM.YYYY')); h.time looks like 07-MAY-15 The above query returns no results, even if...

Exclude / ignore weekends in Oracle SQL

sql,oracle
I made a query that groups text together and their start and end date, i want to exclude/ignore the weekends. In this example 11-12 and 18-19 april are the weekends create table t ( d date, v varchar2(10)); insert into t values (date '2015-04-10', 'ne'); insert into t values (date...

PLSQL - Error in associative array

oracle,plsql,associative-array
Im trying to delete a set of tables and afterwards I want to recreate them using as select from. For couriousity I wanted to do this with an associative array. Unfortunately something is messed up, several errors appear and I can't find the reasons. This is the code: DECLARE TYPE...

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...

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...

SQL Keep (dense_rank last order by) to H2 database

sql,oracle,h2
I have following select statement SELECT c.car_id MAX(m.mod_number) KEEP (DENSE_RANK LAST ORDER BY DECODE(m.mod_number 'SP1', 10, 'SP2', 20, 'SP3', 30, 40)) FROM CAR c, MANUFACTURE m WHERE c.car_type = m.car_type AND m.make LIKE 'FOR%' GROUP BY c.car_id; I wanted to change it into H2 grammar for unit testing but I...

how to check a directory exist and can write file

oracle,shell,unix,sql-loader
I need to check directory test_file exist, if yes then need to check directory test_file is having write permission so that files will be writen in the same. this functionality i needed in shell script(ksh). What would be return code from sqlldr for single table load and multi table...

Return the sum of the count of two seperate tables PL/SQL

database,oracle,plsql,oracle12c
I'm trying to search through two separate tables for the COUNT of a particular value and return it from a function. FUNCTION check_parts (p_partno IN VARCHAR2) RETURN NUMBER IS out_exists NUMBER; sub_exists NUMBER; BEGIN SELECT COUNT(*) INTO out_exists FROM outline_pn op WHERE op.outline_pn = p_partno UNION ALL SELECT COUNT(*) FROM...

Cant delete in database because of constraints

c#,sql,asp.net,oracle
I am making an asp.net project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

Error with selecting sysdate between time range

sql,oracle
I want to fetch the data for sysdate-1 '00:00:00' between '23:59:59' time range. I have tried, but i am getting error. This is my query.. select id_value, tn, LINE_TYPE_ID, ACCOUNT_TYPE_ID, PORT_ID, CURRENT_STATUS_ID, ERROR_CODE, ERROR_DESC, CRM_APP_CODE from np_tn_dtls a join np_port_req_dtls b on A.NP_TXN_ID=B.NP_TXN_ID left join np_subscriber_dtls c on a.NP_TXN_ID=C.NP_TXN_ID left...

Fill with zero to complete a defined number in sql [closed]

sql,oracle
I need to complete cards numbers in sql. I have the prefix =11111 and the number of the card which is variable, therefore it could be '25' or '2130' but at the end I must have 14 numbers. So I need to fill spaces with zeros. I've read about 'LPAD'...

Using if-else condition inside a insert-select block

oracle,plsql,case,insert-select
I have two tables elig(mID, startDate, endDate) and claim(mID, serviceDate). I have to check if the serviceDate is between the startDate and endDate, and insert into a new table hasClaim(mID,startDate,endDate,Flag). The flag is to be set to 'Y' if the condition in satisfied and 'N' otherwise. What I have done...

Notice: Array to string conversion in “path of php file” on line 64

php,mysql,arrays,oracle
PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

Unable to ge results using CASE,AND,OR operators (SQL)

sql,oracle
I have been trying to get the following query to work using CASE statement, OR/AND operators. I would like to get values from E and H: Query : SELECT CAT_CD, PRD_CD, PTG_CAT, HN, OTC_FL, CASE WHEN PTG_CAT = '1' THEN 'MEGA POWER' WHEN PTG_CAT = '2' THEN 'POWER' WHEN PTG_CAT...

Order by case insensitive in oracle

sql,oracle
I want to order a following text in following order but, after trying the following query it is not working. values to order is "A", "B", "Y", "Z", "a", "b", "y", "z". Expected result "ZzYyBbAa" SELECT COL FROM TABLE ORDER BY COL DESC; SELECT COL FROM TABLE ORDER BY UPPER/LOWER(COL)...

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:...

Mass Export of BLOB data to CSV

oracle,csv,export
I am working with an older Oracle database, I don't know which version of oracle, sorry, and I need to do a mass export of 200,000+ files worth of HTML data stored in BLOBs. I have downloaded and used both Toad and SQLDeveloper (Oracle's own DB GUI tool) and at...

Trigger to find next available inventory location

oracle,triggers,inventory
I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item...

Hide SQL > statements in the spool file

oracle,sqlplus,spool
I want to hide the "SQL> @test.sql" and "SQL> spool off" in the file test.txt(spool file) my steps set heading off set pages 0 set trimspool on set lines 120 set feedback off set echo off set termout off spool test.txt @test.sql spool off test.txt SQL> @test.sql 2002452199 2797 9/1/2014...

Oracle SQL Find First Match from single table [closed]

sql,oracle
I have a table that lists concurrent program IDs and names. However, the table allows more than one name for each program ID. I'm needing to report some data about the programs, but am having trouble getting it to display correctly, as the program name is what is being displayed...

Distinct clause on one attribute and another attribute at random

sql,oracle
Suppose i have this table: A B 1 John Smith 1 Mr. John Smith 2 Tom Cruise 2 The actor Tom Cruise 3 Jim I want to select distinct attributes A, but I don't care which of the records in B the query will return, because I know that, if...

Groovy - timestamp from minutes

oracle,grails,groovy,timestamp
I have an array or times/values coming back to be in an array like: [0, 60] Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?...

Column ambiguously defined error with Oracle Merge statement

sql,oracle
I have a problem with this query in Oracle SQL. Errore alla riga del comando:18 Colonna:42 Report errori: Errore SQL: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: This is the query, but i don't see the column ambiguously defined: MERGE INTO T_HPSM_CM_UBIS H USING (SELECT...

How to pull the date in proper format from timestamp

oracle,timestamp
I am working in Oracle SQL Developer and have the following code SELECT Trunc(assigned_date, 'IW') AS bonus_week FROM (SELECT Trunc(blf.assigned_date) AS assigned_date FROM olap.bonus_lifecycle_fact blf) I get output in this form 07-12-03 08-01-28 08-01-28 08-01-28 08-01-07 Which is DD-MM-YY That's fine, except when I export to Excel I get some...

Is it possible to use OracleDataSource for some other database except for Oracle?

java,oracle,hsqldb
What I want is to use OracleDataSource with HSQLDB database. Is it possible?

Changing Primary Key in Oracle

oracle,django-models,oracle-sqldeveloper
I'm updating a table that was originally poorly designed. The table currently has a primary key that is the name of the vendor. This serves as a foreign key to many other tables. This has led to issues with the Vendor name initially being entered incorrectly or with typos that...

Specified cast is not valid. Is it because I am attempting to cast an object?

c#,oracle,oraclecommand
I have the following C# code that I am using to attempt to query an oracle database. I am simply trying to get the numeric result so that I can output it to the console and eventually hold it in a variable. I keep getting the following error though: System.InvalidCastException:...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?

oracle,plsql
I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

How to use subquery result as the column name of another query

sql,oracle,plsql
I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

Need to fetch Oracle RAW in C# string datatype “as is”

c#,oracle,entity-framework-6
Help me out, requirement is that I need the same value of Oracle RAW to be in C# as a string. I am able to save String as RAW value in oracle table, but while fetching it is giving exception "Object must implement IConvertible" which I can understand that since...

Trying to access Oracle's Maven repository

oracle,maven,repository
I'm attempting to access Oracle's repository. Oracle doesn't make it easy. However, I'm attempting to follow the documentation that Oracle provided. I've: Approved the licensing agreement on my system (in case there's some sort of cookie that needs to be set). Set both M2_HOME and MAVEN_HOME to /usr/share/apache-maven. Created an...

update a table from another table using oracle db

sql,oracle
I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = p.name where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

Explain Plan output in Oracle

oracle
I'm not expert on Oracle just started working around. When I execute following queries it gives me output in |(pipeline) format. I want EXPLAIN PLAN output in tabluar or json or xml, etc... Is it possible? EXPLAIN PLAN FOR SELECT * FROM user_master; SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table')); Output: Plan hash...

Update query in liquibase

oracle,liquibase
I need to run the below update query in liquibase, but I am not sure about the syntax. Can anyone please help: update xyz.users set email = (select CONCAT(username, [email protected] ') "email" from xyz.users ) where email like [email protected]%' ; ...

SQL - Muliplying colums

sql,oracle,maximo
Please help I cannot get this to work SELECT PM.PMNUM, (COUNT(ROUTE_STOP.LOCATION) * JOBPLAN.JPDURATION) FROM PM LEFT OUTER JOIN ROUTE_STOP ON ROUTE_STOP.ROUTE = PM.ROUTE LEFT OUTER JOIN JOBPLAN ON JOBPLAN.JPNUM = PM.JPNUM GROUP BY PM.PMNUM ...

PLSQL move data from csv list stored in varchar2 to a collection of integers

sql,oracle,collections,plsql
Oracle Express 11G R2: In PLSQL I have a varchar2 containing a CSV list of values like this: vList:='1212,3232,3232,4343,54545,65654,65665,65654,788787' I would like to move these into a collection, so i create a new type: CREATE TYPE INTEGER_TT AS TABLE OF INTEGER; then declare my variable: my_list INTEGER_TT; What is the...

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...

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....

Entity Framework code-first: querying a view with no primary key

sql,oracle,entity-framework,view,ef-code-first
Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

Merge results from two separate columns into one elongated column

sql,oracle,select
My table: ID Name Status1 Status2 ------------------------------------- 1 foo bar grain 2 bar foo sball 3 foo bar grain 4 far boo sball I need for it to actually come out like this: ID Name Status ------------------------------- 1 foo bar 1 foo grain 2 bar foo 2 bar sball 3...

ORA-04091 mutating table error when calling a function from a procedure

oracle,stored-procedures,plsql
I have a task: Write a procedure to update salary (salary * % of increment) in emp table based on grade. Use function to get increment This is my procedure: CREATE OR REPLACE PROCEDURE sal_incre IS CURSOR c_cur IS SELECT * FROM emp_task; BEGIN UPDATE emp_task SET sal = sal...

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 Optimization: query table with different where clauses

sql,oracle,select,sql-optimization
I have the following SQL query: SELECT ( SELECT SUM(c1) FROM t2 WHERE t1.c1 = t2.c1 AND t2.c3 = 'NEW' AND t2.c4 = TRUNC(SYSDATE) AND t2.c5 = 'N' ) SUM, ( SELECT sum(c2) FROM t2 WHERE t1.c1 = t2.c1 AND t2.c3 = 'OLD' AND t2.c4 = TRUNC(SYSDATE) ) SUM2 FROM...

Create ranges of minutes (15) in select

sql,oracle
I am creating a select which brings me beyond the results of the table a column with a range of 15 minutes. What I am trying to do, is to group hours between 00:00 and 00:15 in a 0:15 range. After grouping the registers between 0:16 and 0:30 in a...

How to specify date more than 3 years ago?

sql,database,oracle,date-difference
Display details of book published more then 3 years ago with sales of less than 10000 copies My teacher request to modify from this Select title_id , title , pubdate , current_date-pubdate, ytd_sales from titles; this is what I did Select title_id , title , pubdate , current_date-pubdate, ytd_sales...

How to write an SQL query that returns count = 0 when no records found in group

sql,oracle
I have a client table from which I need to select data in the following order: Gender Count ----------------- Female 10 Male 50 Unknown 2 (The above table data is just as an example) To achieve this, I used the following SQL: SELECT decode(gender, 'F', 'Female', 'M', 'Male', 'Unknown') "Gender"...

Syntax for declaring variable and executing procedure in Aqua Data Studio?

oracle
If I run this in Oracle SQL Developer: DECLARE num NUMBER; BEGIN num :=5; dbms_output.put_line(num); END; It returns "5" If I run the same code in Aqua Data Studio 10.0.7 (with ';' statement separator checked): [Error] Script lines: 39-40 ------------------------ ORA-06550: line 2, column 7: PLS-00103: Encountered the symbol "end-of-file"...

SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query

sql,oracle,sql-update
I have SYSTEM_SQL_CHECK table in which i have saved sql in CHECK_SQL column. This column is Varchar data type. Now i want to update particular sql.I have written below update sql query but it gives an error SQL Error: ORA-00933: SQL command not properly ended. I also tried to query...

SQL Oracle | How to delete records from a table when they match another table?

sql,oracle,delete
How would I delete records from a table where they match a delete table? As in, I have a table of record keys that say what need to be deleted from my main table. How would I write a delete to say "delete anything from my main table where this...

Connecting codeigniter to mysql and oracle in the same application

mysql,oracle,codeigniter
I'm using Mysql and Oracle for my CI application. I tried to connect it but I found that I cannot make a query to Oracle database. It always gave an error that the table is not exist. I already set the database.php to something like this $active_group = 'oracle'; $active_record...

Oracle SQL - Returning the count from a delimited field

oracle
I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario: I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that...

Can't obtain connection with the DB due to very long schema validation and connection reset afterwards

java,oracle,hibernate
I have an app where I use Hibernate/Oracle 11g XE/Jboss6.2 and I am having trouble starting up the application on my homolog server at DigitalOcean (Jboss and Oracle xe installed locally). Running everything on my dev machine I have no problem at all and it starts in about 30s, but...

How do a repeat a query for past 30 days without union 30 times?

oracle
I have a query that returns a value for today (sysdate) but what is the best way to repeat the query for the past 30 days without creating 30 separate queries unioned together? for example here is the basic query: SELECT sum(duration)/3600 FROM IGN_OEE_EVENTS_D where asset_id = 1978 and event_type...

Data filtering performance:sqlldr+triggers or sqlldr+stored procedure?

oracle,sql-loader
I have to load millions of records on my DB, said records need further processing according to some of their columns values. In particular if a row satisfies a condition I save that row in table B, otherwise I save it in table C. According to documentation sqlldr with DIRECT...

'ORA-00942: table or view does not exist' only when running within a Stored procedure

oracle,plsql
This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that...

How do I extract data from an XMLTYPE field with no namespace and label?

sql,xml,oracle,plsql
Forgive me for being a total beginner. I am looking at a column named FORM_XML that is described as data type XMLTYPE. The contents of one field is: <Form FormID="0" Name="Preventive Care(F)"> <FormObject Name="prevcare01" Type="DateTime" Label="Physical Exam" EditValue="04/05/2007" /> <FormObject Name="prevcare02" Type="DateTime" Label="Lipid Profile" EditValue="NoEditValue" /> <FormObject Name="prevcare03" Type="DateTime" Label="Health...

select only one row that has the highest count in sql

sql,oracle,select
I need to select one row only which has the highest count. How do I do that? This is my current code: select firstname, lastname, count(*) as total from trans join work on trans.workid = work.workid join artist on work.artistid = artist.artistid where datesold is not null group by firstname,...

how to get lat and long from sdo_geometry in oracle

oracle,latitude-longitude,oracle-spatial
how can i get lat and long from point in oracle? Like this: MDSYS.SDO_GEOMETRY(2001,4326,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1), MDSYS.SDO_ORDINATE_ARRAY(51.702814,32.624736)) ...

Dealing with nulls when selecting in MyBatis

java,oracle,mybatis
I have a query which at first launch returns zero rows: <select id="getParams" parameterType="map" resultMap="ParamsResultMap"> SELECT ID, GROUPS, TEMPLATES, DATE_FROM, DATE_TO FROM MY_FILTERS WHERE ID = #{id} </select> <resultMap id="ParamsResultMap" type="my.domain.ParamsVO"> <result column="ID" property="id"/> <result column="GROUPS" property="groups"/> <result column="TEMPLATES" property="templates"/> <result column="DATE_FROM" property="dateFrom" jdbcType="TIMESTAMP"/> <result...

Identifier is too long

sql,oracle
Please help me to find why am getting identifier is too long error , even after having enough space for role in record. Exception : Error report - ORA-06550: line 14, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long ORA-06550: line 18, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long 06550. 00000...

Oracle: unique index with check on a specific value

sql,oracle,constraints
I have a simplified table with two columns (group, key). A key must be unique within a group, but it also have to be unique compared to a 'reference' group. So I created a unique index (group, key) for the first constraint, but is it possible to create a unique...

Does a nested table retain order while not being stored?

oracle,plsql,nested-table
I have a PLSQL function that populates and returns a nested table: select distinct id bulk collect into my_nested_table from user order by id; return my_nested_table; According to the docs nested tables are multisets and have no inherent ordering. Can I nevertheless assume that the nested table returned from the...

Which tables do not have indexes in Oracle? [closed]

oracle,indexing,sqlplus
I think its wrong question ,but its a university project assignment .. The question : I want to find tables without indexes in Oracle with select statement in SqlPlus. Thanks for helping.....

Return DataTable from ORACLE

oracle,datatable,nothing
I'm trying to get data from Oracle to a Datatable. My idea was to create a function to do all connection staff and then call it from other parts of the code. Like: dt = New DataTable dt = Oracle2table(sql.ToString) Then, I created the function: Function Oracle2table(ByVal sql As String)...

Get unmatched records without using oracle minus except not in

oracle,plsql,inner-join,outer-join
Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

SQL Developer does not connect with SID as defined in tnsnames.ora

oracle,oracle-sqldeveloper
My tnsnames.ora looks like this: ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)...

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...

Calculating Sum of Hierarchical Query, Grouped by Type

sql,database,oracle,hierarchy
I have a table TRANSFERS which contains these columns: account_id, father_account_id, amount, type. This table represents all transfers of money made from the specific account, their amount, and what were they paying for by types (such as food/school/taxes etc). What I need to do, is for every main account (an...

how to navigate in self loop tables?

sql,oracle
Consider the following table create table EMPLOYEE ( empno NUMBER not null, ename VARCHAR2(100), salary NUMBER, hiredate DATE, manager NUMBER ); alter table EMPLOYEE add constraint PK_EMP primary key (EMPNO); alter table EMPLOYEE add constraint FK_MGR foreign key (MANAGER) references EMPLOYEE (EMPNO); which is a self looped table i.e. every...

How to join 2 tables with select and count in single query

sql,oracle,left-join
I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

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...

SQL*Loader Control File Custom Date Format

oracle,toad,sql-loader
I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

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,...

Oracle query to find string not containing characters

regex,oracle,ascii
What is an Oracle query that will return all records where the field mytable.myname contains any characters other than A-Z a-z 0-9 -/\() ...

Get only Oracle function return table's columns and their types

c#,oracle
Is there an oracle query which I can use to get back the details of an oracle function which returns a table, where I'm looking for the info of that table, say the record name, but mostly the columns in that record and their types. Example function: create or replace...

Extracting XML data from CLOB

sql,xml,oracle
How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

PL/SQL Number rows with same ID

sql,oracle
I'm making an extract and want to number rows with the same values to have same row number. Example : TRN Date Product ROW_Number ABC1 2015-06-15 001 1 ABC1 2015-06-15 002 1 ABC1 2015-06-15 003 1 DEF1 2015-06-15 001 2 DEF1 2015-06-15 002 2 The rows with same TRN must...

How do I fix function compilation error?

oracle,function,plsql,sql-update
--this is my function create or replace function sal_incr ( p_grade number) return number is v_inc number; begin select raise_percent into v_inc from sal_inc where grade_id in (select grade_id from emp_task where grade_id = p_grade); return v_inc; end; -- Update statement update emp_task set sal = sal + sal_incr(grade_id); --...

sql script to find index's tablespace_name only

sql,database,oracle
Trying to find the specific tablespace names that were created for indexes only. I don't want to see the tablespaces names that are already used for tables. Something like below, but i couldn't have it working due to syntax error. Can someone fix it? select tablespace_name from dba_indexes where tablespace_name...

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...

Result from pipelined function, always will sorted as “written”, or not?

sql,oracle,plsql,oracle12c
Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

how to pull a column named “NUMBER” from Oracle through Ms Query in Excel

sql,excel,oracle,excel-2010
I know, I know...The column shouldn't be named "NUMBER", but it was here before I was, and I can't change it for now. At the moment, I only have read access to this database, and I was told it would be changed...soonish... I've tried referencing it as Table."NUMBER" and that...

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"; ...

Why does .Where() with a Func parameter executes the query?

c#,oracle,linq,entity-framework
Here's how my DataAccessLayer works : public Foo GetFooBy(Func<Foo, bool> filter) { var query = from item in this.DataService.FooSet select item; var where = query.Where(filter); var first = where.First(); return first; } I assumed the query would be run when First() is called but it's actually executed by the Where()....

How to select only distinct row when there might be duplicates using SQL?

sql,database,oracle
I have a tricky SQL query I could use some help with. I have a phone directory table, that was not designed very well. It has name, phone number, job description and primary_job_indicator. However, the primary_job_indicator isn't doing it's job. Not everyone has a primary_job. Here's some sample data: fname...

Oracle SQL PreparedStatement setdate

java,sql,oracle,date
I'm pulling my hair off, I can't find good solution for it, I've searched some other topics, but still no ideas. I have problem with PreparedStatement, especially with setting date. I know, that oracle Date needs date in format YYYY/MM/DD and java.sql.date in YYYY-MM-DD but any of these schemes don't...

Oracle NVL function not allows second parameter as datetime

oracle,datetime,nvl
select nvl(trunc(null),trunc(sysdate)) from dual; While executing above query i am getting following error ORA-00932: inconsistent datatypes: expected NUMBER got DATE look like when i take string or number instead of trunc(sysdate) it run fine....

Using MyBatis Update with foreach

sql,oracle,mybatis
i am struggling with update statement in MyBatis. I want to put multiple strings in one row: UPDATE MY_FILTERS SET GROUPS = <foreach item="item" collection="selectedGroups" open="" separator="," close=""> #{item} </foreach>, TEMPLATES = <foreach item="item" collection="selectedTemplates" open="" separator="," close=""> #{item} </foreach> where ID = #{id} I've tried the following alternatives: open...

Getting a missing keyword error in CASE WHEN statement

sql,oracle,plsql
I'm getting a missing keyword error in Oracle SQL and I'm not sure why. Here's my code. I want to have a column split by different times depending on the variable P1_DATE_CHOOSER. (I'm using Apex App Developer if that helps). Select START_DATE, Round(Avg(Run_TIME), 3) as "Average_RunTime", Round(Max(Run_TIME), 3) as "Max_RunTime",...

Oracle SQL: How do I convert the substrings in a string using to_char

oracle,to-char,regexp-replace
The to_char works fine when applied to single match like below. select to_char('54523234', '99,999,999,999') I use regex to find the match of only the currencies in the string. But when I apply to_char to the first group, it doesn't work SELECT regexp_replace ('24444.88, 54523234.78, and 1044.52 are numbers in this...

Find any character occur more than 4 times

sql,regex,oracle
I want to find any character occurs between 4 and 10 times, I used REGEXP_LIKE but it's valid just for one character 'a' , I want to find for all alphabet: SELECT regex_test_name FROM regex_test WHERE REGEXP_LIKE(regex_test_name, 'a{4,10}') ...

PL/SQL stored procedure which outputs values depending on user input value

oracle,stored-procedures,plsql
I have an Oracle table (sample): +----------------------------+----------------+-----------------------+ | DEPT | BUILDINGID | GEOMETRY | +----------------------------+----------------------------------------+ | Emergency Services AMB | 516 | [MDSYS.SDO_GEOMETRY] | | Emergency Services AMB | 287 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 283 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 460 | [MDSYS.SDO_GEOMETRY]...

Remove leading zeros from Oracle date

sql,oracle,date
I want to output the current date in the format: 1-1-2015 So without the zero's in this example: SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY') AS today FROM dual Output: 17-06-2015 ...

Optimizer using an index not present in the current schema

oracle,indexing,optimizer
CONNECT alll/all SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50; Execution Plan Plan hash value: 2056577954 | Id | Operation | Name | Rows | Bytes | | 0 | SELECT STATEMENT | | 25 | 200 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES...

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...

CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)

sql,oracle,plsql,blob,clob
I've got stored plsql procedure, that takes big text from file create or replace procedure dbst_load_a_file( p_file_name in varchar2, l_clob out clob ) as l_bfile bfile; dst_offset number := 1 ; src_offset number := 1 ; lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX; warning number; begin l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name ); dbms_lob.fileopen(...

SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?

sql,oracle,select,substring
If I had this: NAME EYES==ID==HAIR Jon Brown==F9182==Red May Blue==F10100==Brown Bill Hazel/Green==F123==Brown ...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how would I select a substring from the compact...

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...