SQL & PL/SQL :: Double Quotes In Column Name Can Be Replaced With Alternate Character?
Feb 14, 2012
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
View -1 Replies
ADVERTISEMENT
Dec 4, 2011
declare
v_a varchar2(2000) := 'abcd';
v_e varchar2(2000) := '6666';
v_d varchar2(2000) := 'example';
v_final varchar2(4000);
begin
v_final := '"v_a"'||'''|'''||'"v_e"'||'''|'''||'"v_d"';
-- v_final := '"v_a"';
dbms_output.put_line('v_final: '||v_final);
end;
/
above gives me :
v_final: "v_a"'|'"v_e"'|'"v_d"
so it is printing the variable names, But I want to see values, like this: "abcd"|"6666"|"example"
View 6 Replies
View Related
Aug 22, 2013
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.
View 3 Replies
View Related
Oct 29, 2012
I want to remove more than one space from a string by excluding double quotes.
For example:
I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")
O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
View 17 Replies
View Related
Aug 25, 2010
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)")
View 4 Replies
View Related
Apr 29, 2010
I have oracle 10g database running on Sun Solaris. My database character set is AL32UTF8. Our user wants to store a double byte character and an image, I want to know what the column type should be using for these.
View 2 Replies
View Related
Oct 27, 2010
We have a table with an nvarchar column that we use to store product descriptions.
This field is maintained on a web form created with the htp package.
Whenever a special character is used (ie, the copyright symbol, trademark, etc), the value is stored correctly in the DB. I've verified this because if i query the value in sql developer, i do see the correct symbol.
However, pulling this piece of data out to display in a formtext box (for edit) replaces all the special characters with ?'s.
Our National Character Set value is set to UTF-8.
Is this being caused by our database character set? or is it a web design problem that i'm just not aware of.
I can post code snippets if this isn't enough info...but I was hoping it'd be fairly simple.
View 3 Replies
View Related
Sep 26, 2013
how do we know database character set is either single character set or multi character set?
While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".
Below are steps taken to resolve the issue -
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
i got this error: ORA-12712: new character set must be a superset of old character set
below are the commands executed by me:
SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;
And its working...
I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?
View 11 Replies
View Related
Mar 5, 2010
There is a table like below
seq no. number(6)
amount varchar2(20,2)
The values in the table are:
Rec1
123456
100.00
Rec2
123457
99.01
Rec3
123458
9a.01
Rec4
123459
NULL
Now I want to summarize the amount column replacing non numeric and null values to '0'(zero's).
how to frame query for this.
View 2 Replies
View Related
Aug 8, 2012
I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE
Table values are for e.g
STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA
Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.
View 6 Replies
View Related
Oct 17, 2006
how to retrive alternate records from the table in the answer table the first row wil be in upper case and the second row wil be in lower case ...like wise wat is the querry
View 1 Replies
View Related
Mar 15, 2013
Any alternate to the following query.
select * from emp where ename like upper(NVL('%mi%',ename));
Basically I want to search based on string or null.
View 11 Replies
View Related
Sep 23, 2010
Oracle database version is : 10G.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
get this issue resolved.
View 7 Replies
View Related
Feb 11, 2011
Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.
View 8 Replies
View Related
Jul 23, 2013
One of our client is using Oracle DB 10.2 with customize applications. They have 5 schemas (one for each module). In their Head Office they are utilizing 4 schemas and remaining one is being utilized on company site based in another city. Due to internet connectivity issue they don't have VPN available so they export schema dump file (imp/exp) on daily basis from that server, transfer it using FTP then import it on Production Server (in Head Office) on daily basis.
Now they are looking for an alternate because the schema size is getting larger day by day and due to internet connectivity issue they face lots of problems while transferring the file.
View 4 Replies
View Related
May 17, 2009
I have a Problem regarding Data Replication, I am using Standard Version of Oracle 9i,I am having 5 remote servers , I am replicating the data from all of these servers using Materialized views on a Centralized server , I have created 5 schema for them, there is a application which is Consolidating the Data from all the locations.
When I create materialized views from these Materialized views I am facing problems to Fast refresh them as I am using "Union All" to combine data of all Users (Error is ora -12004) . I would like to know that is there any other way to do this, also I have tried all the ways to solve the problem of Ora-12004.
View 1 Replies
View Related
Feb 19, 2011
Am tring to export a data from oracle forms to excel. I use ole2 option. it is working perfectly in my desktop. But when i move the same to our application server, it is not working. I belive OLE is related to Microsoft office and our server is unix based.
View 1 Replies
View Related
Jan 27, 2012
When we use the AUTOTRACE / EXPLAIN PLAN we can see the (estimated) best execution plan the Optimizer found for our SQL Command. Is there a way to display all alternate execution plans the Optimizer has considered ?
View 1 Replies
View Related
Nov 11, 2012
The Item data for individual cycles is as below.
Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A
The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.
In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.
Below is the code which I have written which considers the rundate as Input parameter.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
[code]....
In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.
Is there any way to query above only once and get the required results using Lag/Lead functions etc.
View 5 Replies
View Related
Oct 12, 2011
I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
View 6 Replies
View Related
Dec 14, 2010
I am reading Section 4-8 (page 42/216) in the Oracle Database 2-day Developer Guide from here:
[Code]....
It reads:
Suppose that you want to select every employee whose last name has a double vowel(two adjacent occurrences of the same vowel).
Example 4.9 shows how you can do this.
The regular expression ([AEIOU]) represents any vowel. The metacharacter 1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.
Example 4.9 Selecting All Employees Whose Last Names Have Double Vowels
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])1', 'i');
Result is similar to:
FIRST_NAME LAST_NAME
-------------------- -------------------------
Harrison Bloom
Lex De Haan
Kevin Feeney
Ki Gee
Nancy Greenberg
Danielle Greene
Alexander Khoo
David Lee
8 rows selected.
I don't understand how [AEIOU])1 would find a double vowel(two adjacent occurrences of the same vowel).
View 6 Replies
View Related
Aug 20, 2012
i'm trying to read a text file into an oracel table. I'm having issues with this statement FIELDS TERMINATED BY ' ' fields are separated by 2 spaces. How ever Oracle is not recognizing ' ' or " " as double space. It is treating as one space thus shifting all coulmns.
How can I represent double space to Oracle ?
View 12 Replies
View Related
Nov 10, 2010
i have table contains a column of var char type i want to insert a value
'1 mmTHICK GI SHEET 4' X 8' X 1MM THICH' in to the coulmn but m getting error
I tried set scan off but its not worrking for the below query.
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
my query is
Insert into Inventory select
'N1280000015',
'1 mmTHICK GI SHEET 4''' X 8''' X 1MM THICH',
[code]....
View 8 Replies
View Related
Aug 2, 2012
I have a situation where I want to configure primary database (11.2) with 2 remote destinations. dest_2 is the default and points to a standby on host_2. However, I also want the primary db to continue transporting redo to dest_3 on node_3 when node_2 is taken down (planned or unplanned).
1) Configure the ALTERNATE attribute of dest_2 to point to dest_3.
2) Configure tnsnames client-side failover on primary host to point to 2 nodes (node_2 and node_3).
View 1 Replies
View Related
Nov 18, 2011
how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.
Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.
ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,
View 5 Replies
View Related
Feb 9, 2011
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 ?
View 6 Replies
View Related
Dec 4, 2012
I have a string: 'VOLT,AGE'..
How can I convert this string to: 'VOLT','AGE' using REGEXP_REPLACE...
I am having trouble escaping the single quotes in my query
View 6 Replies
View Related
Aug 16, 2013
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
View 12 Replies
View Related
Dec 6, 2010
I need to write a procedure to be able to reuse it to clean up subcategory sequence. Here is a problem: two tables: Category and subcategory:
create table Category
(category_id number,
Name varchar2(20))
/
create table Sub_Category
(sub_category_id number,
category_id number,
sequence number)
/
In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.
So ideal world is :
Insert into Category values (123 ,'Category1');
Insert into Category values (234 ,'Category2');
Insert into Category values (345 ,'Category3');
Insert into Category values (456 ,'Category4');
Insert into Category values (567 ,'Category5');
Insert into Sub_Category values (1,123,1);
Insert into Sub_Category values (2,123,2);
Insert into Sub_Category values (3,123,3);
But I've also bad rows like this:
Insert into Sub_Category values (4,234,1);
Insert into Sub_Category values (5,234,1);
Insert into Sub_Category values (6,234,1);
Insert into Sub_Category values (7,345,1);
Insert into Sub_Category values (8,345,1);
[code].....
Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.
I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:
CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
IS
CURSOR c1
IS
select category_ID from category where ...;
BEGIN
FOR a IN c1 LOOP
UPDATE_SUBCATEGORY(a.Category_id);
COMMIT;
END LOOP;
END;
/
And the actual procedure updating subcategory:
CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
BEGIN
............
END;
/
write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?
View 17 Replies
View Related
Jun 9, 2010
I support old oracle code and we currently print using the set report object property.
I recently was requested to modify all of our printing to two-sided printing from the current one page per sheet.
I am trying to figure out if there is a parameter I can set, i.e.REPORT_OTHER or something else that would allow me to do this.
View 4 Replies
View Related