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

Question

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!

Hey, have you tried using a regex to get the column name, then combining them as a returned string together? — Tilen, Oct 11 '21 at 04:33
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Oct 11 '21 at 06:27
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. [ask] [Help] — philipxy, Oct 11 '21 at 06:27

score 2 · Accepted Answer · answered Oct 11 '21 at 06:25

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;

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

1 Answers1