oracle10g , How to fetch a part of string upto a chracter?

How to fetch a part of string upto a chracter?


Tag: oracle10g

I want to fetch the names of employees from a table upto the character ':' but couldn't as substr and ltrim is not working as expected. Below given are given some examples:


But I want them in the way given below:


I just used the query below in Toad for Oracle:

<b>select name from employees</b>


Please try below query:

select SUBSTR(name,(INSTR(name,':')+1)) || ' ' || SUBSTR(name,1,(INSTR(name,':'))-1) from employees;

hope above query will resolve your issue.


How to spool cells to different files?

Is it possible to spool the content of the different rows of a table to different files? The Number of rows of my table is not fixed and it has these columns: ID, NAME, CONTENT_TO_SPOOL I would like to create an output file per row, spooling the CONTENT_TO_SPOOL to files...

Oracle SQL: Use outer loop identifiers in inner loop in execute immediate

I have to execute 32 times a very similar operation, that is setting the value of a column in a row for a given record (for a given quarter). To simplify my code and thrive for beauty, I wanted to use a for loop with an execute immediate, using I_cnt...

ORA 12154 - unable to connect from toad/SQLplus

when i try to connect any DataBase in my TNSNAMES.ora file SQLPLUS throws ORA-12154: TNS could not resolve service name I've already checked this stuff: TNS_ADMIN environment variable is defined as "C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN" "C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora" was located The following connect descriptor was found for my_db in the tnsnames.ora file my_db= (DESCRIPTION= (ADDRESS=...

Comma seperated column without XML path

I have table like below ID Name 1 a 1 b 1 c 2 d 2 e 3 f I would like to get result as ID Name 1 a,b,c 2 d,e 3 f I don't want to use any XMLPATH or coalesce functions. Just in simple SQL query I...

Oracle 10g users are expired

I have an Oracle 10g database which is connected to another server with instance client. Problem is all of users(SYS/SYSTEM/SYSMAN/FATASAN/JAVAHER) are expired (I forgot to change expire date) and also sqlpus and instance client are faced with ORA-12560 error (Both servers were good but now I have no connection to...

Why does regexp_replace replaces nothing with something?

I have to update several scripts to use a table from another schema: S1.MYTABLE => S2.MYTABLE2. Also when I replace the name of the table I want to keep the format of the original script. Keeping enter or space after S2.MYTABLE2 if there was one after S1.MYTABLE My code seems...

Large numbers in Oracle

Why this works select 10000000000000000000000000000000000000000*9 from dual --41 digit number Output: 90000000000000000000000000000000000000000 but this gives uncorrect result select 10000000000000000000000000000000000000000+1 from dual --41 digit number Output: 10000000000000000000000000000000000000000 I cant understand why Oracle can multiply a large number by 9 but fail to add 1 to it ! ...

Delete row with confirmation from Oracle table

I have this SQL query which is used to delete users. DELETE FROM USERS WHERE USERNAME = ? The problem is that I don't know is there any row success row removal or not. I always get success at the end. Is there any way to get for example some...

How to know if DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY was set to false for a Trigger?

Is there a way to know if DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY was set to false for a trigger? DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('"S1"','"MY_TRIGGER"',FALSE) ; By default the property is set to TRUE, I need to query if the property was set to false, because when I re-create the trigger I'd need to set it again to false....

DB Index not being called

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

How to check if there exist only one record for a certain Id

How to check if there exist only one record for a certain Id I have two tables called Tbl_Company and Tbl_Employee I am fetching employees as follows- SELECT DISTINCT emp.employee_id FROM Tbl_Company comp , Tbl_Employee emp WHERE emp.company_id = comp.company_id AND emp.company_id = 1234; This query returns exactly one value....

Left Outer Join - ORA-00933: SQL command not properly ended

What is wrong with this query? I just can't get it to execute on a Oracle 11G Database. SELECT PH.ORD_NBR, PH.TEL_NBR, SM.NAME FROM ORD_HDR PH LEFT_OUTER JOIN STORE SM ON SM.COUNTY='1' AND SM.STORE_ID=PH.STORE_ID WHERE PH.ORD_NBR='777894801' AND ROWNUM<=1 I get this errror ORA-00933: SQL command not properly ended...

ORA-1691: unable to extend lobsegment

I'm getting this error: ORA-1691: unable to extend lobsegment ABC.SYS_LOB0014859757C00018$$ by 1280 in tablespace ABC The tablespace is build like the folowing: CREATE TABLESPACE "ABC" DATAFILE '/ora/db/user/abc1.db' SIZE 4194304000, '/ora/db/user/abc2.db' SIZE 4194304000, '/ora/db/user/abc3.db' SIZE 4194304000, '/ora/db/user/abc4.db' SIZE 4194304000 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 SEGMENT...

Oracle: do Truncates maintain Atomicity within a transaction?

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

XML to String works propertly on one database, but not on second

I have strange situation - I have java stored procedure to generate XML, and to store it in String. Generation XML to Document works, but problem occurs when I try to convert it to String. This StringWriter sw = new StringWriter(); StreamResult result = new StreamResult(sw); DOMSource source = new...

Researching the newest enties

I need to check the newest entries and the size of it in the whole tablespace. Te used db is oracle 10g. I'm facing the problem, that the data is growing very fast. I need to monitor. How can I do that?

Dynamic Select query In Oracle Stored Procedure

I want to write a stored procedure in Oracle passing dynamic value as below: create or replace PROCEDURE DETAILDISCDATA2 ( USERTYPE IN VARCHAR2, BID IN NUMBER, SORT_COLUMN IN VARCHAR2, SORT_ORDER IN VARCHAR2, catCur OUT SYS_REFCURSOR ) AS COUNTRY_CODE VARCHAR2(10) := 'USA'; v_sql varchar2(1000); BEGIN v_sql := 'SELECT bd.*, BD.ATS_ITEM_LIST_PRICE_UNIT as...

Reading XML element occurrences using PL SQL

I have this XML. <a> <b>b1</b> <c>c1</c> <b>b2</b> <c>c2</c2> </a> I want to be able to extract the values of elements 'b' and 'c' using PL\SQL. I'm using Oracle 10g. So far I have this, SELECT XML.b , XML.c FROM XMLTable ( '/a' PASSING p_xml COLUMNS b VARCHAR(2) PATH 'b/.'...

importing a csv file into oracle database using java

I am getting the following error Java.lang.ArrayIndexOutOfBoundsException: 0 at oracle.jdbc.driver.OracleSql.main( I am using eclipse indigo and oracle can i fix this problem.can anyone help me ? i want to load a csv file into oracle database.. i've created a table named zt with three columns (id, s_date, data) CSV...

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

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 Sql Query : How Pivot Function Sum() in Packages?


sapinst giving database connection error

I am trying to install SAP R-3 ABAP, where databse instance is on one machine and central instance on different machine. Database instance installation is finished but when trying to install central instance when sapinst tries to connect from Central Instance to database instance following error comes up WARNING 2009-05-02...

Get separate count for each condition within group

I am trying to get a view of table information from and Oracle 10g table that lists the counts of specific values of a column in their own columns with each row being the group value. for example: The first select is : SELECT processed_by, count(priority) as P2 FROM agreement_activity...

How to select between two dates without using year to get birthday

select * from tbl_contact e where e.dob between to_date('26-APR','DD-MON') and to_date('03-MAY','DD-MON') This Works !...

Pl/Sql open cursor problems

I have code like, create or replace function ... return types.cursortype as l_cursor types.cursorType; begin open l_cursor for select ... ; return l_cursor; end; this code is used to return hundreds of records daily. Will these open cursors cause any memory leak issue ? Is there any way to close...

Select distinct lines by a field

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

FRM-40403: A calling form has unapplied change. Save not allowed

Im working with oracle 10g , when i try to submit my form the following error FRM-40403 says that A calling form has unapplied change. Save not allowed I search on internet about that error mesagge but i could not find a clear answer, Could someone tell me what is...

Oracle Add Date Column to Existing Table (default to sysdate new rows only)

Is there a way to add column DATE_CREATED such as only new rows will pickup the default sysdate? When I ran the ALTER below, all priors rows got the DATE_CREATED set to the run-time of the ALTER script; I would prefer them to remain null. alter table abc.mytable add (DATE_CREATED...

Oracle date between two dates

I'm looking for rows where Date_Created is greater than 15 minutes ago, back to 4 days ago This works: where h.Date_Created < (Sysdate - interval '15' minute) and h.Date_Created > (Sysdate - 4) This also works: where h.Date_Created <= (Sysdate - interval '15' minute) and h.Date_Created >= (Sysdate - 4);...

How to_date function with sysdate

select TO_CHAR(sysdate, 'YEAR')-365 FROM DUAL; I get error 'invalide number' ...

Query to get number of % sign = length of string in the next row in Oracle 10g

is there any SQL query in Oracle10G which can give the desired output as given required in below sample. Query should print the name first and in the second row it should print the "%" equal in number with the length of the string. Could you please help? Below is...

Oracle SQL 10g query for creating comma separated records

I am writing an oracle 10g query for creating jasper reports. This is the query - SELECT essay_id, LTRIM ( MAX (SYS_CONNECT_BY_PATH (full_name, ',')) KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS full_name FROM (SELECT essay_id, full_name, ROW_NUMBER () OVER (PARTITION BY essay_id ORDER BY full_name) AS curr, ROW_NUMBER ()...

OCI8 doesn't enable on windows 8 + php 5.2.5

Previously I connected Oci 8 on the same machine without using instantclient. I just installed xampp-win32-1.6.6a and edited the apache/php.ini. (Not sure) After installing VS2013, my php_info() doesn't show oci8 enabled. If possible, I want to connect without 'instantclient'. I have already uninstalled VS 2013....

Raw array lenght

I have the following piece of code: set serveroutput on declare datos raw(100); begin dbms_output.put_line('The raw variable has a lenght of '||length(datos)); end; / why it doesn't work? It should give me that the length is 100....

How to fetch a part of string upto a chracter?

I want to fetch the names of employees from a table upto the character ':' but couldn't as substr and ltrim is not working as expected. Below given are given some examples: ABINERI:REBECCA C CARRINGTON:JAMES M But I want them in the way given below: REBECCA C ABINERI JAMES M...

How can i get data on 1 hour basis

date_time Data Status 2015-04-15 17:40:20.0 18.5786 op 2015-04-15 16:20:59.0 16.7868 do 2015-04-15 16:20:55.0 24.58903 No 2015-04-14 16:21:00.0 24.289028 ok 2015-03-15 16:20:59.0 16.51689 dok 2015-04-16 16:20:55.0 24.789028 Nto 2015-02-15 17:55:59.0 28.784145 doq 2015-02-16 17:45:59.0 28.701414 dq 2015-04-18 17:15:59.0 25.784145 q 2015-04-10 15:15:19.0 21.784145 qq Three coloumns are date_time,data and status and...

How to select ranges in a range of record in oracle

If I have a table like this Number Status ------ ------ 1 A 2 A 3 A 4 U 5 U 6 A 7 U 8 U 9 A 10 A What query can I use to group the range into ranges where Status = A? Range Count Status -----...

how to use like clause for a value saperated with comma in oracle

In my Database I have a column where the value is separated by a comma (firstname,lastname). I am trying to search this entire field in where clause using like condition. I have tired this. SELECT fullname from users where ( upper( fullname ) like upper('abc, xyz')) ...

Find all child nodes for any parent node in Oracle 10g

My table structure is: COMPANY_ID, ID, PARENT_ID Here is some part from this table: | COMPANY_ID | ID | PARENT_DID | |------------|-------------|-------------| | 12 | 1 | null | | 12 | 3 | 1 | | 12 | 2 | 1 | | 12 | 4 | 3 |...

Sub Sorting in Oracle

I have a demographics database where I have a list of population numbers per continent, country, state and city. I have now added a rank column to this table which I need to update. However the ranking needs to happen at a state level. So there will be a rank...

Why I can't compare dates?

I have this simple query: select, h.time from main_data sm INNER JOIN TA t on = AND t.first=1 LEFT OUTER JOIN History h on 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...

VBA/Excel: Not accepting SQL subclauses

I have an Excel/VBA spreadsheet connecting to an Oracle database via ADODB. The connection works fine and ordinary queries run fine. But I'm trying to be a little bit clever and avoid running multiple select statements by using a with as clause. This link basically explains what I'm trying to...

Improving my query efficiency - reducing full table scans?

I was wondering if you could help me out I currently have a working query, but I feel it is not as efficient as it could be. Ill go on to explain: I have a car table and a carEvent table. Car table just stores info such as make, model...

How to fetch the list of errors for invalid objects in Oracle 10g

Explanation: I have more than 200 invalid objects in my DB, the reasons could be couple of objects only (others due to dependancy). Is there a way we can select the object name and the 'Error Reason' for it being invalid.

Insert a Sequence for every query execution in Oracle

I have an automated job that runs an sql query. Below is the query. Insert into employee_master (Job_Run_ID, Employee_ID, Employee_Name, Employee_Location) values (runId, eId, eName, eLoc); Let's say this job runs for the first time and it inserts 10 records. Now, All the inserted records should have Job_Run_Id as 10001....

select rowid Oracle

Im trying to display the rowid pseudocolumn, but something strange happened, This is my query select rowid, rowid||'', rowid r from dual output: ROWID||'' R AAAAECAABAAAAgiAAA AAAAECAABAAAAgiAAA So why cant display rowid without alias or not concatenated with something else ? Any explanation ? I used Toad and Oracle 10g....

I am trying to retrieive data from two tables SQL Oracle 10g

I have two tables tbl_inc t and tbl_expn. All fields of two tables are same except its primary key I want to get the amount to display a chart for a month in an year with sum(t.amount) and sum(d.amount) for each day. I have multiple amount in a single day....

oracle external table get selected columns from the file

I'm having problems with oracle external table I have a text file will 50+ unnecessary columns, Now I want to select a few columns from the text file to my external table. create table tmpdc_ticket( SERVICE_ID CHAR(144), SERVICE_TYPE CHAR(50), CUSTOMER_NAME CHAR(200), TELEPHONE_NO CHAR(144), ACCOUNT_NUMBER CHAR(144), FAULT_STATUS CHAR(50), BUSINESS_GROUP CHAR(100) )...

oracle sql loader maximum size of a expression

I have a ctl file to use sqlldr, but the length of the expression is greater than 258 and is the minimum length that i can get in the query is impossible to me create a query with less characters. My ctl is: OPTIONS (PARALLEL=TRUE, SILENT=(HEADER,FEEDBACK), ERRORS=1000000) LOAD DATA INFILE...

Concurrency issue on a database select statement

I have an issue and I'm thinking of using database isolationtype == Serializable for this situation, but after reading a bunch of articles I'm still not convinced that that is the solution for my problem below. Setup: Weblogic cluster > 2 servers Simple Java JDBC Servlets, EJB Session beans 2.0...