D2K - what is the main difference between bind parameter and lexical parameter where are they used can any

GeekInterview.com > Interview Questions > Oracle > D2K

Go To First | Previous Question | Next Question

D2K | Question 26 of 71

what is the main difference between bind parameter and lexical parameter where are they used can anyone please illustrate with example?

Total Answers and Comments: 5

Last Update: April 03, 2009 Asked by: gopi

Sponsored Links

Best Rated Answer

—

Submitted by: trraka

Both could be used in reports as well as forms.

In reports Lexical parametrs are used to create the where clause of the Report query dynamically.

eg

select * from em where &p_emp;

and in the after parameter form trigger you can set the values of the where clause as:

p_emp:='emp_name like %:p_emp_name% '

Above answer was rated as good by the following members:
kamarajg, bc_sumanamara

June 27, 2006 05:17:57

Badhu

RE: what is the main difference between bind parameter...

Bind Parameters are denoted by (:) Used to get the value from the User.

Lexcial Parmeters are denoted by (&) Used to get the Clause from the User.

Forms Pl/Sql and SQL - we can use both. Reports - we can use Bind Paramters.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

March 03, 2008 05:45:41

sanjoy.dubey

Member Since: March 2008 Contribution: 5

RE: what is the main difference between bind parameter and lexical parameter where are they used can anyone please illustrate with example?

In Reports you can use both bind and lexical parameters

Is this answer useful? Yes | No

March 19, 2008 02:26:19

trraka

Member Since: January 2008 Contribution: 6

RE: what is the main difference between bind parameter and lexical parameter where are they used can anyone please illustrate with example?

Both could be used in reports as well as forms.

In reports Lexical parametrs are used to create the where clause of the Report query dynamically.

eg

select * from em where &p_emp;

and in the after parameter form trigger you can set the values of the where clause as:

p_emp: 'emp_name like :p_emp_name '

Is this answer useful? Yes | No

2 0

Overall Rating: +2

September 18, 2008 13:26:36

Raghu2008

Member Since: August 2008 Contribution: 9

RE: what is the main difference between bind parameter and lexical parameter where are they used can anyone please illustrate with example?

Hi

The bind parameter is prefixed with ':' and it is used to accept and refer the value while executing the queries where as lexical parametes (prefixed with '&') are used to build report queries dynamically. Bind parametes can be used in Report and Forms but lexical parameters can be used on reqports only.

Thanks
Raghu.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

April 01, 2009 07:45:43

samareshp

Member Since: April 2009 Contribution: 24

RE: what is the main difference between bind parameter and lexical parameter where are they used can anyone please illustrate with example?

Both are used in reports.

Differences between bind and lexical references

Bind references are used to replace a single value in SQL or PL/SQL. Specifically bind references may be used to replace expressions in SELECT WHERE GROUP BY ORDER BY HAVING CONNECT BY and START WITH clauses of queries.
Bind references may not be used in a FROM clause.
An example is:
SELECT ORDID TOTAL
FROM ORD
WHERE CUSTID :CUST

Lexical references are placeholders for text that you embed in a SELECT statement when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT FROM WHERE GROUP BY ORDER BY HAVING CONNECT BY and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is: SELECT ORDID TOTAL
FROM &ATABLE

A reference to a parameter used to represent a string of text in a SQL SELECT statement. For a lexical reference you must precede the parameter name with an ampersand (&).

You cannot make lexical references in a PL/SQL statement. You can however use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.

If a column or parameter is used as a lexical reference in a query its data type must be character.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank