Home » SQL & PL/SQL » SQL & PL/SQL » replace (oracle 10204)
replace [message #427458] Thu, 22 October 2009 13:23 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
At run time i want to read this query from table and replace the $date$ with todays date? THis date is variable and changes everyday. after which i want to execute the query dynamically.

I am reading the sql

select sql_value
into l_validate_sql
from sql_table;

l_validate_sql is now select count(*) into l_cnt from tab1 where client = 'XYZ' and asof_date = $date$

I am trying to replace as follows

l_ddl := REPLACE (l_validate_sql, '16-Oct-2009');

But l_ddl still reads

select count(*) into l_cnt from tab1 where client = 'XYZ' and asof_date = $date$

I want it to change to

select count(*) into l_cnt from tab1 where client = 'XYZ' and asof_date = '16-Oct-2009'

after which i will be doing

EXECUTE IMMEDIATE (l_ddl);

thanks
Re: replace [message #427459 is a reply to message #427458] Thu, 22 October 2009 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
static SQL

select count(*) into l_cnt from tab1 where client = 'XYZ' and asof_date = TRUNC(SYSDATE)

will do same thing
Re: replace [message #427460 is a reply to message #427459] Thu, 22 October 2009 13:34 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
the date can be any date selected at runtime. it will not always be sysdate

[Updated on: Thu, 22 October 2009 13:34]

Report message to a moderator

Re: replace [message #427462 is a reply to message #427458] Thu, 22 October 2009 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use constants in your query use a bind variable.

Regards
Michel
Re: replace [message #427463 is a reply to message #427460] Thu, 22 October 2009 13:43 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
create or replace sp_test(in_date in date)
as
l_cnt number;
begin
select count(*) into l_cnt from emp where hire_date=in_date;
dbms_output.put_line('Count :'||l_cnt);
end;
/ 
Re: replace [message #427467 is a reply to message #427463] Thu, 22 October 2009 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Its_me_ved

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: replace [message #427468 is a reply to message #427467] Thu, 22 October 2009 14:09 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir, My apolgies!! I wont next time.
OP was going in a wrong direction. He could do something by creating a function/stored procedure but instead was thinking about Dynamic sql.Which is wrong. So I provided the answer.
Re: replace [message #427493 is a reply to message #427458] Thu, 22 October 2009 23:09 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
read up on execute immediate.

This will show you how to pass variables into a select statement, and how to get the result back into a variable.

Kevin
Previous Topic: incorrect number of arguments for default constructor
Next Topic: Compile view
Goto Forum:
  


Current Time: Thu Sep 29 22:43:32 CDT 2016

Total time taken to generate the page: 0.12787 seconds