D2K - lexical references

GeekInterview.com > Interview Questions > Oracle > D2K

Go To First | Previous Question | Next Question

D2K | Question 69 of 71

lexical references

I am making an oracle 10g report and in Query Builder I tried to use lexical reference after the from clause
like

SELECT ORDID, TOTAL
FROM &ATABLE;

But it was showing me an error "invalid table name"
Can't i use a lexical reference to make me enter the table name at run time?

please help.

Total Answers and Comments: 3

Last Update: February 25, 2009 Asked by: pragya.vyas

Sponsored Links

Best Rated Answer

—

Submitted by: Raghu2008

You can use the lexical parameters at From clause but you have to use lexical parameter as given below.

SELECT 'last_name' client_last_name, 'first_name' client_first_name,
'mailing_address1' mailing_address, 'city' city
FROM dual
WHERE 1=2
&RGROUP1

In above query I have used 2 lexical parameters and while calling the above report pass the &RGOUP1 values using the below statement.

:RGROUP1 := 'UNION SELECT 'last_name' client_last_name, 'first_name'
client_first_name, 'mailing_address1' mailing_address, 'city' city
FROM emp_det'

Using the above method you can generate the dynamic query in the report.

Thanks,
Raghu.

Above answer was rated as good by the following members:
nalinbit

September 18, 2008 13:22:27

Raghu2008

Member Since: August 2008 Contribution: 9

RE: lexical references

You can use the lexical parameters at From clause but you have to use lexical parameter as given below.

SELECT 'last_name' client_last_name 'first_name' client_first_name
'mailing_address1' mailing_address 'city' city

FROM dual
WHERE 1 2
&RGROUP1

In above query I have used 2 lexical parameters and while calling the above report pass the &RGOUP1 values using the below statement.

:RGROUP1 : 'UNION
SELECT 'last_name' client_last_name 'first_name'
client_first_name 'mailing_address1' mailing_address 'city' city

FROM emp_det'

Using the above method you can generate the dynamic query in the report.

Thanks
Raghu.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

October 31, 2008 08:13:33

jitendra_kumar

Member Since: October 2008 Contribution: 2

RE: lexical references

Lexical references is used to runtime dynamic where conditions. If you create query through query builder colon(:) must be used and this is used in after parameter form.

Jitendra kumar(software Engineer).

Is this answer useful? Yes | No

February 25, 2009 02:42:40

Rkhatri

Member Since: February 2009 Contribution: 1

RE: lexical references

It is working fine with Oracle 10g and even with 11g

SQL>select &a &b from &tablename;

It will ask respective values and it work fine

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank