PL/SQL :: ORA-00917 - Missing Comma?

Dec 18, 2012

I am trying to insert data into a table which I created but I keep getting the error: missing comma.

INSERT INTO EMPLOYEE_DATA

(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT, SALARY_(£), SALARY_PAIDON)

VALUES
('1', 'David', 'Smith', 'IT', '20,000', '3/11/2012')

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-00917 - Missing Comma Error

Mar 5, 2013

SELECT XMLELEMENT (DECODE ('RF', 'RF', 'Credit', 'Sale'), xmlattributes ('01234567' AS "id") ) AS Order FROM DUAL

when I run the above SQL I'm getting "ORA-00917-missing comma" error.

View 6 Replies View Related

Server Utilities :: Using Decode In SQL*Loader / ORA-00917 / Missing Comma

Mar 13, 2010

For some reason, sql loader is raising an error for this line

CNT_CODECHAR "decode(:CNT_CODE,'GB',:CNT_CODE,RPAD(:CNT_CODE,3)",

Basically what i want to do is, if the country code is 'GB' then dont change anything but if its not append a space to the country code and return a 3 character value.

The error that is generated is

Record 3: Rejected - Error on table WORLD_COUNTRIES.
ORA-00917: missing comma

There is nothing wrong with the source data because i have tested these different combinations both work fine

CNT_CODECHAR "RPAD(:CNT_CODE,3)",
CNT_CODECHAR,

its only when i use the DECODE function that it complains.

View 3 Replies View Related

SQL & PL/SQL :: Replace Comma With '�10'?

Jan 23, 2012

TestCase

DROP TABLE test ;
CREATE TABLE test (id NUMBER, cnt_str VARCHAR2(200));
INSERT INTO test (id, cnt_str) VALUES
(1,'AKRN000002,1451-1473,00000A,74,AKRN000002,1475-14791000000A,8010AKRN000002,1481-1492,00000A,9310AKRN000002,1494-1500')

[code]...

The requirement is in each of the string where there is comma after the number and the number is prefixed by "-" character,
the comma after the number should be replaced by '10'.

For example in the second record where ID = 2,
CNT_STR is '00000B,1-251000000D,26-32,ADTW000301,2858-2875'.
In this string -32, should become -3210 and resulting string should be '00000B,1-251000000D,26-3210ADTW000301,2858-2875'

Expected Result.

ID CNT_STR
-------- -------------------------------------
1 AKRN000002,1451-14731000000A,7410AKRN000002,1475-14791000000A,8010AKRN000002,1481-14921000000A,9310AKRN000002,1494-1500
2 00000B,1-251000000D,26-3210ADTW000301,2858-2875
3 AKRN000001,1126-12001000000B,501-525

View 4 Replies View Related

SQL & PL/SQL :: Comma Appearing In Place Of Dot

Jun 20, 2012

When I try to print a decimal value , I get the dot replaced with a comma.

SQL> select 23.45 from dual;

23.45
----------
23,45

Is it because of any particular sqlplus set command or OS issue?

View 3 Replies View Related

SQL & PL/SQL :: Get Comma Separated Values

Oct 19, 2012

Create table a ( Objectid number, Value varchar2(2000);
/

Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');

I should get the output as:

ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11

View 6 Replies View Related

Comma Separated Values In Parameter?

Jul 2, 2008

Here is what i have in ms-sql, how to convert this into t-sql ?

@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0

[Code].....

View 2 Replies View Related

Parsing Comma-separated String?

Jan 22, 2009

I have a string value like -- a,,b,c,d,e,f

Using just sql, I want to put each value of the above string in a different row. So the output should be --

a
b
c
d
e
f

using procedures it would not be that great but I want to do it just using queries.

View 1 Replies View Related

SQL & PL/SQL :: Range And Comma Separated Queries

May 30, 2013

I have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:

<pnum> 1-10, 14, 17, 11, 21-24 </pnum>

I can muster a basic SQL statement to query for this by hand (more then one way to do this)-

SELECT *
FROM part_table
WHERE pnum BETWEEN '1' AND '10'
OR pnum BETWEEN '21' AND '24'
OR pnum IN (14, 17, 11);

is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?

View 1 Replies View Related

SQL & PL/SQL :: Comma Separated View Results

Jun 19, 2010

We have a existing view which pulls list of orders against a deal. Straight forward result of the view is below.

Deal idSALES_ORDER_NUMBER
280760548460578
280760548789518
280760548794798
280760548794799
280760548873291
280760548887725
280760548900581

Each deal may have different number of orders.

We need to modify the view logic to result the list as comma seperated:

48460578,48789518,48794798,48794799,48873291,48887725,48900581

how to proceed.

View 2 Replies View Related

SQL & PL/SQL :: Processing Comma Separated Strings

Mar 3, 2003

Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?

View 4 Replies View Related

SQL & PL/SQL :: Insert Comma After 3rd Position In String Value?

Jul 29, 2013

I have to convert string 1234567 as 123,456,7 .

note 1234567 is a string.

View 6 Replies View Related

SQL & PL/SQL :: Separate String Based On Comma?

Oct 28, 2013

This I want TO separate TO different COLUMNS based ON comma.

THE RULE IS LIKE out OF total five fields FIRST 3 comma will be FIRST 3 addresses AND rest will be address4 AND LAST NUMBER should appear IN pincode field IF found.

The trouble is for reading reverse to get the number.

WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
SELECT 'The Icon, 2nd 3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT addr1 FROM address;

View 10 Replies View Related

SQL & PL/SQL :: Concatenate A Comma But Not Single Quotes

Oct 8, 2010

In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '

Col_A
______

123
124

select '|| col_A ||'||','
from
Table_A;

The output should be
'123',
'124'

I can concatenate a comma but not single quotes.

View 3 Replies View Related

PL/SQL :: Rows Into Comma Separated Values

Oct 30, 2012

I would like get rows into comma separated values expected output

rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:

create table test1 (name1 varchar2(10));

insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;

SQL> select * from test1;

NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO

View 3 Replies View Related

PL/SQL :: Strip Comma From A Numeric Field

Aug 20, 2012

providing sql statement to strip commas from a number/numeric field in the BI repository logical column? I do not want to use cast function to convert to character because we need to keep that field as numeric. I could only find string expressions to remove commas.

View 12 Replies View Related

SQL & PL/SQL :: To Split The Rows By Comma Separated

Mar 4, 2011

SELECT 'TEST','F1,F2,F3,F4' from dual

I want to split the rows by comma separated as below

TEST F1
TEST F2
TEST F3
TEST F4

View 5 Replies View Related

PL/SQL :: Split Comma Separated String

Mar 11, 2013

I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1

View 3 Replies View Related

PL/SQL :: Comma Separated String In Rows

Oct 23, 2013

I have one table   select * from abcd;

No  err-----------------------------1    rishi,rahul2    rishi,ak I want output like: 

No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this: 

select  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null  but this query is giving me output: 

1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct  no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);

View 4 Replies View Related

SQL & PL/SQL :: Sort A Comma Separated String?

Aug 4, 2008

I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.

I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.

CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';

[code]...

View 26 Replies View Related

Formatting Number With Comma Separation / TO_CHAR

Mar 1, 2012

how a number can be displayed using comma separation without using TO_CHAR function.

NUMBER 3455678.05 should be displayed as 3,455,678.05 NUMBER 3455678 should be displayed as 3,455,678

View 1 Replies View Related

Converting Comma Separated List To Table

Dec 7, 2011

I have a comma separated list 'black','red','white' and I want to get each of words in this list in one rows. Some time ago I done it with:

QUOTE select * from XXX('black','red','white')

where XXX was a function which converted list to table.

View 1 Replies View Related

Server Utilities :: SQL Loader Comma Delimiter

Dec 22, 2011

I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below

descri,dfdfdfd,dfdfdf,
sdfsdf,
dfsdfd,

i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns

View 6 Replies View Related

Forms :: Why Arabic Comma Reverse Text

Mar 2, 2012

The Text is displayed correctly when there is no "Arabic comma" ، , otherwise the text will be reversed, How can I avoid the issue, why we have such problem only when we add the comma. The screenshot: s10.postimage.org/8qw1l8621/Arabic_comma.png

View 3 Replies View Related

SQL & PL/SQL :: Enforce Comma Separated Column Values?

May 21, 2012

I have a requirement wherein I need to enforce certain column to have only comma as a delimiter, where multiple values exists.

Example:

Create table:

create table test_oz ( slot number, server_name varchar2(50), used_by varchar2(50),
constraint test_oz_pk primary key (slot, server_name) );

Insert Test Data:

insert into test_oz values ( 1,'SRV1','SAMMY' );
insert into test_oz values ( 2,'SRV1','SAMMY,TOM' );
commit;

Data:

SQL> select * from test_oz;
SLOT SERVER_NAM USED_BY
---------- ---------- ----------
1 SRV1 SAMMY
2 SRV1 SAMMY,TOM

From above, the USED_BY column data need to be only comma separated. Is there a way to enforce that?

When user tries to insert data using any other delimiter, it should fail.

I was trying to see if a CHECK CONSTRAINT could be of use, but could not find it to work.

View 3 Replies View Related

SQL & PL/SQL :: Convert ID And Comma Delimited List To Rows

May 31, 2011

how can I convert

select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual

to

select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual

?

I would prefer sql then plsql. Script for creating a test table:

create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');

View 3 Replies View Related

SQL & PL/SQL :: Multiple Search Terms Will Be Separated By A Comma

Dec 12, 2011

I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.

1.) The search interface must have a single box, like google.

2.) Multiple search terms will be separated by a comma.

3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email

4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)

5.) Each search term will be checked against various columns.

6.) Search terms must not have a preference in order. Name, Address = Address, Name

7.) Records will be returned only for the rows where all search terms are found.

View 3 Replies View Related

Application Express :: Comma When Passing Values In URL

Jun 20, 2012

I have two page , from first page to second page I am passing some values using url parameter passing but some values contains comma ex :- P1_ NATION  text field contains INDIA,USA,UK

but apex treated that as these are separate values and assign it to separate items

suppose my intention is like P2_NATION,P2_EMPLOYEE,P2_EMPID :INDIA,USA,UK,SAGAR ,123

but apex treating it as P2_NATION = INDIA :P2_EMPLOYEE=USA :P2_EMPID = UK

I am using apex4.1 , db 11g , ie , chrome , ff.

View 2 Replies View Related

SQL & PL/SQL :: Adding Quotes Around Field Only If Comma Exists Within

Mar 25, 2010

I searched the forum but could not find an answer. I am creating an extract via SPOOL that will send the output to a CSV file. I am comfortable concatenating commas in between the fields, the problem lies in text fields such as last_name which may contain a comma. The requirements of my client state to put double quotes around text fields ONLY if they contain a comma (so as not to throw off the CSV file obviously).

The only thing I could think of was a nested DECODE checked every character of every text field for a , and if it finds one to put " and if not to put nothing. This would get very tedious to program it as there are many text fields and they can be very long. I may even run out of nested DECODE statements (I forget what the limit is now).

View 9 Replies View Related

SQL & PL/SQL :: How To Escape Comma While Exporting Data From Table Into CSV File

Apr 9, 2012

How we can escape comma while exporting data from table into csv file.

CREATE TABLE emp
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
address varchar2(100),
[code].......

i have to export data from emp table which has address column and address column contain comma, when i am running below script, the comma part in address field comes in next tab in csv file, is there any way we can avoid shifting to next tab and can have complete address in one tab.

set echo off
set verify off
set termout on
set heading off
set pages 50000
[code]....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved