Write PL/SQL or T-SQL procedures to accomplish the

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Write PL/SQL or T-SQL procedures to accomplish the

Author

Topic

Kaiokhen
Starting Member

1 Post

Posted - 2012-04-30 : 08:37:50

I am stuck with this problem and its giving me error. I cant find whats wrong with the code. The error says

Warning: Procedure created with compiled error

5. Write PL/SQL or T-SQL procedures to accomplish the following tasks:
a. Obtain the name and credit limit of the customer whose number currently is stored in
I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and
I_CREDIT_LIMIT, respectively. Output the contents of I_CUSTOMER_NAME and
I_CREDIT_LIMIT.

This is what i have

CREATE OR REPLACE PROCEDURE DISP_ORDER_NUM
(I_ORDER_NUM IN ORDERS.ORDER_NUM%TYPE) AS
I_ORDER_DATE ORDERS.ORDER_DATE%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;

BEGIN
SELECT ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME
INTO I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDER_NUM = I_ORDER_NUM
AND ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM;

DBMS_OUTPUT.PUT_LINE(I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME);

END;
/

b. Obtain the order date, customer number, and name for the order whose number currently
is stored in I_ORDER_NUM. Place these values in the variables I_ORDER_DATE,
I_CUSTOMER_NUM and I_CUSTOMER_NAME, respectively. Output the contents of
I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME.

This is what i have

CREATE OR REPLACE PROCEDURE DISP_ORDER_NUM (I_ORDER_NUM IN ORDERS.ORDER_NUM%TYPE) AS
I_ORDER_DATE ORDERS.ORDER_DATE%TYPE;
I_CUSTOMER_NUM CUSTOMER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME CUSTOMER.CUSTOMER_NAME%TYPE;

BEGIN
SELECT ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME
INTO I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME
FROM ORDERS, CUSTOMER
WHERE ORDER_NUM = I_ORDER_NUM
AND ORDERS.CUSTOMER_NUM = CUSTOMER.CUSTOMER_NUM;

DBMS_OUTPUT.PUT_LINE(I_ORDER_DATE, I_CUSTOMER_NUM, I_CUSTOMER_NAME);

END;
/

c. Add a row to the ORDERS table.

d. Change the date of the order whose number is stored in I_ORDER_NUM to the date
currently found in I_ORDER_DATE.

e. Delete the order whose number is stored in I_ORDER_NUM.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-30 : 09:19:58

1. SQLTeam is a Microsoft SQL Server web site. For Oracle questions try http://dbforums.com/
2. We don't answer homework, exam, or certification questions on SQLTeam.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 08:04:58

There is nothing more left to say.
Rob has said everything.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-01 : 09:15:29

quote:
Originally posted by vinu.vijayan

There is nothing more left to say.
Rob has said everything.

But don't let that stop you!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-01 : 09:29:45

quote:
Originally posted by robvolk

quote:
Originally posted by vinu.vijayan

There is nothing more left to say.
Rob has said everything.
But don't let that stop you!

Won't stop my quest for a higher post count either!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-02 : 00:25:45

quote:
Originally posted by DonAtWork

quote:
Originally posted by robvolk

quote:
Originally posted by vinu.vijayan

There is nothing more left to say.
Rob has said everything.
But don't let that stop you!

Won't stop my quest for a higher post count either!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources