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

SQL & PL/SQL :: Show Value Of Variable In Double Quotes?

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

Server Utilities :: Loading String Has Double Quotes In It?

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

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

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

Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table

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

SQL & PL/SQL :: Column Type For Double Byte And Image

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

Forms :: UTF-8 - Special Characters Replaced With ?

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

Server Administration :: How To Know Character Set As Single Character Or Multi Character

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

SQL & PL/SQL :: Sum Of Character Column

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

SQL & PL/SQL :: Special Character Not Allowed In Column Value?

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

How To Get Alternate Records

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

SQL & PL/SQL :: Alternate To Like Operator

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

SQL & PL/SQL :: Oracle Database 10g - Data In Column Stored In Different Character Set

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

Non Unique Alternate Key Of A Table

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

Oracle DB 10.2 - Export / Import Alternate Or Improvement

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

Replication :: Alternate Solution For Materialized Views

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

Forms :: Alternate For OLE In Unix Application Server

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

Performance Tuning :: Possible To Display Alternate Execution Plans

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

Performance Tuning :: Alternate Query Instead Of Querying Table Twice?

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

SQL & PL/SQL :: Double Right Outer Join

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

SQL & PL/SQL :: Finding Double Vowels

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

PL/SQL :: Representing Double Space?

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

SQL & PL/SQL :: Insert String Having Two Double Quote?

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

Data Guard :: Configuring Primary For A Failover (alternate) Remote Destination?

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

SQL & PL/SQL :: Display A Value In Between Single Quotes?

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

SQL & PL/SQL :: Using Apostrophe / Quotes In A String

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

SQL & PL/SQL :: Inserting Single Quotes

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

PL/SQL :: Embedding Quotes In Strings?

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

SQL & PL/SQL :: Procedure To Update Double Subcategory Sequence

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

Forms :: Printing Double-Sided Using SET_REPORT_OBJECT_PROPERTY?

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







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