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).
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.
I have a date field in my form (date.start_time) in which I want to reference in a library and add 5 mins to the time (since its in the lib I am coding this I need to use name_in ). My current code is:
Here is a sample function which takes in a string of CSV fields and prints the third field :
create or replace function restr(istr varchar2) return INTEGER is var_1 varchar2(30); begin dbms_output.put_line('input:'||istr); select regexp_substr(',' || istr,1,3,null,1) into var_1 from dual;
[Code]..
When I pass an input string as :
JOHN,MARY,O'DONNEL,O'CONNELLY,MARK
with quotes/apostrophe in it to the function then it prints the input string in the first dbms_output but errors out at the select with ORA-01760.
how we can use the quote/apostrophe character here ?
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual; 'THISISN'' ---------- This isn't SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual; TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''') --------------------------------------------------------------------- 34456.789 SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual; TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY') ------------------------------------------------------ Today's 16th August 2013
I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work: 'val1','val2' ''val1','val2'' ''val1'',''val2'' '''val1'',''val2''' ''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
Create Table A ( a number, CreationDate DATE) PARTITION BY RANGE (CreationDate) ( Partition p_03122012 VALUES LESS THAN (TIMESTAMP' 2012-12-04 00:00:00'),
[Code]...
NOTE: Partition are named as p_ddmmyyyy where ddmmyyyy is date.
select * from dba_tab_partitions where table_name = 'A'
Now my requirement is :
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'p_ddmmyyyy' (Here I want ddmmyyyy to be from sysdate i.e. date we get from - Select to_char(sysdate,'ddmmyyyy') from dual i.e. for today it becomes select * from dba_tab_partitions where table_name = 'A' and partition_name > 'P_04122012'
I am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data BADFILE '/var/opt/app/bad/filename' DISCARDFILE '/var/opt/app/discard/filename' append into table source_file FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456' order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE from TBL1, TBL2 where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456' order by Material
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT LOAD DATA INFILE * INTO TABLE TMPTLI_LAWSON_ITM_MST TRUNCATE FIELDS TERMINATED BY "|" (ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD", AVL_CD , ITM_CST_AMT, ITM_SLL_AMT, EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)", PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD", ITM_CMNT "TRIM(:ITM_CMNT)")
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
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
Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc: ORA-01114: IO error writing block to file 1030 (block # 602122) ORA-27063: number of bytes read/written is incorrect IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: -1 Additional information: 180224
But this file_id i don't have in my database, i am making these queries:
SQL> select FILE_ID from dba_temp_files order by FILE_ID;
FILE_ID ---------- 1 2 3 4 5 6 7 8 9 [code]....
I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.
i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.
