Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Error..not able to find it out.... (oracle 10g)
Cursor Error..not able to find it out.... [message #362727] Thu, 04 December 2008 00:26 Go to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Hi,
I created a cursor ,it was working the other day (just changed the cursor stament with groupby func)
But, Today I got the error.Would you very help if you can let me know the solution.
~~~~~~~~~~Cursor ~~~~~~~~
create or replace procedure Sample_1 as
cursor c_f is
Select min(a.AP) , max(a.AP_ID) , b.DC_SNO, b.DC_ML
FROM Table1 a JOIN Table2 b
ON (a.DC_ID = b.DC_ID)
Where DC_T = 'XXXX' GROUP BY b.DC_SNO, b.DC_ML ;
v_a char(15);
v_b char(15);
v_c varchar2(32);
v_d varchar2(24);
begin
open c_f;
loop
fetch c_f into v_a,v_b,v_c,v_d;
exit when c_f%notfound;
INSERT INTO T_S1 (MIN_AP,MAX_AP,DC_SNO,DC_ML) VALUES (v_a,v_b,v_c,v_d);
end loop;
close c_f;
end;
Re: Cursor Error..not able to find it out.... [message #362729 is a reply to message #362727] Thu, 04 December 2008 00:28 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Quote:
But, Today I got the error.


What is the error ?

Also please follow posting rules, use tags to post code

[Updated on: Thu, 04 December 2008 00:28]

Report message to a moderator

Re: Cursor Error..not able to find it out.... [message #362731 is a reply to message #362727] Thu, 04 December 2008 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: Cursor Error..not able to find it out.... [message #362735 is a reply to message #362731] Thu, 04 December 2008 00:35 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
17 end loop;
18 close c_f;
19 end;
20 /

Warning: Procedure created with compilation errors.

SQL> exec Sample_1
BEGIN Sample_1; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object DBName.SAMPLE_1 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>
This is the Error I got, But for the previous cursor, it executed and inserted the rows in the needed table.
So my doubt is whether we can have the groupby func (min, max)can be used in the cursor statment with the tbles joins.

Thanks,
Sowmya
Re: Cursor Error..not able to find it out.... [message #362739 is a reply to message #362735] Thu, 04 December 2008 00:43 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Please post the previous cursor and the current one in properly formatted tags.
Re: Cursor Error..not able to find it out.... [message #362741 is a reply to message #362727] Thu, 04 December 2008 00:45 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
The other Cursor executed as

12 INSERT INTO T_S1 (MIN_ASUP,MAX_ASUP,DVC_SERIALNO,DVC_MODEL) VALUES (v_a,v_b,v_c,v_d);
13 end loop;
14 close c_f;
15 end;
16 /

Procedure created.

SQL> exec TEST_s

PL/SQL procedure successfully completed.

SQL> select count(*) from T_S1;

COUNT(*)
----------
19

But the prb with the other proc only..... "as error"
Thanks,
Sowmya
Re: Cursor Error..not able to find it out.... [message #362743 is a reply to message #362741] Thu, 04 December 2008 00:50 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thanks !pls find the below cursor and the error are pasted previously.

PREVIOUS CURSOR~~~~~~~~~

create or replace procedure TEST_s as
cursor c_f is select MIN_AP,MAX_AP,DC_SNO,DC_ML from TableA where rownum <20;
v_a char(15);
v_b char(15);
v_c varchar2(32);
v_d varchar2(24);
begin
open c_f;
loop
fetch c_f into v_a,v_b,v_c,v_d;
exit when c_f%notfound;
INSERT INTO T_S1 (MIN_AP,MAX_AP,DC_SNO,DC_ML) VALUES (v_a,v_b,v_c,v_d);
end loop;
close c_f;
end;

NEW CURSOR~~~~~~~

create or replace procedure Sample_1 as
cursor c_f is
Select min(a.AP) , max(a.AP_ID) , b.DC_SNO, b.DC_ML
FROM Table1 a JOIN Table2 b
ON (a.DC_ID = b.DC_ID)
Where DC_T = 'XXXX' GROUP BY b.DC_SNO, b.DC_ML ;
v_a char(15);
v_b char(15);
v_c varchar2(32);
v_d varchar2(24);
begin
open c_f;
loop
fetch c_f into v_a,v_b,v_c,v_d;
exit when c_f%notfound;
INSERT INTO T_S1 (MIN_AP,MAX_AP,DC_SNO,DC_ML) VALUES (v_a,v_b,v_c,v_d);
end loop;
close c_f;
end;
Re: Cursor Error..not able to find it out.... [message #362744 is a reply to message #362741] Thu, 04 December 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the WHOLE stuff, the WHOLE session and post it FORMATTED.

By the way, if a procedure compiles with of course it can't be executed => use "show errors" after compilation.

Regards
Michel
Re: Cursor Error..not able to find it out.... [message #362745 is a reply to message #362741] Thu, 04 December 2008 00:52 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I am unable to analyse using bits and pieces of your code, please post the enitre code which is giving the error.
Re: Cursor Error..not able to find it out.... [message #362754 is a reply to message #362745] Thu, 04 December 2008 01:13 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
SQL> create or replace procedure TEST_s as
2 cursor c_f is select MIN_AP,MAX_AP,DC_SNO,DC_ML from T_Sample where rownum <20;
3 v_a char(15);
4 v_b char(15);
5 v_c varchar2(32);
6 v_d varchar2(24);
7 begin
8 open c_f;
9 loop
10 fetch c_f into v_a,v_b,v_c,v_d;
11 exit when c_f%notfound;
12 INSERT INTO T_S1 (MIN_AP,MAX_AP,DC_SNO,DC_ML) VALUES (v_a,v_b,v_c,v_d);
13 end loop;
14 close c_f;
15 end;
16 /

Procedure created.

SQL> exec TEST_s

PL/SQL procedure successfully completed.

SQL> select count(*) from T_S1;

COUNT(*)
----------
38
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~completed ~~~~~~~~~~~~~~~~~~
ERROR for the below one:

SQL> create or replace procedure Sample_1 as
2 cursor c_f is Select min(a.AP_ID) , max(a.AP_ID) , b.DC_SNO, b.DC_ML
3 FROM TABLEA a
4 JOIN TABLEB b
5 ON (a.DC_ID = b.DC_ID)
6 Where DVC_TYPE = 'SKK' GROUP BY b.DC_SNO, b.DC_ML ;
7 v_a char(15);
8 v_b char(15);
9 v_c varchar2(32);
10 v_d varchar2(24);
11 begin
12 open c_f;
13 loop
14 fetch c_f into v_a,v_b,v_c,v_d;
15 exit when c_f%notfound;
16 INSERT INTO T_S1 (MIN_AP,MAX_AP,DC_SNO,DC_ML) VALUES (v_a,v_b,v_c,v_d);
17 end loop;
18 close c_f;
19 end;
20 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE SAMPLE_1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/15 PL/SQL: SQL Statement ignored
4/6 PL/SQL: ORA-00942: table or view does not exist
SQL> select count(*) from TableB;

COUNT(*)
----------
6950632

SQL> select count(*) from TableB;

COUNT(*)
----------
6947650

SQL>

I pasted from the sqlplus screen as I cound't copy the screen shot...
I have the Towo tables A and B in the DB..but cudn't understand the error!
Hopefuly this helps to understand the prb clearly...let me know incase of any info needed for the same.Thanks!

Re: Cursor Error..not able to find it out.... [message #362762 is a reply to message #362754] Thu, 04 December 2008 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are "tableA" and "tableB" tables owned by user which runs the procedure? Could it be, perhaps, that you've run into a lack of privileges issues? The fact that SQL*Plus SELECT statement returns something doesn't have to mean anything, because - if access to these objects was granted via role, you might need to grant it directly to user as privileges acquired via roles do not apply in PL/SQL procedures.
Re: Cursor Error..not able to find it out.... [message #362764 is a reply to message #362754] Thu, 04 December 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I could answer but as you are reluctant to read and follow guidelines i will not and I hope for you that someone else will be kind enough to help you despite your behaviour, I'm not.

Regards
Michel
Re: Cursor Error..not able to find it out.... [message #362766 is a reply to message #362727] Thu, 04 December 2008 01:40 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thanks !
Could you please let me know how to chk the access of the access of these 2 tables...
Whether by inserting a new row....or do we hae syntax chking for the same.....
pls let me know ...
and thank u so much for your reply....
Re: Cursor Error..not able to find it out.... [message #362851 is a reply to message #362766] Thu, 04 December 2008 05:29 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sowmyaa,

I am not sure what you are looking for. May be the link Authorization: Privileges, Roles, Profiles, and Resource Limitations helps.
Also have a look at DBA_TAB_PRIVS or USER_TAB_PRIVS

Hope the links helps you to understand @Littlefoot's reply.

Regards,
Jo

[Updated on: Thu, 04 December 2008 05:29]

Report message to a moderator

Re: Cursor Error..not able to find it out.... [message #362853 is a reply to message #362766] Thu, 04 December 2008 05:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
O.k this time I choose to help you but not from next time if you are so reluctant to follow the guide lines.

set role none;
exec <procedure_name>;
select count(*) from tableb;


See what you get.

Regards

Raj
Re: Cursor Error..not able to find it out.... [message #363053 is a reply to message #362853] Thu, 04 December 2008 23:27 Go to previous message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thanks for all your replies...
the prob was with the roles and previleges...
I created another table (with this table content)and executed and it got executed.

Thanks,
Sowmya
Previous Topic: Seperate Parts of English Sentence
Next Topic: Picture not saved
Goto Forum:
  


Current Time: Wed Dec 07 16:27:15 CST 2016

Total time taken to generate the page: 0.23947 seconds