Home » SQL & PL/SQL » SQL & PL/SQL » Any body help us.............? (oracle, 9i,unix)
icon5.gif  Any body help us.............? [message #446329] Sun, 07 March 2010 22:29 Go to next message
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 #446337 is a reply to message #446329] Sun, 07 March 2010 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>select tname from temp_dup_cdr_1 where partition_date between '01-feb-2010' and '01-mar-2010'; -- tname = partition table name

With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

EXACTLY what problem needs to be solved?
Re: Any body help us.............? [message #446347 is a reply to message #446337] Sun, 07 March 2010 23:23 Go to previous messageGo to next message
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 #446349 is a reply to message #446347] Sun, 07 March 2010 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

USE sqlplus along CUT & PASTE so we can see what you do & how Oracle responds.
Re: Any body help us.............? [message #446383 is a reply to message #446329] Mon, 08 March 2010 01:17 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #446417 is a reply to message #446416] Mon, 08 March 2010 04:16 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Or in other words, :
"I have no intention of taking any of the advice offerred above. Instead I am going to explicitly go against the advice so nyaa nyaa."
Re: Any body help us.............? [message #446589 is a reply to message #446416] Tue, 09 March 2010 07:58 Go to previous messageGo to next message
joy_division
Messages: 4644
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 Go to previous message
Frank
Messages: 7880
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..
Previous Topic: Materializd
Next Topic: Scheduling a stored procedure/package
Goto Forum:
  


Current Time: Sun Dec 11 04:22:25 CST 2016

Total time taken to generate the page: 0.13774 seconds