|
|
| Re: Lexical Parameter. [message #539403 is a reply to message #539401] |
Sun, 15 January 2012 01:18   |
 |
Barbara Boehmer
Messages: 9104
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 |
flyboy
Messages: 1903
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 #539408 is a reply to message #539407] |
Sun, 15 January 2012 02:02 |
flyboy
Messages: 1903
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 #539410 is a reply to message #539409] |
Sun, 15 January 2012 02:26 |
flyboy
Messages: 1903
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 #539414 is a reply to message #539412] |
Sun, 15 January 2012 02:47 |
 |
Littlefoot
Messages: 21823
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; select e.empno, e.ename, e.job, e.sal
from emp e
where e.job = :par_job
&lex_where |
|
|
|
|
|
|
|
|
|
| Re: Lexical Parameter. [message #539427 is a reply to message #539424] |
Sun, 15 January 2012 10:01 |
|
Barbara Boehmer
Messages: 9104
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
