Home » Developer & Programmer » Reports & Discoverer » Lexical Parameter.
Lexical Parameter. [message #539401] Sun, 15 January 2012 00:48 Go to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Hi everyone,

I studied a document about lexical parameter in that it says
"Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"

And given an example as
Select * from emp, deptno 
      	&where. 


I am really confused with this is it right or wrong?

and i know about substitution variables using &

is this are same (lexical and substitution) or different.

Expect your valuable reply's
Re: Lexical Parameter. [message #539403 is a reply to message #539401] Sun, 15 January 2012 01:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
It usually helps if you provide a link to the article you are referring to, so that we can read the whole article and see in what context the excerpt is used. The terminology can get confusing. I find it more important to know what can be done than what it is called. Usually, when you hear people talking about lexical parameters, they are referring to parameters passed in forms and reports. However, I suppose a SQL*Plus substitution variable also constitutes a lexical parameter. As to whether or not they can be used in the manner mentioned, all you have to do is test and see, as shown below.

-- single column value:
SCOTT@orcl_11gR2> Select deptno, ename, job from emp where job = upper ('&job');
Enter value for job: clerk
old   1: Select deptno, ename, job from emp where job = upper ('&job')
new   1: Select deptno, ename, job from emp where job = upper ('clerk')

    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 JAMES      CLERK
        10 MILLER     CLERK

4 rows selected.


-- where clause:
SCOTT@orcl_11gR2> Select deptno, ename, job from emp &where;
Enter value for where: where job = 'CLERK'
old   1: Select deptno, ename, job from emp &where
new   1: Select deptno, ename, job from emp where job = 'CLERK'

    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 JAMES      CLERK
        10 MILLER     CLERK

4 rows selected.


-- whole select statement:
SCOTT@orcl_11gR2> set autoprint on
SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> begin
  2    open :g_ref for '&select';
  3  end;
  4  /
Enter value for select: select deptno, ename, job from emp where job = ''CLERK''
old   2:   open :g_ref for '&select';
new   2:   open :g_ref for 'select deptno, ename, job from emp where job = ''CLERK''';

PL/SQL procedure successfully completed.


    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 JAMES      CLERK
        10 MILLER     CLERK

4 rows selected.

Re: Lexical Parameter. [message #539405 is a reply to message #539401] Sun, 15 January 2012 01:29 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
It would be nice if you specified, which exact "document" you studied. As Barbara posted nice demonstration of sqlplus substitution variable, I am quite convinced that the "document" you studied denotes different client tool (Forms/Reports/?).

Note the difference among SQL (declarative language for handling data), PL/SQL (procedural language for the same) and sqlplus/Forms/... (client tool for data access).

Although client tools may (for convenience) have some elements same with the same meaning and naming, it is not always a rule.
Re: Lexical Parameter. [message #539406 is a reply to message #539405] Sun, 15 January 2012 01:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I found the following article containing the posted quote:

http://freshers-oracleapplications.blogspot.com/2008/06/reports.html

I also found the following:

http://www.dba-oracle.com/t_lexical_parameter.htm
Re: Lexical Parameter. [message #539407 is a reply to message #539406] Sun, 15 January 2012 01:51 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Greate barbara,


Thank you so much for your reply

fly boy- i got it from the link -< http://www.google.co.in/url?sa=t&rct=j&q=lexical+references+example&source=web&cd=4&ved=0CDsQFjAD&url=http%3A %2F%2Fgeorgenet.net%2Foracle%2Ffiles%2FSQL_PLSQL%2FReports_FAQ.doc&ei=WXMST6fSMsbmrAf8s7iGAg&usg=AFQjCNEsxoD_kKk2GH4z0mdzFIqQ obbQcg&cad=rja> and tool-reports 6i(client -server)

as you said about 'lexical parameters' are quit enough to clear my doubts.

Is it not possible to do lexical parameters inside a PL/SQL block?
the document that i got meant it like so "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"

[Updated on: Sun, 15 January 2012 01:57]

Report message to a moderator

Re: Lexical Parameter. [message #539408 is a reply to message #539407] Sun, 15 January 2012 02:02 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
So, you are interested in Reports client tool. In that case, you are correct - according to e.g. this article http://www.allinterview.com/showanswers/29009.html there is a difference from sqlplus substitution variable - it is available only in SQL SELECT statement and not in PL/SQL blocks.
As I wrote before - different client tool, different behaviour.

By the way, I do not know what your "document" contains as I will not (= do not want to) open any DOC file from unknown source on my computer (e.g. the one from your link).
Re: Lexical Parameter. [message #539409 is a reply to message #539408] Sun, 15 January 2012 02:13 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member



You can give me some examples for "it is available only in SQL SELECT statement and not in PL/SQL blocks."

Still i have some doubts about the possibilities of lexical prameters in SQL and PL/SQL.

Re: Lexical Parameter. [message #539410 is a reply to message #539409] Sun, 15 January 2012 02:26 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
I personally cannot, as I know nothing about Reports (except the fact that this reporting tool exists). Note that this is Oracle SQL and PL/SQL forum. You are looking for examples of Reports lexical parameter.
Maybe you should study other results google found found for "lexical parameter" or explore how PL/SQL block may be used in Reports (again, question on Reports, not PL/SQL).
Or you may choose proper forum (e.g. the Reports & Discoverer one on this site).
Re: Lexical Parameter. [message #539412 is a reply to message #539410] Sun, 15 January 2012 02:41 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Ok,

I am apreciating your mentality to help me,

Thank you so much "flyboy" -is it your nickname i don't know what is your exact name.
Re: Lexical Parameter. [message #539414 is a reply to message #539412] Sun, 15 January 2012 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Replies you got so far are, more or less, everything you need to know about lexical parameters. Barbara's examples showed you how to use them. If you follow links people posted, you'll learn even more. Finally, if you intend to use Reports Builder/Developer, open its Online Help System and search for "lexical parameters".

When do I use them? In cases where WHERE clause has to be set dynamically, depending on user's parameter value. I declare a new user parameter and name it LEX_WHERE. In After Parameter Form trigger I set its value, for example
if :par_deptno = 10 then
   :lex_where := ' and e.deptno = ' || :par_deptno;
else
   :lex_where := ' and 1 = 1';
end if;
Finally, I use it in report's query as
select e.empno, e.ename, e.job, e.sal
from emp e
where e.job = :par_job
&lex_where
Re: Lexical Parameter. [message #539417 is a reply to message #539414] Sun, 15 January 2012 03:12 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Thanks Little foot

I understood the syntax part from the discussion up to now, thanks everyone for your reply s. But what about the restriction of lexical reference in PL/SQL block ,is it right?
From your example it is referring in some SQL blocks only .
Re: Lexical Parameter. [message #539418 is a reply to message #539417] Sun, 15 January 2012 03:20 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Thank you everyone those who helped me to come out from the problem.

i got some idea from the doc which i found in the post of barbara. <http://www.dba-oracle.com/t_lexical_parameter.html>

If you got any extra idea please forward with this topic.
Re: Lexical Parameter. [message #539424 is a reply to message #539418] Sun, 15 January 2012 09:00 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
But what about the restriction of lexical reference in PL/SQL block ,is it right?

Run Reports Builder and try it.
Re: Lexical Parameter. [message #539427 is a reply to message #539424] Sun, 15 January 2012 10:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You can use lexical parameters, such as SQL*Plus substitution variables, in anonymous PL/SQL blocks, such as the third example that I provided previously:

SCOTT@orcl_11gR2> set autoprint on
SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> begin
  2    open :g_ref for '&select';
  3  end;
  4  /
Enter value for select: select deptno, ename, job from emp where job = ''CLERK''
old   2:   open :g_ref for '&select';
new   2:   open :g_ref for 'select deptno, ename, job from emp where job = ''CLERK''';

PL/SQL procedure successfully completed.


    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 JAMES      CLERK
        10 MILLER     CLERK

4 rows selected.


You cannot use such parameters within a PL/SQL block inside of a named stored procedure:

SCOTT@orcl_11gR2> set define off
SCOTT@orcl_11gR2> create or replace procedure stored_procedure_name
  2    (p_ref out sys_refcursor)
  3  as
  4  begin
  5    open p_ref for '&select';
  6  end stored_procedure_name;
  7  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> set define on
SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> execute stored_procedure_name (:g_ref)
BEGIN stored_procedure_name (:g_ref); END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "SCOTT.STORED_PROCEDURE_NAME", line 5
ORA-06512: at line 1


ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "g_ref"


You can pass such parameters to a named stored procedure:

SCOTT@orcl_11gR2> variable g_ref refcursor
SCOTT@orcl_11gR2> create or replace procedure stored_procedure_name
  2    (p_ref out sys_refcursor,
  3  	p_sel in  varchar2)
  4  as
  5  begin
  6    open p_ref for p_sel;
  7  end stored_procedure_name;
  8  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> execute stored_procedure_name (:g_ref, '&p_sel')
Enter value for p_sel: select deptno, ename, job from emp where job = ''CLERK''

PL/SQL procedure successfully completed.


    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 JAMES      CLERK
        10 MILLER     CLERK

4 rows selected.






[Updated on: Sun, 15 January 2012 10:08]

Report message to a moderator

Re: Lexical Parameter. [message #539431 is a reply to message #539427] Sun, 15 January 2012 11:37 Go to previous message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

This is really what i expected thank u so much Barbara for your time.
Previous Topic: Oracle Reports : COM or not COM ?
Next Topic: Oracle Discoverer report upgrade from 11i to R12
Goto Forum:
  


Current Time: Sat May 30 10:43:16 CDT 2020