| replace [message #427458] |
Thu, 22 October 2009 13:23  |
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 #427463 is a reply to message #427460] |
Thu, 22 October 2009 13:43  |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
