Consider a table with two columns

Table Name= table1
name varchar2 => this is the primary key
value varchar2

Two PL/SQL blocks

declare
v_name table1.name%type;
v_value table1.value%type;
begin
v_name:='123';
select value into v_value from table1 where name=v_name;
end;
/

and another block

declare
v_name table1.name%type;
v_value table1.value%type;
begin
v_name:='123';
select value into v_value from table1 where name=''||v_name||';
end;
/

Will there be any difference in performance b/w the two blocks

cheers
pushp

Yes - the second block won't compile! Perhaps you meant:

declare
v_name table1.name%type;
v_value table1.value%type;
begin
v_name:='123';
EXECUTE IMMEDIATE 'select value from table1 where name='''||v_name||'''' into v_value;
end;
/

... in which case the answer is that there will be negligible difference for such a small program, but that the first is MUCH better than the second as a general approach, or if the code is to be invoked MANY times with different values of v_name. This is because the first uses BIND VARIABLES.

The second could also be re-written to use bind variables:

declare
v_name table1.name%type;
v_value table1.value%type;
begin
v_name:='123';
EXECUTE IMMEDIATE 'select value from table1 where name=:x' into v_value USING v_name;
end;
/

But the first block still has the edge performance-wise, because it doesn't parse the statement each time it is called.

sorry the second block shud have been
declare
v_name table1.name%type;
v_value table1.value%type;
begin
v_name:='123';
select value into v_value from table1 where name=''||v_name||'';
end;
/

where the '' in (''||v_name||'' ) are two single quotes and not a single double quote.
The block compiles fine.

Well, the reason I do ask this is bcoz the first block causes a full table scan if value of v_name parameter contains a number.
whereas the second one does not.

Now they are the same, as far as I can see. You are concatenating NULL ('') onto the front and back of the bind variable v_name.

I don't see how the optimizer could "know" that v_name contains a number, since it is a varchar2 bind variable.

If you got this information from TKPROF, perhaps you could post the output here?