Home » SQL & PL/SQL » SQL & PL/SQL » SOS PL/SQL missing (10g running in xp pro)
icon8.gif  SOS PL/SQL missing [message #311447] Fri, 04 April 2008 09:17 Go to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi all
I need your help please

I have my Overtime procedure
DECLARE
mytime_declared TIMESTAMP(9);
mytime_declared TIMESTAMP;
v_HoursWorked Number := 50 ;
v_OverTime Number := 0 ;
BEGIN
IF v_HoursWorked > 40 THEN
v_OverTime := v_HoursWorked - 40;
DBMS_OUTPUT.PUT_LINE('Hours overtime worked = ' || v_OverTime);
END IF;
END;

Here my Attendance table
CREATE TABLE attendance(
at_id number(15),
time_in number(6,2),
time_out number(6,2),
s_id NUMBER (15) references staff (s_id),
absent_late VARCHAR2(7),
CONSTRAINT absent_late_chk CHECK (absent_late IN (
'Absent','Late','Present')));

Here my table staff
create table staff(
s_id number(15) primary key,
fname varchar2(30),
lname varchar2(30),
street varchar2(40),
city varchar2(20),
postcode varchar2(15),
phone varchar2(15),
dob date,
hourly_rate number(4,2),
min_no_of_hours number(4,2),
position varchar2(12),
grade char(1),
salary number(6,2),
otime_rate number(4,2),
status varchar2(15));


I may need to declare hours legaly work ? but may I have declare it in my procedure att_salaries ? or in my Attendance table ?
I don't know if my question is clear ?/ the goal is to know how many overtime someone did...

I was asking myself if I need an attendance procedure

create or replace procedure att_salaries(
fname_varchar2, lname varchar2)
AS
v_number(4);
fname varchar
v_order varchar2(8);
response varchar2(50);

begin
insert into attendance (at_id, time_in)
values ('time_out');

update (v_s_id,fname, lname,time_out);
DBMS_OUTPUT.PUT_LINE('Record Created');
END;
/

getting error
Warning: Procedure created with compilation errors.

Best regards

merci d'avance...
Re: SOS PL/SQL missing [message #311450 is a reply to message #311447] Fri, 04 April 2008 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I was asking myself if I need an attendance procedure
& what was the answer?

>Warning: Procedure created with compilation errors.
First fix the syntax errors & follow posting guideline is GOLD sticky post at top of forum.
icon11.gif  Re: SOS PL/SQL missing [message #311451 is a reply to message #311450] Fri, 04 April 2008 09:26 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi
my question is how to calculate overtime done by someone I don't know how many procedure I need exactly
Thanks
Re: SOS PL/SQL missing [message #311461 is a reply to message #311451] Fri, 04 April 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to calculate overtime done by someone I don't know how many procedure I need exactly

0 (zero).

Regards
Michel
icon4.gif  Re: SOS PL/SQL missing [message #311467 is a reply to message #311461] Fri, 04 April 2008 10:17 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi Michel
je suis perdu pouvez vous m'aider pls
j,ai une procedure pour calculer les heures sup mais comment ca marche ? avec des info que j'ai est que pouvez vous m'aider
ps: je suis tres nul

Hi all
I need your help please

I have my Overtime procedure
DECLARE
mytime_declared TIMESTAMP(9);
mytime_declared TIMESTAMP;
v_HoursWorked Number := 50 ;
v_OverTime Number := 0 ;
BEGIN
IF v_HoursWorked > 40 THEN
v_OverTime := v_HoursWorked - 40;
DBMS_OUTPUT.PUT_LINE('Hours overtime worked = ' || v_OverTime);
END IF;
END;

Here my Attendance table
CREATE TABLE attendance(
at_id number(15),
time_in number(6,2),
time_out number(6,2),
s_id NUMBER (15) references staff (s_id),
absent_late VARCHAR2(7),
CONSTRAINT absent_late_chk CHECK (absent_late IN (
'Absent','Late','Present')));

Here my table staff
create table staff(
s_id number(15) primary key,
fname varchar2(30),
lname varchar2(30),
street varchar2(40),
city varchar2(20),
postcode varchar2(15),
phone varchar2(15),
dob date,
hourly_rate number(4,2),
min_no_of_hours number(4,2),
position varchar2(12),
grade char(1),
salary number(6,2),
otime_rate number(4,2),
status varchar2(15));


I may need to declare hours legaly work ? but may I have declare it in my procedure att_salaries ? or in my Attendance table ?
I don't know if my question is clear ?/ the goal is to know how many overtime someone did...

I was asking myself if I need an attendance procedure

create or replace procedure att_salaries(
fname_varchar2, lname varchar2)
AS
v_number(4);
fname varchar
v_order varchar2(8);
response varchar2(50);

begin
insert into attendance (at_id, time_in)
values ('time_out');

update (v_s_id,fname, lname,time_out);
DBMS_OUTPUT.PUT_LINE('Record Created');
END;
/

getting error
Warning: Procedure created with compilation errors.

Best regards

merci d'avance...
Re: SOS PL/SQL missing [message #311470 is a reply to message #311467] Fri, 04 April 2008 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
je suis perdu pouvez vous m'aider pls
j,ai une procedure pour calculer les heures sup mais comment ca marche ? avec des info que j'ai est que pouvez vous m'aider
ps: je suis tres nul

Translation: I have a procedure to compute overtime but how does this work? With these information can you help me? I'm dumb.

Please keep it in english or post it in French forum below.
You first have to read the OraFAQ Forum Guide, especially "How to format your post?" section.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

What you posted is a mess, we don't have what are the inputs and should be the ouput, what are the criteria for overtime... Explain the meanings of your tables...

Regards
Michel
icon10.gif  Re: SOS PL/SQL missing [message #311473 is a reply to message #311470] Fri, 04 April 2008 10:41 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Well

what I mean is that the staff calling Pierre who supposed to work legaly 8 hs a day Monday to Friday but he worked 2hs Monday and 4 hs Tuesday.
which information I need to complete in my procedure overtime don't worry about my tables....
Re: SOS PL/SQL missing [message #311494 is a reply to message #311473] Fri, 04 April 2008 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Incomplete data to compute.

Regards
Michel
Re: SOS PL/SQL missing [message #311520 is a reply to message #311494] Fri, 04 April 2008 12:27 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

ha sorry michel i gave wrong information
I mean Pierre worked 10hs Monday and 12hs Tuesday which means he did 2hs as overtime Monday and 4 hs OT Tuesday
Re: SOS PL/SQL missing [message #311526 is a reply to message #311520] Fri, 04 April 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what do you expect we answer with what you posted?
So you have your answer: he did 2hs as overtime Monday and 4 hs OT Tuesday.

Regards
Michel
icon10.gif  Re: SOS PL/SQL missing [message #311539 is a reply to message #311526] Fri, 04 April 2008 12:54 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

I am not getting your logicale about it because in my mind I thought I need an ATTENDANCE TABLE( which I have to declare time_in number(6,2),
time_out number(6,2) and
an ATTENDANCE PROCEDURE which supposed to be something like.
create or replace procedure att_salaries(
fname_varchar2, lname varchar2)
AS
v_number(4);
s_id references staff (s_id);
fname varchar2(30);
v_order varchar2(8);
response varchar2(50);

begin
insert into attendance (at_id, time_in)
values ('time_out');

update (v_s_id,fname, lname,time_out);
DBMS_OUTPUT.PUT_LINE);
END;
/

and OVERTIME PROCEDURE like
DECLARE
mytime_declared TIMESTAMP(9);
mytime_declared TIMESTAMP;
v_HoursWorked Number := 50 ;
v_OverTime Number := 0 ;
BEGIN
IF v_HoursWorked > 40 THEN
v_OverTime := v_HoursWorked - 40;
DBMS_OUTPUT.PUT_LINE('Hours overtime worked = ' || v_OverTime);
END IF;
END;

It's always hard to get a logicale from new things we learn.
my dream is to get your logicale and next step doing what are you doing help people in this forum Laughing I will get it

[Updated on: Fri, 04 April 2008 13:25]

Report message to a moderator

Re: SOS PL/SQL missing [message #311544 is a reply to message #311539] Fri, 04 April 2008 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
because in my mind I thought I need an ATTENDANCE TABLE( which I have to declare ...

Quote:
don't worry about my tables....

WE have not to worry about YOUR tables but WE have to know that YOU are talking about a specific table that YOU have in mind and YOU know what is the meaning of columns but WE don't have to know it and WE have to find a procedure to get the result YOU want but YOU don't explain us where it comes from.
And YOU pollute us with a procedure that has nothing to do with what YOU are trying to find.
And YOU don't understand what I meant, what shoud I say?

In addition, you seems to not understand what formatting or reading the guidelines means.

Quote:
It's always hard to get a logicale from new things we learn.

Don't explain what you don't know, first post the specifications then we will try to see how you can do it.

Regards
Michel
icon1.gif  Re: SOS PL/SQL missing [message #311661 is a reply to message #311539] Sat, 05 April 2008 10:17 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi all
getting error with my procedure

SQL> Declare
2 my_ovt overtime_sessions%ROWTYPE;
3 Begin
4 select *
5 into my_ovt
6 from overtime_sessions
7 where v_hours = 'overtime_sessions';
8
9 if my_ovt.hours like '%staffs%'
10 then
11 DBMS_OUTPUT.put_line ('The payment_made is'||my_ovt.payment_made);
12 END IF;
13 END;
14 /
where v_hours = 'overtime_sessions';
*
Here errors

ERROR at line 7:
ORA-06550: line 7, column 8:
PL/SQL: ORA-00904: "V_HOURS": invalid identifier
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored
Re: SOS PL/SQL missing [message #311666 is a reply to message #311447] Sat, 05 April 2008 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
contains posting guidelines. Please follow them!

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

You need to read & study how to write correct PL/SQL syntax.
Re: SOS PL/SQL missing [message #311667 is a reply to message #311544] Sat, 05 April 2008 10:52 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi Michel,
I am soory for last time was feeling too mach pressure from job will pay more attention next time
Re: SOS PL/SQL missing [message #311675 is a reply to message #311666] Sat, 05 April 2008 11:33 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

I changed it to
Where Hours = 'staff overtime';

getting this error
ERROR at line 1:
ORA-01403: no data found <------- doest it I have to populate my table ??
ORA-06512: at line 4
Re: SOS PL/SQL missing [message #311680 is a reply to message #311675] Sat, 05 April 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A new table you get off your hat.
Try again.

Regards
Michel
icon5.gif  Re: SOS PL/SQL missing [message #311788 is a reply to message #311447] Sun, 06 April 2008 18:27 Go to previous messageGo to next message
rolmau
Messages: 18
Registered: March 2008
Location: UK
Junior Member

Hi all
my procedure to calculate VAT getting problem !
DECLARE
e_vat number;
e_tt number;
cursor ord_row
is select cost, quantity
from e_product, e_ord_line
where e_product.product_no = e_ord_line.product_no
and e_ordline_details.order_no = order_details.order_no;
Begin
commit_form;
e_vat:=0;
e_tt:=0;
for o_line in ord_row loop
e_vat :=(o_line.cost*o_line.quantity*0.175);
e_tt :=e_vat+(o_line.cost*o_line.quantity);
end loop;
:order_details.vat:=e_vat;
:order_details.total:=e_tt;
end;
/
Getting this errors Where I make a mistake ????/
SP2-0552: Bind variable "ORDER_DETAILS" not declared.

[Updated on: Sun, 06 April 2008 19:37]

Report message to a moderator

Re: SOS PL/SQL missing [message #311946 is a reply to message #311788] Mon, 07 April 2008 08:32 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
1. Is this related to the original question?
2. Is this a FORMS question or a PL/SQL question?
3. You still haven't posted in a properly formatted manner. Is the pressure of your job still a factor?
Re: SOS PL/SQL missing [message #312042 is a reply to message #311447] Mon, 07 April 2008 20:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel, why did you have to do the translation for us? I was enjoying the thought that it was a really juicy french swear.

Kevin
Re: SOS PL/SQL missing [message #312249 is a reply to message #312042] Tue, 08 April 2008 05:10 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry to ruin your pleasure. Smile

Regards
Michel
Previous Topic: How to store image files in database
Next Topic: few clarifications
Goto Forum:
  


Current Time: Fri Dec 09 15:29:50 CST 2016

Total time taken to generate the page: 0.32708 seconds