RETURNING multiple Primary Key Columns in a Function on one line. PLSQL, SQL ORACLE DEV-CodePudding

im just trying to find out how I can return MULTIPLE Primary Keys Columns from a table in one DBMS OUTPUT LINE....

In a table I have the following...

The code for my function is:

    CREATE OR REPLACE FUNCTION Get_Constraint_Columns(
    /*
    * Oct 10th, 2021
    * Return the column names that make up a PK constraint and columns that make up unique constraint.
    */
    iTable VARCHAR2, iConstraint VARCHAR2
    )
    RETURN VARCHAR2
    AS
      wReturn VARCHAR2(30);
    CURSOR ColumnSelected IS
      SELECT Column_Name
      FROM User_Cons_Columns
      WHERE Table_Name = iTable AND Constraint_Name = iConstraint
      ORDER BY Position;
    CurrentRow User_Cons_Columns%ROWTYPE;
    
    BEGIN
     FOR CurrentRow IN ColumnSelected LOOP
    
       wReturn := (CurrentRow.Column_Name);
    
       RETURN wReturn; 
    
     END LOOP;
    
    END;
    /

When that function is called and a report is SPOOLED i get:

When I need it to look like:

Where it has BOTH column names...(MFR, PRODUCT) I've moved things around in the code and tried different things but it either ends with the same result or errors. I would be forever appreciative if someone might be able to point me in the right direction. Thanks in advance!

CodePudding user response：

The reason it is failing is that the RETURN statement immediately quits the block. So you will only ever get the first iteration of the loop. Move the RETURN out of the loop, AND contatenate the values to get what you intend to get:

create or replace FUNCTION get_constraint_columns(
    /*
    * Oct 10th, 2021
    * Return the column names that make up a PK constraint and columns that make up unique constraint.
    */
    itable VARCHAR2, iconstraint VARCHAR2
    )
    RETURN VARCHAR2
    AS
      wreturn VARCHAR2(30);
    CURSOR columnselected IS
      SELECT column_name
      FROM user_cons_columns
      WHERE table_name = itable AND constraint_name = iconstraint
      ORDER BY position;
    currentrow user_cons_columns%ROWTYPE;

    BEGIN
     FOR CurrentRow IN columnselected LOOP
       wreturn := wreturn ||', '||(currentrow.column_name);
       
     END LOOP;
     -- LTRIM removes the leading comma and space
     RETURN LTRIM(wreturn,', '); 
END;

Using LISTAGG this function can be simplified.

create or replace FUNCTION get_constraint_columns(
    itable VARCHAR2, iconstraint VARCHAR2
)
RETURN VARCHAR2
AS
  wreturn VARCHAR2(4000); --4000 because 30 can be too small 
BEGIN
  SELECT LISTAGG(column_name, ', ') 
     WITHIN GROUP (ORDER BY position)
    INTO wreturn
    FROM user_cons_columns
   WHERE table_name = itable AND constraint_name = iconstraint
   ORDER BY position;
  RETURN wreturn;
END;