which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.
In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)
However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
I have input like below
ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA
and I want to get concatenated values separated
ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA
I am trying to pass the concatenated value as parameter to the select statement. But it is not returning the results.
If i pass mytrx(1) := '123' it is working fine. It is failing when i pass more than one value.
Table script
create table xxtesttrx (trx_number varchar2(50));
insert into xxtesttrx
values('123');
insert into xxtesttrx
values('456');
Code
declare
-- declare the table type
TYPE xxtest IS TABLE OF
xxtesttrx.trx_number%Type
INDEX BY BINARY_INTEGER;
[code]......
I have not found a solution for this, but it could be that I don't know what to search for.
I need to edit the following statement:
SELECT a.id||a.name||a.amount*2 as transaction
FROM a
WHERE a.amount IN (500, 1000)
To return:
TRANSACTION
------------------------------------
123SMITH1000
123SMITHADJUSTED AMOUNT PER X
456JONES2000
456JONESADJUSTED AMOUNT PER X
The returned rows need to be in this format to be executed in another database.
Can this be done?
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
I have a table that reads something like this
City Route
NewYork 2
NewYork 4
NewYork 5
London A
London B
Paris X1
I want to assign Routes (concatenated) to an item on page load... as an example for NewYork it should read like this
P2_ROUTE = 2, 4, 5
I am looking to do the above using query, something like this
select (concatenated route)
from Table_A
where
City = :P2_City
how to concatenate variables.?
how to write below query in pl/sql cursor. The help table has two associated tables, help_txt and help_id, which will have strings of data concatenated into one sales contact record. There are multiple lines of text per comment and multiple lines of resolution text at 40 characters per line. The key to the help_text table (id, date,seqno) is the main key to the help_txt table and help_id t table with a sequence added to each table
The formatted string will contain some text and variables with the comment lines (1-10 or more) concatenated first, followed by the resolution lines (1-10 or more). There will be multiple comment and multiple resolution lines. The Cust_Cmnt_Txt lines and the Resolved_Desc lines should be concatenated and formatted in the following string (% marks the variable string) :
'help taken ' %help.Taken_Dte 'received from the following source: ' %help.id. 'Remark Text: ' %help_text (where help_txt_Seq = 1) %help_text (where help_text_seq = 2-10 or more) 'Resolution: ' %help_id_Res_Txt.Resolved_Desc (where help_ID_Txt_Seq = 1) %help_ID_Res_Txt.Resolved_Desc (where help_id_Txt_Seq = 2-10 or more)
create table test_g(x date);
insert into test_g values (to_date('01-NOV-2001','DD-MON-YYYY'));
insert into test_g values (to_date('02-NOV-2011','DD-MON-YYYY'));
insert into test_g values (to_date('03-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('12-DEC-2012','DD-MON-YYYY'));
insert into test_g values (to_date('31-DEC-2012','DD-MON-YYYY'));
[code].....
I wrote below procedure
create or replace procedure p_testq(p_in_date in date) is
v_comp date;
v_strg varchar2(200);
i number:=1;
type t_trc is ref cursor;
trc t_trc;
v_sql varchar2(2000);
-- record to which data goes into
type t_prec is record(x date);
prec t_prec;
-- plsql table to store data
type t_frec is table of t_prec index by binary_integer;
frec t_frec;
-- flow of data, is from v_sql --> plsql record --> plsql table
begin
dbms_output.put_line(' month of paramter '|| P_IN_DATE ||' is '||to_char(P_IN_DATE,'MON'));
select min(x)
into v_comp
from test_g
where x <= P_IN_DATE
[code].....
how do I store v_strg values so that the dates are included in single quotes
begin
p_testq('12-DEC-2012');
end;
v_strg: 06-Nov-2012,09-Nov-2012
I want values to be '06-Nov-2012',''09-Nov-2012'
I have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".
select q'[Oracle's world ]'
from dual
But The following query works.
select q'[It's Oracle's world ]'
from dual
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
I have a simple update statement. Sometimes the data in this statement has single quotes in it (like shown below).
Update table1 set account = 'CD'S NOT MINE' WHERE NUMBER = '0027201'
When I run this SQL, I get SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 - "quoted string not properly terminated"
Is there an escape charecter that I can use?
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]....
I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;
returns nothing.
Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;
returns character '?'.
create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
This package is generating excel file which contains cursor result.In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.
eg:
Zip_
'01234
'12567
'23432
'00234
create or replace
PACKAGE BODY PKG_MONTH_END_AUTOMATION AS
PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')
[code]...
-- main body
BEGIN
-- Generating Zip Files
SELECT last_day(add_months(sysdate,-1))
INTO v_last_date
[code]...
I wish to replace the string "Benutzername&Kontakt" with nothing.
SELECT REPLACE (role_owner, 'Benutzername&Kontakt', '')
FROM JC_NAME_62
WHERE jc_name LIKE 'SAP_R4_BIN5\_%' ESCAPE ''
But my Toad prompts me always to fill in the variable. I don't wish to do that, I wish to escape the &
The documentation for v$database says: QUOTE V$DATABASE displays information about the database from the control file. Is it safe to assume that there will only ever be one row queried from this view?
View 2 Replies View RelatedINSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')
View 2 Replies View RelatedI 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.
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
1,10,11,2,....like that.
What is the best alternative to using VARCHAR2 if the output gets truncated after 4000 bytes in a PL/SQL function?
View 10 Replies View Relatedin our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
View 2 Replies View Relatedcreate table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/
[Code]....
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105
[Code]...
Function created.
SQL> select * from members
2 order by safe_to_number(roomID);
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4
[Code]....
How can I sort the output to get the result like below
2007209 1 1
2006253 1 1-A
2006253 1 1-A
[Code]...
and so on...
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);
Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b)
Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
How to convert a varchar2 column to CLOB when there is a thousands of records in it.
View 15 Replies View RelatedHow to convert varchar2 to number data?
View 8 Replies View RelatedWhat is the exact difference between varchar and varchar2?
As i know only the length is the one difference.Apart from this length,what are all the differences?
I'm trying to insert CLOB to varchar2 from one DB to another using database link but failed.
INSERT INTO tmp_std_master@aapsbhx.iss.soton.ac.uk
(student_id, title, first_name, middle_name, surname, hus_id,
date_of_birth, email_address, prog_desc --- varchar2 (100)
)
SELECT DISTINCT c.ref1 student_id, c.title, c.firstname firstname,
[code]....