Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with this package/body?
What is wrong with this package/body? [message #239228] Mon, 21 May 2007 16:06 Go to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
create or replace
PACKAGE BODY "LAWPERFORM"
IS
PROCEDURE lawperformbuild IS
permempfl Utl_File.file_type;

CURSOR checkrec IS

SELECT e.employee, e.first_name, e.last_name, e.email_address, e.employee, e.date_hired, e.position, e.pay_grade, d.department, d.r_name, p.locat_code, p.next_review, p.last_review, e.supervisor, e.supervisor_ind
from lawtest.employee e, lawtest.paemployee p, ios8.webuser w, lawtest.deptcode d
WHERE e.emp_status ='AF'
AND w.r_status ='1'
AND d.department = e.department
AND e.employee = p.employee;

BEGIN

permempfl := Utl_File.fopen('/oracle/files/dev1','testlawperform','w');
FOR erec IN checkrec LOOP
Utl_File.put_line(permempfl,
RPAD(e.employee,9,'0') ||
',' || RTRIM(e.first_name) ||
',' || RTRIM(e.last_name) ||
',' || RTRIM(e.email_address) ||
',' || RPAD(e.employee,12,'0') ||
',' ||
',' || TO_CHAR(e.date_hired,'mm/dd/yyyy') ||
',' || RTRIM(e.position) ||
',' || ',' || RTRIM(e.pay_grade) ||
',' || RTRIM(d.department) ||
',' || RTRIM(d.r_name) ||
',' ||RTRIM(p.locat_code) ||
',' || TO_CHAR(p.next_review,'mm/dd/yyyy') ||
',' || RTRIM(p.last_review,'mm/dd/yyyy') ||
',' || ',' || RTRIM(e.supervisor) ||
',' || RTRIM(e.supervisor_ind)|| ',' || ',');
End Loop;
Utl_File.fclose(permempfl);
END lawperformbuild;
END LAWPERFORM;
End;
Re: What is wrong with this package/body? [message #239230 is a reply to message #239228] Mon, 21 May 2007 16:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
please read & follow the #1 STICKY post at the top of this forum.

I give up.
what is wrong with the code?
I don't see any error?
Re: What is wrong with this package/body? [message #239234 is a reply to message #239228] Mon, 21 May 2007 16:51 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
It's not formatted? There are mixed-case keywords? There are quoted literals concatenated together for no clear reason?

There is what looks like a redundant "End;" on the last line.
Re: What is wrong with this package/body? [message #239296 is a reply to message #239228] Tue, 22 May 2007 00:08 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
I just want to select some data and write out a csv file. That is what the quoted literals are for. I am a newbie so there is probably a better way to write out a csv file.
Re: What is wrong with this package/body? [message #239298 is a reply to message #239296] Tue, 22 May 2007 00:12 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

William Robertson wrote
Quote:
There is what looks like a redundant "End;" on the last line.


As William Robertson pointed out do check the last line of your code.

please post cut and paste of your sql session. so it will be easy for people to see what is wrong and what the error message are?


regards
shanth..
Re: What is wrong with this package/body? [message #239301 is a reply to message #239298] Tue, 22 May 2007 00:23 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Attachment1 is the error message with the END; still in the line. Attachment2 is with the END; statement removed from the package.


[Mod-edit: replaced 800+ kB bmp by 16kB png]

[Updated on: Tue, 22 May 2007 00:38] by Moderator

Report message to a moderator

Re: What is wrong with this package/body? [message #239302 is a reply to message #239228] Tue, 22 May 2007 00:25 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Attachment2.

[Mod-edit: changed 800+ kB bmp to 23 kB png]

[Updated on: Tue, 22 May 2007 00:37] by Moderator

Report message to a moderator

Re: What is wrong with this package/body? [message #239310 is a reply to message #239302] Tue, 22 May 2007 00:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please don't use bandwidth-spoiling bmp format for posting screendumps, use jpg or png or the likes.
Better yet: use sqlplus and copy-paste the result.
Re: What is wrong with this package/body? [message #239328 is a reply to message #239296] Tue, 22 May 2007 01:05 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
silverbull wrote on Tue, 22 May 2007 06:08
I just want to select some data and write out a csv file. That is what the quoted literals are for. I am a newbie so there is probably a better way to write out a csv file.

I just meant I couldn't see any point in

',' || ','

when you could use

',,'

It won't make any difference in practice of course but it's needless complication.

I was also wondering what rule you use for uppercase or lowercase names and keywords, e.g:

END lawperformbuild;
END LAWPERFORM;
End;

Attachment1 ("the error message with the END; still in the line") clearly says "Encountered the symbol "END;" which suggests you should check what it's doing there.

Attachment2 ("with the END; statement removed from the package") is telling you that a table you referred to cannot be found (either it does not exist or you don't have permission to query it - if you check and it does exist then you are missing a direct grant: note that a grant via a role is not sufficient in stored PL/SQL). It is also saying it does not recognise "e.employee", so you should check the table lawtest.employee. (Does this "employee" table have a column called "employee"?)

[Updated on: Tue, 22 May 2007 01:06]

Report message to a moderator

Re: What is wrong with this package/body? [message #239330 is a reply to message #239228] Tue, 22 May 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe a stupid idea but why not just:
spool myreport
select ...
spool off

Regards
Michel
Re: What is wrong with this package/body? [message #239450 is a reply to message #239328] Tue, 22 May 2007 07:58 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Thanks for the suggestions. All of the tables are valid because I can take just the select statement and copy and paste it into SQLPLUS and it works fine. I am trying to compile this package in ORACLE SQL Developer.
Re: What is wrong with this package/body? [message #239456 is a reply to message #239450] Tue, 22 May 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And now it works or not?

Regards
Michel
Re: What is wrong with this package/body? [message #239457 is a reply to message #239456] Tue, 22 May 2007 08:12 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Sorry, no it does not work in Developer.
Re: What is wrong with this package/body? [message #239471 is a reply to message #239457] Tue, 22 May 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the error is?

Regards
Michel
Re: What is wrong with this package/body? [message #239506 is a reply to message #239228] Tue, 22 May 2007 09:33 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Error(6,1): PL/SQL: SQL Statement ignored
Error(24,13): PL/SQL: ORA-00942: table or view does not exist
Error(37,7): PL/SQL: Statement ignored

I can run the Select statement in SQL worksheet and it is fine. I do wonder if it is something wrong with not having a direct grant.
Re: What is wrong with this package/body? [message #239520 is a reply to message #239506] Tue, 22 May 2007 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the same in SQL*Plus and post it here. I don't know what are lines 6, 24, 37.
Of course with the same user in the same database...

Regards
Michel
Re: What is wrong with this package/body? [message #239523 is a reply to message #239228] Tue, 22 May 2007 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT e.employee, e.first_name, e.last_name, e.email_address, e.employee, e.date_hired, e.position, e.pay_grade, d.department, d.r_name, p.locat_code, p.next_review, p.last_review, e.supervisor, e.supervisor_ind
from lawtest.employee e, lawtest.paemployee p, ios8.webuser w, lawtest.deptcode d
WHERE e.emp_status ='AF'
AND w.r_status ='1'
AND d.department = e.department
AND e.employee = p.employee;


ios8.webuser w should be eliminated out of the FROM clause for two different reasons
1) it contributes nothing to the SELECT clause
2) it is NOT joined to any other table in the WHERE clause creating a cartesian product.
Re: What is wrong with this package/body? [message #239542 is a reply to message #239523] Tue, 22 May 2007 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not that, the create package where you have an error.
Just post the same thing you executed with SQL Developer.

Regards
Michel
Re: What is wrong with this package/body? [message #239550 is a reply to message #239542] Tue, 22 May 2007 11:11 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Error(6,3): PL/SQL: SQL Statement ignored
Error(23,13): PL/SQL: ORA-00942: table or view does not exist
Error(31,7): PL/SQL: Statement ignored
Error(31,43): PLS-00201: identifier 'E.EMPLOYEE' must be declared


CREATE OR REPLACE PACKAGE BODY dev1."LAWPERFORM" IS PROCEDURE lawperformbuild IS permempfl utl_file.file_type;

CURSOR checkrec IS

SELECT e.employee,
e.first_name, e.last_name,
e.email_address,
e.employee,
e.date_hired,
e.POSITION,
e.pay_grade,
d.department,
d.r_name,
p.locat_code,
p.next_review,
p.last_review,
e.supervisor,
e.supervisor_ind
FROM lawtest.employee e,
lawtest.paemployee p,
lawtest.deptcode d
WHERE d.department = e.department
AND e.employee = p.employee;

BEGIN
permempfl := utl_file.fopen('/oracle/files/dev1', 'testlawperform', 'w');
FOR erec IN checkrec
LOOP
utl_file.PUT_LINE(permempfl, RPAD(e.employee, 9, '0') || ',' || RTRIM(e.first_name) || ',' || RTRIM(e.last_name) || ',' || RTRIM(e.email_address) || ',' || rpad(e.employee, 12, '0') || ',' || ',' || to_char(e.date_hired, 'mm/dd/yyyy') || ',' || RTRIM(e.POSITION) || ',' || ',' || RTRIM(e.pay_grade) || ',' || RTRIM(d.department) || ',' || RTRIM(d.r_name) || ',' || RTRIM(p.locat_code) || ',' || to_char(p.next_review, 'mm/dd/yyyy') || ',' || RTRIM(p.last_review, 'mm/dd/yyyy') || ',' || ',' || RTRIM(e.supervisor) || ',' || RTRIM(e.supervisor_ind) || ',' || ',');
END LOOP;
Error(31,43): PLS-00201: identifier 'E.EMPLOYEE' must be declared
utl_file.fclose(permempfl);
END lawperformbuild;
END lawperform;
Re: What is wrong with this package/body? [message #239552 is a reply to message #239550] Tue, 22 May 2007 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DO IT IN SQL*PLUS.
Is this so difficult to do it?

Regards
Michel
Re: What is wrong with this package/body? [message #239557 is a reply to message #239552] Tue, 22 May 2007 11:43 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Someone else also misunderstood you.

SQL> create or replace
2 PACKAGE BODY LAWPERFORM IS PROCEDURE lawperformbuild IS permempfl utl_file.file
_type;
3
4 CURSOR checkrec IS
5
6 SELECT e.employee,
7 e.first_name,
8 e.last_name,
9 e.email_address,
10 e.employee,
11 e.date_hired,
12 e.POSITION,
13 e.pay_grade,
14 d.department,
15 d.r_name,
16 p.locat_code,
17 p.next_review,
18 p.last_review,
19 e.supervisor,
20 e.supervisor_ind
21 FROM lawtest.employee e,
22 lawtest.paemployee p,
23 lawtest.deptcode d
24 WHERE d.department = e.department
25 AND e.employee = p.employee;
26
27 BEGIN
28 permempfl := utl_file.fopen('/oracle/files/dev1', 'testlawperform', 'w');
29 FOR erec IN checkrec
30 LOOP
31 utl_file.PUT_LINE(permempfl, RPAD(e.employee, 9, '0') || ',' || RTRIM(e.first_name)
|| ',' || RTRIM(e.last_name) || ',' || RTRIM(e.email_address) || ',' || rpad(e.employee, 12, '0
') || ',' || ',' || to_char(e.date_hired, 'mm/dd/yyyy') || ',' || RTRIM(e.POSITION) || ',' || ','
|| RTRIM(e.pay_grade) || ',' || RTRIM(d.department) || ',' || RTRIM(d.r_name) || ',' || RTRIM(p.loca
t_code) || ',' || to_char(p.next_review, 'mm/dd/yyyy') || ',' || RTRIM(p.last_review, 'mm/dd/yyy
y') || ',' || ',' || RTRIM(e.supervisor) || ',' || RTRIM(e.supervisor_ind) || ',' || ',');
32 END LOOP;
33
34 utl_file.fclose(permempfl);
35 END LAWPERFORMBUILD;
36 END LAWPERFORM;
Re: What is wrong with this package/body? [message #239559 is a reply to message #239557] Tue, 22 May 2007 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There see no error.
And format your post: How to format your posts

Regards
Michel
Re: What is wrong with this package/body? [message #239566 is a reply to message #239559] Tue, 22 May 2007 12:22 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Here is the error.
SQL> create or replace
2 PACKAGE BODY LAWPERFORM IS PROCEDURE lawperformbuild IS permempfl utl_file.file
_type;
3
4 CURSOR checkrec IS
5
6 SELECT e.employee,
7 e.first_name,
8 e.last_name,
9 e.email_address,
10 e.employee,
11 e.date_hired,
12 e.POSITION,
13 e.pay_grade,
14 d.department,
15 d.r_name,
16 p.locat_code,
17 p.next_review,
18 p.last_review,
19 e.supervisor,
20 e.supervisor_ind
21 FROM lawtest.employee e,
22 lawtest.paemployee p,
23 lawtest.deptcode d
24 WHERE d.department = e.department
25 AND e.employee = p.employee;
26
27 BEGIN
28 permempfl := utl_file.fopen('/oracle/files/dev1', 'testlawperform', 'w');
29 FOR erec IN checkrec
30 LOOP
31 utl_file.PUT_LINE(permempfl, RPAD(e.employee, 9, '0') || ',' || RTRIM(e.first_name)
|| ',' || RTRIM(e.last_name) || ',' || RTRIM(e.email_address) || ',' || rpad(e.employee, 12, '0
') || ',' || ',' || to_char(e.date_hired, 'mm/dd/yyyy') || ',' || RTRIM(e.POSITION) || ',' || ','
|| RTRIM(e.pay_grade) || ',' || RTRIM(d.department) || ',' || RTRIM(d.r_name) || ',' || RTRIM(p.loca
t_code) || ',' || to_char(p.next_review, 'mm/dd/yyyy') || ',' || RTRIM(p.last_review, 'mm/dd/yyy
y') || ',' || ',' || RTRIM(e.supervisor) || ',' || RTRIM(e.supervisor_ind) || ',' || ',');
32 END LOOP;
33
34 utl_file.fclose(permempfl);
35 END LAWPERFORMBUILD;
36 END LAWPERFORM;
37 /

Warning: Package Body created with compilation errors.

SQL> execute lawperform.lawperformbuild
BEGIN lawperform.lawperformbuild; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "QSEXTON.LAWPERFORM" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
Re: What is wrong with this package/body? [message #239569 is a reply to message #239566] Tue, 22 May 2007 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Till you follow the link I posted to format your post, I'll not help you.

Execute "show errors" to show the error after create the package. No need to execute it as it fails to compile.

Regards
Michel
Re: What is wrong with this package/body? [message #239570 is a reply to message #239228] Tue, 22 May 2007 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Michel,
For your sanity & blood pressure, I suggest you add the OP to your ignore list as he seems extremely clue resistant.

HAND!
Re: What is wrong with this package/body? [message #239571 is a reply to message #239506] Tue, 22 May 2007 12:29 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
silverbull wrote on Tue, 22 May 2007 15:33
Error(6,1): PL/SQL: SQL Statement ignored
Error(24,13): PL/SQL: ORA-00942: table or view does not exist
Error(37,7): PL/SQL: Statement ignored

I can run the Select statement in SQL worksheet and it is fine. I do wonder if it is something wrong with not having a direct grant.


Yes, if you can query the table from the comand line (where roles are enabled) but not from stored PL/SQL (where they are not enabled) then your access is via a role and you need a direct grant. that is why I said

William Robertson wrote on Tue, 22 May 2007 07:05
Attachment2 ("with the END; statement removed from the package") is telling you that a table you referred to cannot be found (either it does not exist or you don't have permission to query it - if you check and it does exist then you are missing a direct grant: note that a grant via a role is not sufficient in stored PL/SQL). It is also saying it does not recognise "e.employee", so you should check the table lawtest.employee. (Does this "employee" table have a column called "employee"?)
Re: What is wrong with this package/body? [message #239578 is a reply to message #239571] Tue, 22 May 2007 12:45 Go to previous messageGo to next message
silverbull
Messages: 11
Registered: May 2007
Junior Member
Thanks William. I contacted my DBA after your first post about Direct Grants.
Re: What is wrong with this package/body? [message #239579 is a reply to message #239570] Tue, 22 May 2007 12:52 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ana,

Your post always goes down my pressure I count on you to keep me in good health. Smile

Regards
Michel
Previous Topic: CAN I PASS A TABLE NAME AS A VARIABLE IN THE FROM CLAUSE?
Next Topic: Deduction accrued balances
Goto Forum:
  


Current Time: Sun Dec 11 02:15:28 CST 2016

Total time taken to generate the page: 0.04690 seconds