Search code examples
sqlplsqloracle12c

PL/SQL Output column results in dbms_output.put_line LOOP


Good morning, I will start out saying that I have already done 97% of this as yes, it is homework. The ONLY part I am confused with is not understanding the Error that is given. I had to create a loop that would take 2 numbers given and then output which numbers both are commonly divisible by. Here is that code:

    SET SERVEROUTPUT ON SIZE UNLIMITED

--DROP TABLE IF IT EXISTS
DROP TABLE TESTER1 cascade constraints;
--CREATE TESTER1 TABLE
CREATE TABLE TESTER1 (xnum number, num1 number, num2 number);
--DECLARE VARIABLES
DECLARE 
   Test_Number1 number := 10;
   Test_Number2 number := 20;
   x number := 1;
--BEGIN   
BEGIN --OUTSIDE LOOP
    LOOP
        BEGIN --INSIDE LOOP
            INSERT INTO TESTER1(xnum,num1,num2)
            VALUES(x,MOD(Test_Number1,x),MOD(Test_Number2,x));
            x := x + 1;
        EXIT WHEN NOT x < 20;
        EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('error');
        END;
    END LOOP; --INSIDE LOOP
END; --OUTSIDE LOOP
/

I then wrote a SELECT statement to find where both have a common divisor:

SELECT xnum FROM tester1
where num1=num2;

My Question is: How do I loop through the results from the table Tester1 and use dbms_output.put_line() so each result from the SELECT statement is inserted until it loops through 3 rows? I hope I explained it well enough. I am using Oracle 12c.


Solution

  • Ok, so the answer I have found and just need to try and incorporate into my previous statement is:

    BEGIN
        FOR r_xnum IN (SELECT xnum FROM tester1
    where num1=num2)
        LOOP
        dbms_output.put_line('Common factor: ' ||r_xnum.xnum);
        END LOOP;
    END;
    /
    

    Thanks again for all that pointed me in the right direction. It has been awhile since I posted here last so not sure how to close out the question without completely deleting it, so I will keep it how it is unless someone else knows how. EDIT I have to wait 2 days until I can accept my own answer

    Final run:

    SET SERVEROUTPUT ON SIZE UNLIMITED
    
    --DROP TABLE IF IT EXISTS
    DROP TABLE TESTER1 cascade constraints;
    --CREATE TESTER1 TABLE
    CREATE TABLE TESTER1 (xnum number, num1 number, num2 number);
    
    --DECLARE VARIABLES
    DECLARE 
       Test_Number1 number := '&input1';
       Test_Number2 number := '&input2';
       x number := 1;
    --BEGIN   
    BEGIN --LOOP 1
        LOOP --LOOP FOR INSERT
            BEGIN --INSIDE LOOP
                INSERT INTO TESTER1(xnum,num1,num2)
                VALUES(x,MOD(Test_Number1,x),MOD(Test_Number2,x));
                x := x + 1;
                EXIT WHEN NOT x < 20;
           END;
        END LOOP; --LOOP 1
            BEGIN --LOOP FOR SELECT
                dbms_output.put_line('The two numbers enetered are ' ||Test_Number1|| 'and '||Test_Number2);
                FOR r_xnum IN (SELECT xnum FROM tester1
                    where num1=num2)
                LOOP
                    dbms_output.put_line('Common factor: ' ||r_xnum.xnum);
                END LOOP;
            END;
    END;
    /