Home > OS >  Error on DBMS output while printing text and calculated result using PL/SQL block
Error on DBMS output while printing text and calculated result using PL/SQL block

Time:10-14

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

  • Related