I'm using Oracle SQL developer making PL/SQL code. My code is to print the data by using a mathematical formula in DBMS_Output. However, codes starting with DBMS_OUTPUT.PUT_LINE is all grey and doesn't print anything. I'm already using DBMS output by clicking view -> DBMS output -> plus -> connect to the server so I don't think disabled DBMS output is not the problem. Can you help me out?
DECLARE
type Receipt is record(
productID PRODUCTS.PRODUCTID%type,
productName PRODUCTS.PRODUCTNAME%type,
numOfProduct PRODUCTS.UNITSONORDER%type,
unitPrice PRODUCTS.UNITPRICE%type);
receipt Receipt;
BEGIN
select PRODUCTID,PRODUCTNAME, UNITSONORDER, UNITPRICE
into receipt
from PRODUCTS where PRODUCTID = 1;
DBMS_OUTPUT.PUT_LINE('Product ID : '|| receipt.productID);
DBMS_OUTPUT.PUT_LINE('Product Name : '|| receipt.productName);
DBMS_OUTPUT.PUT_LINE('Number of orders : '|| receipt.numOfProduct);
DBMS_OUTPUT.PUT_Line('Unit price : '|| receipt.unitPrice);
DBMS_OUTPUT.PUT_Line('sub-total'|| receipt.unitPrice);
DBMS_OUTPUT.PUT_Line('Taxes = '|| receipt.unitPrice*(13/100));
DBMS_OUTPUT.PUT_Line('Total Payment = '|| receipt.unitPrice*(13/100) (receipt.unitPrice));
END;
CodePudding user response:
You should be getting a number of syntax errors when you attempt to run this code because you can't declare a record type receipt
and declare a local variable receipt
. Only one declaration of receipt
is allowed. Perhaps you're coming from a programming language that is case-sensitive where Receipt
and receipt
would be different identifiers?
If we change the type declaration to a different identifier, receipt_rec
, then your code mostly compiles. You just have an issue on the final dbms_output
call because Oracle thinks you're trying to add everything to the left of the
sign to the value on the right of the
sign rather than doing the arithmetic and then concatenating that to the string text. I'd wrap the whole calculation in a to_char
but it would be sufficient just to add parenthesis to force the order of operations you want. Normally, it would make more sense to do the numeric calculation into a separate local variable and just print that local variable out.
DECLARE
type Receipt_rec is record(
productID PRODUCTS.PRODUCTID%type,
productName PRODUCTS.PRODUCTNAME%type,
numOfProduct PRODUCTS.UNITSONORDER%type,
unitPrice PRODUCTS.UNITPRICE%type);
receipt Receipt_rec;
BEGIN
select PRODUCTID,PRODUCTNAME, UNITSONORDER, UNITPRICE
into receipt
from PRODUCTS where PRODUCTID = 1;
DBMS_OUTPUT.PUT_LINE('Product ID : '|| receipt.productID);
DBMS_OUTPUT.PUT_LINE('Product Name : '|| receipt.productName);
DBMS_OUTPUT.PUT_LINE('Number of orders : '|| receipt.numOfProduct);
DBMS_OUTPUT.PUT_Line('Unit price : '|| receipt.unitPrice);
DBMS_OUTPUT.PUT_Line('sub-total'|| receipt.unitPrice);
DBMS_OUTPUT.PUT_Line('Taxes = '|| receipt.unitPrice*(13/100));
DBMS_OUTPUT.PUT_Line('Total Payment = '|| ( receipt.unitPrice*(13/100) (receipt.unitPrice) ));
END;
/
Here's a dbfiddle