SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

May 6, 2013

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

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between IN And EXISTS Operator

Jan 10, 2012

what the difference between IN and EXISTS operator. Why should we use EXISTS operator?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Procedure And Function

May 22, 2010

I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.

both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?

View 4 Replies View Related

PL/SQL :: Difference Between Procedure And Function

Aug 25, 2012

difference between Procedure and Function.We Know that Procedure is Basically Used to perform Actions and Functions are Basically Used for Calculations but what would be the exact difference since Actions/Calculations can be done in both Procedure and Functions.Which factors decide whether we need to write a Procedure and in which cases we need Function.

View 4 Replies View Related

Difference Between Stored Procedure And Function?

Jan 12, 2010

I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.

The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..

I want to know what is the difference betn stored procedure and function?

From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).

View 2 Replies View Related

SQL & PL/SQL :: Difference In Num_rows And COUNT Function?

Dec 7, 2012

SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402

This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.

SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998

But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.

View 2 Replies View Related

PL/SQL :: Difference Between Translate And Replace Function

Jan 6, 2013

Explain with the best example for difference between translate and replace function.

View 2 Replies View Related

PL/SQL :: Difference In Num_rows And Count Function

Dec 7, 2012

SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402

This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.

SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'

and owner='APP'; --Number of records 237213998But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.

View 3 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;

that i have worte..

View 3 Replies View Related

SQL & PL/SQL :: Difference Between Deterministic Functions And Function Result Cache In 11g

Oct 30, 2012

difference between Difference between Deterministic functions and Function result cache in 11g?

View 4 Replies View Related

Reports & Discoverer :: Difference Between Formula Column And Function In Program Unit?

May 24, 2013

know when to use a formula column and when to use a function in program unit in oracle reports.

View 1 Replies View Related

SQL & PL/SQL :: Where Clause In Concat

Jul 24, 2010

I has a table with team names like

Table name:- Teams

Structure:- name
Ind
Aus
Pak

I want the o/p as:- Fixtures
Ind Vs Aus
Ind Vs Pak
Aus Vs Pak
.......

I tried it but failed in what condition to use in the where clause.I did up to this:-

select name||' Vs ' || name from teams where .......;

how to get the result.

View 2 Replies View Related

PL/SQL :: CONCAT Not Working As Documented

Sep 10, 2013

I've written a SP and it refuses to work with the CONCAT function as documented. >>Yes I have searched the web and other locales to no avail. Create Procedure

`SP_CODE`(IN search_term VarChar(200))begin  if search_term is not null then  begin  

Select b.s_term, a.O_Number, a.S_CODE, a.LONG_DESC From JKC a INNER JOIN search_terms b ON a.S_CODE = b.S_CODE Where b.s_term like CONCAT ('%', search_term, '%');    end;end;  

The editor believes that the problem is at CONCAT('%', search_term, '%'); 

View 7 Replies View Related

SQL & PL/SQL :: Concat Column Values

Sep 22, 2011

Table creation

SQL> create table concat_test (
2 empno varchar(10),
3 empname varchar (10),
4 description varchar(50) )
5 /

Inserts are

insert all
into concat_test (empno,empname,description) values ('1','pratik','want')
into concat_test (empno,empname,description) values ('1','pratik','to')
into concat_test (empno,empname,description) values ('1','pratik','concat')
select * from dual;

I want the o/p as

Empno empname description
1 Pratik want to concat

View 5 Replies View Related

PL/SQL :: SQL Query Concat Numeric Or Value

Jan 3, 2013

I am getting numeric or value error when concatenating sql script..

Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)

V_SAMPLEQUERY VARCHAR2(2000);
     
firstpart:_

V_SAMPLEQUERY := '      
SELECT
AB.ABCDID AS ABCDID,
CD.MEMBERID AS MEMBERID,

[Code]..

secondpart:_ ***ERROR PART***

V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error

View 2 Replies View Related

PL/SQL :: Concat Multiple Output Lines?

Nov 21, 2012

I'm struggling with a problem for quite a while now and can't find any solution online so far ...

OK I got three different Tables

cust_tbl:
customer:id
nuxx :1
naxx :2
baxx :3
suxx :4

[code]...

As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row

View 9 Replies View Related

SQL & PL/SQL :: Concat Or Append In Long-raw Type Column?

Feb 4, 2012

Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type

View 15 Replies View Related

Server Utilities :: CONCAT Fails In SQLLoader?

Mar 2, 2010

I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,

LOCAL_NUM "'20'||:local_num",

results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,

LOCAL_NUM "CONCAT('20', :local_num)",

result is the same.The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.

View 2 Replies View Related

Forms :: Error In Concat A Clob With Char Datatype

Nov 2, 2011

How could we concat a clob with a char datatype.

declare
a clob;
b varchar2;
c clob;
begin
c:=a||b;
end;

When execute above code in form runtime, I get error ora-32767.

"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified.
Action: Correct the maximum line size to be in the range [1, 32767]."

View 3 Replies View Related

SQL & PL/SQL :: Decode With Like Operator

Jul 12, 2013

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.

View 5 Replies View Related

SQL & PL/SQL :: Uses Of Check Operator

Sep 16, 2010

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))
/

View 6 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 :: Using 2 Union Operator

Jun 28, 2010

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?

View 9 Replies View Related

SQL & PL/SQL :: Minus Operator And NULL

Feb 12, 2013

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

View 3 Replies View Related

Forms :: Use Like Operator With If Condition?

Jan 19, 2013

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%'.

View 1 Replies View Related

SQL & PL/SQL :: Escape Operator Symbol?

Feb 28, 2011

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

View 2 Replies View Related

SQL & PL/SQL :: Oracle 10g Quote Operator

Sep 7, 2011

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

View 4 Replies View Related

PL/SQL :: Rownum In UNION Operator

Feb 18, 2013

I 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?

View 11 Replies View Related

Optimizer Cannot Merge A View That Contains Set Operator

Jul 18, 2012

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

View 5 Replies View Related







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