Home » SQL & PL/SQL » SQL & PL/SQL » Build Parametric queries in SQL
Build Parametric queries in SQL [message #564519] Fri, 24 August 2012 12:07 Go to next message
vhuaman
Messages: 2
Registered: August 2012
Location: Morristown, NJ
Junior Member
When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle

Example

SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=[Enter Date];

Maybe this question was posted before, but I can not find it in the Forum yet. I will appreciate your comments

Vicente
Re: Build Parametric queries in SQL [message #564520 is a reply to message #564519] Fri, 24 August 2012 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
what date is 10/11/12?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>How can I do something similar in SQL Oracle
answer depends upon which SQL client software you use to interface to Oracle DB
Re: Build Parametric queries in SQL [message #564524 is a reply to message #564520] Fri, 24 August 2012 12:45 Go to previous messageGo to next message
vhuaman
Messages: 2
Registered: August 2012
Location: Morristown, NJ
Junior Member
Regarding the Parametric query question
Here is my Oracle details

BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Vicente
Re: Build Parametric queries in SQL [message #564525 is a reply to message #564519] Fri, 24 August 2012 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not work like MS Access, it is not at the same time the db and the application.
You have the database/rdbms and in other place the application, it is a n-tiers model with n>1.

You can create a query string in your application (depending on your programming language) like
SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=:datevar

Then you application ask for the date (or the user fills a field) you put in a variable, say myvar, then you call Oracle passing the string query and telling Oracle that the value of ":date" is in the variable "myvar".

Regards
Michel

Re: Build Parametric queries in SQL [message #564614 is a reply to message #564519] Sun, 26 August 2012 20:26 Go to previous messageGo to next message
tony123
Messages: 9
Registered: August 2012
Junior Member

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 27 16:55:04 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select object_id from all_objects where created> &1;
Enter value for 1: sysdate
old   1: select object_id from all_objects where created>&1
new   1: select object_id from all_objects where created>sysdate

no rows selected

SQL> select object_id from all_objects where created>&1;
Enter value for 1: sysdate-30
old   1: select object_id from all_objects where created>&1
new   1: select object_id from all_objects where created>sysdate-30

 OBJECT_ID
----------
     87119
     87122
     87123
     87126
Re: Build Parametric queries in SQL [message #564615 is a reply to message #564614] Sun, 26 August 2012 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
tony123,
Welcome to this forum.
For your first post, you did much better than most folks!
Re: Build Parametric queries in SQL [message #565507 is a reply to message #564615] Tue, 04 September 2012 17:38 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
@tony123. &1 is a string, so you need to explicitly convert to a string when you set it, and explicitly convert back to a date in the query.

This is a "parametrized view" (depending on your definition of course).

set echo on
CREATE OR REPLACE VIEW EMP_VIEW
(ENAME, EMPNO, hiredate)
AS 
select ename, empno, hiredate
  from emp 
 where hiredate between to_date(sys_context( 'emp_view_ctx', 'start_dt'), 'mm/dd/rr')
 and to_date(sys_context( 'emp_view_ctx', 'end_dt'), 'mm/dd/rr');
create or replace context emp_view_ctx using emp_view_proc;
create or replace procedure emp_view_proc(start_date in date, end_date in date)
as
begin
 DBMS_SESSION.set_context ( 'emp_view_ctx', 'start_dt', to_char(start_date, 'mm/dd/rr'));
 DBMS_SESSION.set_context ( 'emp_view_ctx', 'end_dt', to_char(end_date, 'mm/dd/rr'));
end;
/
begin
  emp_view_proc(to_date('02/20/81', 'mm/dd/rr'), 
                to_date('03/20/81', 'mm/dd/rr'));
end;
/
select * from EMP_VIEW; 
begin
  emp_view_proc(to_date('05/01/81', 'mm/dd/rr'),
                to_date('09/20/81', 'mm/dd/rr'));
end;
/
select * from EMP_VIEW; 

SQL> begin
  2    emp_view_proc(to_date('02/20/81', 'mm/dd/rr'),
  3                  to_date('03/20/81', 'mm/dd/rr'));
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select * from EMP_VIEW;

ENAME           EMPNO HIREDATE
---------- ---------- ---------
ALLEN            7499 20-FEB-81
WARD             7521 22-FEB-81

SQL> begin
  2    emp_view_proc(to_date('05/01/81', 'mm/dd/rr'),
  3                  to_date('09/20/81', 'mm/dd/rr'));
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select * from EMP_VIEW;

ENAME           EMPNO HIREDATE
---------- ---------- ---------
BLAKE            7698 01-MAY-81
CLARK            7782 09-JUN-81
TURNER           7844 08-SEP-81

SQL> 

Re: Build Parametric queries in SQL [message #590354 is a reply to message #564614] Wed, 17 July 2013 08:46 Go to previous messageGo to next message
mgough
Messages: 4
Registered: July 2013
Junior Member
Hi Folks, how would we parameterize the sql in plsql for testing 'set :datevar = x' for example. - Thx M Confused

[Updated on: Wed, 17 July 2013 08:47]

Report message to a moderator

Re: Build Parametric queries in SQL [message #590356 is a reply to message #590354] Wed, 17 July 2013 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


SQL statement must be known & static as parse time; other wise must use dynamic SQL & EXECUTE IMMEDIATE

Re: Build Parametric queries in SQL [message #590358 is a reply to message #590354] Wed, 17 July 2013 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have a specific question, please create your own question.
Before, Please read OraFAQ Forum Guide.

Note that your current question is not understandable, give more details on what you want to do.

Regards
Michel
Re: Build Parametric queries in SQL [message #590368 is a reply to message #590358] Wed, 17 July 2013 09:04 Go to previous messageGo to next message
mgough
Messages: 4
Registered: July 2013
Junior Member
I have an SSRS report that supplies one or a number of values per parameter to a sql query to be executed against an oracle database.
I would like to specify the parameters to be used in the query diectly in plsql for testing purposes. Would you know the syntax to do this without changing the where clause?

********************Excerpt************
where b.crt_pro in (:P_PRO)
and b.jayer_name in (:P_FUST) )
***************************************
Re: Build Parametric queries in SQL [message #590369 is a reply to message #590368] Wed, 17 July 2013 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to realize that "in (:P_PRO)" is "=(:P_PRO)".
Is this what you want?

Regards
Michel

[Updated on: Wed, 17 July 2013 09:07]

Report message to a moderator

Re: Build Parametric queries in SQL [message #590375 is a reply to message #590369] Wed, 17 July 2013 09:25 Go to previous messageGo to next message
mgough
Messages: 4
Registered: July 2013
Junior Member
P_PRO can be a list of values so I guess it has to stay as IN ...

[Updated on: Wed, 17 July 2013 09:25]

Report message to a moderator

Re: Build Parametric queries in SQL [message #590378 is a reply to message #590375] Wed, 17 July 2013 09:27 Go to previous messageGo to next message
cookiemonster
Messages: 11068
Registered: September 2008
Location: Rainy Manchester
Senior Member
But it won't act like a list of values. It'll act like a single value that won't match anything.

Read this: varying in list
Re: Build Parametric queries in SQL [message #590379 is a reply to message #590375] Wed, 17 July 2013 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this is NOT what PL/SQL understand: there is one variable then there is one value whatever this value is.
Search for "varying in-list" here and on AskTom site.

Regards
Michel
Re: Build Parametric queries in SQL [message #590380 is a reply to message #590379] Wed, 17 July 2013 09:29 Go to previous message
mgough
Messages: 4
Registered: July 2013
Junior Member
Thanks folks - I'll check it out !!
Previous Topic: Help me with the PL/SQL code to update the table in 11g
Next Topic: Dynamic SQL error : Want to define dynamic type for a variable in Oracle 10g
Goto Forum:
  


Current Time: Tue Oct 21 01:37:18 CDT 2014

Total time taken to generate the page: 0.12161 seconds