SQL & PL/SQL :: Inserting Single Quotes
Dec 4, 2012I 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
ADVERTISEMENT
PL/SQL :: How To Replace 2 Single Quotes To Single Quote
Nov 21, 2012I have an script.sql that receives as a parameter an string.
example:
@C:/myscript.sql "o'connor"
user_account_value varchar2(120) := '&1';
EXECUTE IMMEDIATE "Select * from Table where column = :1 " USING user_account_value I am not sure how to deal with string that contains single quotes.
If the parameter were passed as : "o''connor" this will work
If the parameter is pass as: "o'connor" this will not work.
so my question is what options do I have to deal with dynamic queries and single quotes.
I tried replacing replace(myParameter,'''',''''''); but not working well.
SQL & PL/SQL :: Display A Value In Between Single Quotes?
Nov 18, 2011how 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,
SQL & PL/SQL :: Concatenate A Comma But Not Single Quotes
Oct 8, 2010In 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.
PL/SQL :: SYSDATE In DDMMYYYY Inside Single Quotes
Dec 4, 2012I have a requirement like:
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'
So it returns me two rows.
How can I do it in single quotes.
Inserting Into A Table By Selecting Multiple Rows Into A Single Row
Jul 27, 2012I have a flat file as source wherein I am getting values like
Comp_id, Comp_name, ISIN, column_name, column_value
The structure is like this may contain multiple records like Comp_id, Comp_name, ISIN will be same, but column_name will contain the column_name to which its corresponding column_value needs to be populated to.
E.g. of Feed File -
Comp_id, Comp_name, column_name, column_value
1,HSBC,branch_name,HSBC-DELHI
1,HSBC,branch_add,24-Lajpat Nagar
1,HSBC,branch_phone,2322322
2,HSBC,branch_name,HSBC-MUMBAI
2,HSBC,branch_add,24Andheri
2,HSBC,branch_phone,4445221
2,HSBC,branch_postalcode,400023
Target table structure
Comp_id, Comp_name, branch_name, branch_add, branch_phone, branch_postalcode
I need to insert the above data to a table by selecting data from above scenario.
SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?
Jul 26, 2011shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies View RelatedSQL & PL/SQL :: Using Apostrophe / Quotes In A String
Feb 9, 2011Here 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 ?
PL/SQL :: Embedding Quotes In Strings?
Aug 16, 2013In 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
SQL & PL/SQL :: Show Value Of Variable In Double Quotes?
Dec 4, 2011declare
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"
How Many Quotes To Use In Dynamic Query Using IN Clause With String
Nov 23, 2011I 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'''
SQL & PL/SQL :: Adding Quotes Around Field Only If Comma Exists Within
Mar 25, 2010I 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).
Server Utilities :: Loading String Has Double Quotes In It?
Aug 22, 2013I 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.
Server Utilities :: Sql Loader / How To Ignore Error Due To Quotes
May 5, 2010I have control file written like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
but i have data in csv file like
660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",
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.
SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String
Oct 29, 2012I 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 ")
SQL & PL/SQL :: Double Quotes In Column Name Can Be Replaced With Alternate Character?
Feb 14, 2012Any 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
Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table
Aug 25, 2010I 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)")
Inserting In A View
Aug 13, 2010I have a view ( from many tables) , an error view in wich i monitor errors that appear in my project. This view should be empt always but whenever a error occurs the view shows me this. I wanna put a trigger or something like that on that view to send me an e-mail whenever a line is inserted in that view. ( I don't wanna use a job for that to make a count because this will affect the entire database).
View 3 Replies View RelatedInserting Dates Into Table
Mar 10, 2012My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
Inserting Values From A Sequence?
Jun 10, 2011I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
Oracle SQL Plus Inserting Into A New Field?
May 29, 2007Ive added a new field to a table The new field is called Release_confirmation. How do I add the same value to this field for all rows ie. confirm.
So Id basically be setting Release_confirmation to confirm for all existing rows.
Should I use update or insert?
Inserting Data Into Two Tables
Feb 15, 2009I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
Inserting Cursor Into A Table
Aug 27, 2010i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.
declare
p_out sysadm.CGF_PKG_REFCUR.rcDataCursor;
a table1.node%TYPE;
b table1.acct%TYPE;
c table1.descr%TYPE;
[code]......
In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.
p_out returns a result set of (select a,b,c from table2).
I am trying to get the result set from OLAP database and populate a table in the EPM database.
Inserting Blank Data
Jul 17, 2009I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
SQL & PL/SQL :: Inserting Images Into Database
Dec 22, 2011how to insert images into a table or database..i had created a column with BLOB datatype inside a table.
View 1 Replies View RelatedSQL & PL/SQL :: Inserting Data Using DBLINK
Feb 21, 2013I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
how to insert XMLTYPE data using DBLINK.
SQL & PL/SQL :: Inserting Date Ranges
Apr 30, 2010Can we insert multiple dates within give range in a single insert statement say from 1st Feb to 31st MAY 04,something like after insertion it should resemble like below
01-FEB-04
02-FEB-04
03-FEB-04
04-FEB-04
05-FEB-04
.
.
.
31-MAY-04
SQL & PL/SQL :: For Loop - Inserting Data?
Mar 7, 2012create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);
I want to insert data from stg1, stg2 into stg
Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2
I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;
it gives me table does not exist error:
so by stgi, i mean it should take stg1, stg2 etc
SQL & PL/SQL :: Inserting Data From One Table To Another?
Oct 23, 2010I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0
TEST22:
-----------
|sno |sname |
-----------
| | |
-----------
TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------
Required outcome is
TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------
Also this should be applicable for more than one value in the column col2 of table TEST22P.
ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------
I used decode & pivot insert for this,but the result is a failure.
SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
SQL>
sno sname
--------
1 null
null arun
AND
SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier
Inserting Into Table Values
Oct 6, 2012I need to insert rows in a table all the columns are have same value except one column i.e.
insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');
is there any short cut to insert because there are thousands of records to be inserted.