Show the output of a procedure in Oracle-CodePudding

The procedure uses the previous function to display the list of the products: num, designation and mention on the application.

SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION STORE(num_produit IN INTEGER) RETURN VARCHAR AS
    N INTEGER := 0;
    incre INTEGER := 0;
BEGIN
    SELECT SUM(qte) INTO N FROM Ligne_Fact WHERE num_produit = produit;
    IF N > 15 THEN
        RETURN 'fort';
    ELSIF N > 11 THEN
        RETURN 'moyen';
    END IF;
    RETURN 'faible';
END;
/
CREATE OR REPLACE PROCEDURE SHOW_PRODUITS IS
    SOME_VAR VARCHAR2(256);
BEGIN
   SELECT num, designation, STORE(num) INTO SOME_VAR FROM Produit;
    dbms_output.enable();
    dbms_output.put_line('result : '|| SOME_VAR);
END;
/
BEGIN
    SHOW_PRODUITS;
END;
/

I am sure that all the tables are filled with some dummy data, but I am getting the following error:

Function STOCKER compiled
Procedure AFFICHER_PRODUITS compiled
LINE/COL  ERROR
--------- -------------------------------------------------------------
4/4       PL/SQL: SQL Statement ignored
4/56      PL/SQL: ORA-00947: not enough values
Errors: check compiler log
Error starting at line : 28 in command -
BEGIN
    AFFICHER_PRODUITS;
END;
Error report -
ORA-06550: line 2, column 5:
PLS-00905: object SYSTEM.SHOW_PRODUITS is invalid
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

CodePudding user response：

The first major mistake you've made is to create your objects in SYSTEM schema. It, just like SYS, are special and should be used only for system maintenance. Create your own user and do whatever you're doing there.

As of your question: select returns 3 values, but you're trying to put them into a single some_var variable. That won't work. Either add another local variables (for num and designation), or remove these columns from the select:

CREATE OR REPLACE PROCEDURE SHOW_PRODUITS IS
    SOME_VAR VARCHAR2(256);
BEGIN
   SELECT STORE(num) INTO SOME_VAR FROM Produit;    --> here
    dbms_output.enable();
    dbms_output.put_line('result : '|| SOME_VAR);
END;
/

Code, as you put it, presumes that produit contains a single record (it can't be empty nor it can have 2 or more rows because you'll get various errors).

Maybe you wanted to access all rows; in that case, consider using a loop, e.g.

CREATE OR REPLACE PROCEDURE SHOW_PRODUITS IS
    SOME_VAR VARCHAR2(256);
BEGIN
   FOR cur_r IN (SELECT num, designation, STORE(num) some_var FROM Produit) LOOP
     dbms_output.put_line(cur_r.num ||', '|| cur_r.designation ||', result : '|| cur_r.some_var);
   END LOOP;
END;
/

Then

set serveroutput on
BEGIN
    SHOW_PRODUITS;
END;
/

CodePudding user response：

You are trying to compile some stored routine which calls another stored routine named AFFICHER_PRODUITS and that routine calls SHOW_PRODUITS but SHOW_PRODUITS does not compile, hence the error. (By the way, it is recommended not to create your own stored routines in the SYSTEM schema.)

SHOW_PRODUITS does not compile because of this line:

SELECT num, designation, STORE(num) INTO SOME_VAR FROM Produit;

It appears that you want to get the query results as a string. In order to do that, you need to concatenate the column values, i.e.

SELECT num || designation || STORE(num) INTO SOME_VAR FROM Produit;

Of-course if all you want to do is display the query results, using DBMS_OUTPUT, you can declare a separate variable for each column.

CREATE OR REPLACE PROCEDURE SHOW_PRODUITS IS
    SOME_NUM Produit.num%type;
    SOME_DES Produit.designation%type;
    SOME_VAR VARCHAR2(6);
BEGIN
    SELECT num
          ,designation
          ,STORE(num)
      INTO SOME_NUM
          ,SOME_DES
          ,SOME_VAR
      FROM Produit;
    dbms_output.enable();
    dbms_output.put_line('result : ' || SOME_NUM || SOME_DES || SOME_VAR);
END;

Note that if the query returns more than one row, you will [probably] need to use a cursor.

CREATE OR REPLACE PROCEDURE SHOW_PRODUITS IS
    SOME_NUM Produit.num%type;
    SOME_DES Produit.designation%type;
    SOME_VAR VARCHAR2(6);
--
    CURSOR c1 IS
        SELECT num, designation, STORE(num) FROM Produit;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO SOME_NUM, SOME_DES, SOME_VAR;
        EXIT WHEN c1%NOTFOUND;
        dbms_output.put_line('result : ' || SOME_NUM || SOME_DES || SOME_VAR);
    END LOOP;
    CLOSE c1;
END;