Home » RDBMS Server » Server Administration » Please help us ....
| Please help us .... [message #371816] |
Sun, 10 December 2000 01:25  |
asma
Messages: 12
Registered: December 2000
|
Junior Member |
|
|
When we execute this procedure it doesn't go through if clause it goes directly to else clause under any codition.....
create or replace
procedure fidhall(
p_name IN VARCHAR2,
p_phone IN NUMBER,
p_country IN VARCHAR2,
p_city IN VARCHAR2,
p_address IN VARCHAR2,
p_email IN VARCHAR2 DEFAULT null,
p_credittype IN VARCHAR2,
p_creditno IN NUMBER,
p_creditexp IN DATE,
p_nameoncard IN VARCHAR2,
p_meetingdate IN DATE,
p_noguest IN NUMBER,
p_hallname IN VARCHAR2,
p_puffet IN CHAR)
is
begin
declare
NEWHALLNAME HALL.HALLNAME%type;
cursor FINDHALL is
select H_HALLNAME
FROM HRESERVATION
WHERE HRESERVATION.H_HALLNAME = P_HALLNAME AND
HRESERVATION.WEDDINGDATE != P_MEETINGDATE ;
BEGIN
OPEN FINDHALL ;
LOOP
FETCH FINDHALL INTO NEWHALLNAME ;
EXIT WHEN FINDHALL%ROWCOUNT = 1 OR FINDHALL%NOTFOUND;
END LOOP;
IF FINDHALL%ROWCOUNT = 1 THEN
INSERT INTO CUSTOMER(NAME,PHONE,COUNTRY,CITY,ADDRESS,EMAIL,CREDITTYPE,CREDITNO,
CREDITEXP,NAMEONCARD,CONFIRMATION#)
VALUES
(P_NAME,P_PHONE,P_COUNTRY,P_CITY,P_ADDRESS,P_EMAIL,
P_CREDITTYPE,P_CREDITNO,P_CREDITEXP,P_NAMEONCARD,
CUST_CONF#.NEXTVAL );
INSERT INTO HRESERVATION(WEDDINGDATE,NOGUEST,CUSTNAME,CUSTCONFIRMATION#,H_HALLNAME)
VALUES (P_MEETINGDATE,P_NOGUEST,P_NAME,P_HALLNAME,
CUST_CONF#.CURRVAL);
ELSE
HTP.P('This hall is not available in this date, thank you');
END IF;
END;
END;
thanx....
|
|
|
|
| Re: Please help us .... [message #371821 is a reply to message #371816] |
Mon, 11 December 2000 03:16  |
SQL_Tuner
Messages: 8
Registered: November 2000
|
Junior Member |
|
|
You can debug your program with the PUT_LINE procedure. Before the code runs enter:
SET SERVEROUTPUT ON
Then, in the procedure, add:
DBMS_OUTPUT.PUT_LINE (FINDHALL%rowcount)
before and after the FETCH. You should quickly be able to determine what's going on, then.
Regards,
ST
|
|
|
|
| Re: Please help us .... [message #371822 is a reply to message #371821] |
Mon, 11 December 2000 13:37 |
kovai sriram
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
Try this and let me see the results.
create or replace
procedure fidhall(
p_name IN VARCHAR2,
p_phone IN NUMBER,
p_country IN VARCHAR2,
p_city IN VARCHAR2,
p_address IN VARCHAR2,
p_email IN VARCHAR2 DEFAULT null,
p_credittype IN VARCHAR2,
p_creditno IN NUMBER,
p_creditexp IN DATE,
p_nameoncard IN VARCHAR2,
p_meetingdate IN DATE,
p_noguest IN NUMBER,
p_hallname IN VARCHAR2,
p_puffet IN CHAR)
is
begin
declare
NEWHALLNAME HALL.HALLNAME%type;
cursor FINDHALL is
select H_HALLNAME
FROM HRESERVATION
WHERE HRESERVATION.H_HALLNAME = P_HALLNAME AND
HRESERVATION.WEDDINGDATE != P_MEETINGDATE ;
BEGIN
OPEN FINDHALL ;
IF FINDHALL%FOUND THEN
LOOP
FETCH FINDHALL INTO NEWHALLNAME ;
EXIT WHEN FINDHALL%ROWCOUNT > 0;
END LOOP;
INSERT INTO
CUSTOMER(NAME,PHONE,COUNTRY,CITY,ADDRESS,EMAIL,CREDITTYPE,CREDITNO,
CREDITEXP,NAMEONCARD,CONFIRMATION#)
VALUES
(P_NAME,P_PHONE,P_COUNTRY,P_CITY,P_ADDRESS,P_EMAIL,
P_CREDITTYPE,P_CREDITNO,P_CREDITEXP,P_NAMEONCARD,
CUST_CONF#.NEXTVAL );
INSERT INTO
HRESERVATION(WEDDINGDATE,NOGUEST,CUSTNAME,CUSTCONFIRMATION#,H_HALLNAME)
VALUES (P_MEETINGDATE,P_NOGUEST,P_NAME,P_HALLNAME,
CUST_CONF#.CURRVAL);
ELSE
HTP.P('This hall is not available in this date, thank you');
END IF;
END;
END;
|
|
|
|
|
|
| Re: Please help us .... [message #371828 is a reply to message #371816] |
Tue, 12 December 2000 09:03 |
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
All right, here is the answer.
From your code it looks like you are trying to see if the hall exists and if exists check the time.
Since the combination of hallname and meeting time is unique (has to be), rather than selecting h_hallname in your cursor try to select count(*), reason being, count(*) returns a number (does not matter what number, pratically it has to be either 1 or 0) if records are found and 0 (zero) if no records found.
This will solve your problem.
Then all you have to do is open the cursor, fetch count(*) value into a variable, close the cursor.
You do not need a cursor loop as you fetch only one record or row from the database.
After close cursor write your insert statements using if then else statement.
I can send the answer but try doing it and if you have problem let me know.
Shanthi
|
|
|
|
| Updated: [message #371832 is a reply to message #371816] |
Tue, 12 December 2000 09:33 |
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Asma, I did not notice previously, your cursor declaration is wrong too. You are openning cursor with where clause and checking againist a value but your cursor is not taking and arguments??!?
Your cursor should look like this
cursor FINDHALL(c_hallname, c_meetingdate) is
select H_HALLNAME
FROM HRESERVATION
WHERE HRESERVATION.H_HALLNAME = c_HALLNAME AND
HRESERVATION.WEDDINGDATE != c_MEETINGDATE ;
and when you open you should use
open findhall(p_hallname, p_meetingdate);
Also there are unnecessary begins and end like the one before declare, why do you need this??!??
And your program is going into else clause always as you are cursor is never open due to declaration problems. Please check the syntax all the way thru.
Shanthi
|
|
|
|
| Re: Please help us .... [message #371833 is a reply to message #371821] |
Tue, 12 December 2000 10:40 |
kovai sriram
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
Asma, I have changed a bit. Try this . It should work. Get back with the errors u get.
create or replace
procedure fidhall(
p_name IN VARCHAR2,
p_phone IN NUMBER,
p_country IN VARCHAR2,
p_city IN VARCHAR2,
p_address IN VARCHAR2,
p_email IN VARCHAR2 DEFAULT null,
p_credittype IN VARCHAR2,
p_creditno IN NUMBER,
p_creditexp IN DATE,
p_nameoncard IN VARCHAR2,
p_meetingdate IN DATE,
p_noguest IN NUMBER,
p_hallname IN VARCHAR2,
p_puffet IN CHAR)
is
begin
declare
NEWHALLNAME HALL.HALLNAME%type;
cursor FINDHALL(p_hallname,p_meetingdate) is
select H_HALLNAME FROM HRESERVATION WHERE HRESERVATION.H_HALLNAME = P_HALLNAME AND
HRESERVATION.WEDDINGDATE != P_MEETINGDATE ;
BEGIN
OPEN FINDHALL(p_hallname,p_meetingdate);
FETCH FINDHALL INTO NEWHALLNAME ;
dbms_output.put_line(findhall%rowcount);
IF FINDHALL%ROWCOUNT > 0 THEN
INSERT INTO CUSTOMER(NAME,PHONE,COUNTRY,CITY,ADDRESS,EMAIL,CREDITTYPE,CREDITNO,
CREDITEXP,NAMEONCARD,CONFIRMATION#)
VALUES
(P_NAME,P_PHONE,P_COUNTRY,P_CITY,P_ADDRESS,P_EMAIL,
P_CREDITTYPE,P_CREDITNO,P_CREDITEXP,P_NAMEONCARD,
CUST_CONF#.NEXTVAL );
INSERT INTO HRESERVATION(WEDDINGDATE,NOGUEST,CUSTNAME,CUSTCONFIRMATION#,H_HALLNAME)
VALUES (P_MEETINGDATE,P_NOGUEST,P_NAME,P_HALLNAME,
CUST_CONF#.CURRVAL);
ELSE
HTP.P('This hall is not available in this date, thank you');
END IF;
END;
|
|
|
|
| Re: Updated: [message #371844 is a reply to message #371832] |
Wed, 13 December 2000 08:15 |
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Good job.
You receive Oracle error "Invalid Number" if you try to insert number data in a charater filed or vice a versa.
Check you second insert statement, the order of last two values is wrong. You are trying to insert hallname in custconfirmation# field and confirmation# in hallname field.
Shanthi
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 04 02:45:43 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
