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
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'
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]....
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 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 &
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
create table my_tab
(U_name varchar2(30) ,
constraint ck_check (U_name = upper(m_name))
/
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.
I have an query i.e.
I want 3 lines input in 1 line using 2 union operator like
Input:-
'i love playing
football and
volleyball'
i want the output like:-
"i love playing football and volleyball"
solve query using 2 union operator?
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible
i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .
for example ...
if search string=string2 then
message('Record found');
end if ;
i want to use like '%search string%'.
1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?
Employee name ----> rama_krishna_raj
what the difference between IN and EXISTS operator. Why should we use EXISTS operator?
View 1 Replies View RelatedI have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2
Will the above query solve my purpose?
Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB )
Number of downloads: 1
view_def.txt ( 14.12K )
Number of downloads: 2
The following runs no problem
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND
[code]....
However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
[code]....
I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
How to replace the like operator for increase the performance. Because it is taking more time and not using the index.
SELECT *
FROM emp
WHERE ename like '%AL';
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual;
--Output H S
Select Concat('H ',' S') A From Dual;
--Output H S
Select Length('H '||' S') A From Dual;
--Output 6
Select Length(Concat('H ',' S')) A From Dual;
--Output 6
How we can compare IN operator between two text boxes in form
I have two text boxes on form
control.txt1 - 003
control.txt2 - 001,002,003
On button pressed
if :control.txt1 in (:CONTROL.TXT2) THEN
MESSAGE('1');
MESSAGE('1');
else
MESSAGE('0');
MESSAGE('0');
END IF;
All the time Message is 0 .
I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)
But it does not raise any exception and the query works absolutely fine generating desired results when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)
I want to link to blocks using description as there is no relation , for example i have two tables with one field in common called description, and i want to link this field in two tables using like operator.
create table item ( item_code varchar2(12),item_name varchar2(30));
insert into item VALUES('A','HEA160');
insert into item VALUES('B','HEA180');
create table stk (sl_item varchar2(12),sl_desc varchar2(30),sl_qty number);
insert into stk VALUES ('X','HEA160X1000',12);
insert into stk VALUES ('X','HEA160X2000',4);
insert into stk VALUES ('Y','HEA180X3000',10);
Suppose i click on item block item_desc with value on HEA160 all the items similar to that should appear in stk block like 'HEA160X1000' ,'HEA160X2000' , if i click on 'HEA180' on item then 'HEA180X3000' it should come.
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE
I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.
I have run very simplified code to try to sort it out but no success:
SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');
select 1 from dual where regexp_like('123', '^[0-9]$');
same result from both.
I am using Oracle 11 and freetoad 11.
I have a table called Student and a column as name now if i write a Query
select * from student where name < 'BRIAN D'
How does the comparison will be done.
