Any body help us.............? [message #446329] |
Sun, 07 March 2010 22:29  |
ravi_1967
Messages: 5 Registered: March 2010 Location: Mumbai
|
Junior Member |
|
|
Please any body help us........as soon as possible.
declare
cursor r1 is
select tname from temp_dup_cdr_1 where partition_date between '01-feb-2010' and '01-mar-2010'; -- tname = partition table name
c_party_network_id temp_dup_cdr_1.c_party_network_id%type;
hello varchar2(100);
begin
for a1 in r1
loop
hello := a1.tname;
dbms_output.put_line('======>> '||hello);
declare
cursor c1 is
select pname from '||hello||' ------->> compiler shows error this point
where sysdate start_date and end_date
order by 1;
uname varchar2(50);
count number(6) :=1;
begin
for j1 in c1
loop
dbms_output.put_line('=======>>> '||uname);
count := count + 1;
exit when c1%notfound;
end loop;
close c1;
end;
end loop;
close r1;
end;
[EDITED by LF: applied [code] tags]
[Updated on: Mon, 08 March 2010 01:25] by Moderator Report message to a moderator
|
|
|
|
Re: Any body help us.............? [message #446347 is a reply to message #446337] |
Sun, 07 March 2010 23:23   |
ravi_1967
Messages: 5 Registered: March 2010 Location: Mumbai
|
Junior Member |
|
|
We are using the same way, but my question is that whole partition table name is storing in a variable.
That variable we ussing in 2nd cursor as a table format.
My problem is that 2nd query pl/sql compiler is not suported.
and shows error @ particular line and column. where we have
apply the variable(previous cursour return the table name)
Best Regard's,
Ravindra Upadhyay
|
|
|
|
Re: Any body help us.............? [message #446383 is a reply to message #446329] |
Mon, 08 March 2010 01:17   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ravi_1967 wrote on Mon, 08 March 2010 05:29
declare
cursor c1 is
select pname from '||hello||' ------->> compiler shows error this point
First, check your quotes. Their placement don't make any sense at all.
Second: you cannot use variables for objects like that. If you want to have the tablename as a variable in your query, you have to use dynamic sql.
Since dynamic SQL is way harder to get right, debug, etc than regular SQL and PL/SQL, I would stay far away from it for now if I were you. First try to get the hang of regular stuff.
|
|
|
Re: Any body help us.............? [message #446416 is a reply to message #446383] |
Mon, 08 March 2010 04:10   |
ravi_1967
Messages: 5 Registered: March 2010 Location: Mumbai
|
Junior Member |
|
|
Same as apply in other procedure. but partition variable used like
declare
tname varchar2(100);
counter number(10):=1;
BEGIN
LOOP
EXIT WHEN counter=10+1;
tname :='a_name_P='||counter;
EXECUTE IMMEDIATE (select a,b,c from '|| tname ||') a
where a.invoice_date between
a.issue_sdt and a.issue_edt
and a.status not in('B','C');
counter := counter + 1;
end loop;
end;
|
|
|
|
Re: Any body help us.............? [message #446589 is a reply to message #446416] |
Tue, 09 March 2010 07:58   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
ravi_1967 wrote on Mon, 08 March 2010 05:10
tname :='a_name_P='||counter;
EXECUTE IMMEDIATE (select a,b,c from '|| tname ||') a
And what do you think this will actually translate to?
|
|
|
Re: Any body help us.............? [message #446617 is a reply to message #446416] |
Tue, 09 March 2010 11:04  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Like I said before: try to learn plain vanilla SQL. After that try to learn some decent PL/SQL. Then practice until you master PL/SQL.
And then you will find you don't need Dynamic SQL 99% of the time..
|
|
|