SELECT cols.column_name
FROM all_constraints cons NATURAL JOIN all_cons_columns cols
WHERE cons.constraint_type = 'P' AND table_name = UPPER('METADATA_TAB');

I am getting primary key field but I want to retrieve data from METADATA_TAB table with primary key fields.

I am expecting primary key field and data both should display.

anyone one can help. thank you.

CodePudding user response：

Here's a demo.

Table contains a composite primary key (which means that the final result should contain all columns involved, not just one).

SQL> CREATE TABLE test
  2  AS
  3     SELECT deptno,
  4            empno,
  5            ename,
  6            job
  7       FROM emp
  8      WHERE deptno = 10;

Table created.

SQL> ALTER TABLE test
  2     ADD CONSTRAINT pk_test PRIMARY KEY (deptno, empno);

Table altered.

Function that accepts table name as a parameter and returns refcursor as a result; listagg function returns comma-separated list of all primary key columns. If there's no primary key, function raises an exception.

SQL> CREATE OR REPLACE FUNCTION f_get (par_table_name IN VARCHAR2)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     l_pk_cols  VARCHAR2 (100);
  5     l_str      VARCHAR2 (1000);
  6     rc         SYS_REFCURSOR;
  7  BEGIN
  8     SELECT LISTAGG (cols.column_name, ', ')
  9               WITHIN GROUP (ORDER BY cols.position)
 10       INTO l_pk_cols
 11       FROM all_constraints cons NATURAL JOIN all_cons_columns cols
 12      WHERE     cons.constraint_type = 'P'
 13            AND table_name =
 14                DBMS_ASSERT.sql_object_name (UPPER (par_table_name));
 15
 16     IF l_pk_cols IS NULL
 17     THEN
 18        raise_application_error (-20000,
 19                                 'That table does not have a primary key');
 20     ELSE
 21        l_str := 'select ' || l_pk_cols || ' from ' || par_table_name;
 22
 23        OPEN rc FOR l_str;
 24
 25        RETURN rc;
 26     END IF;
 27  END;
 28  /

Function created.

Testing:

SQL> SELECT f_get ('test') FROM DUAL;

F_GET('TEST')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO      EMPNO
---------- ----------
        10       7782
        10       7839
        10       7934

    
SQL> SELECT f_get ('mytable') FROM DUAL;
SELECT f_get ('mytable') FROM DUAL
       *
ERROR at line 1:
ORA-20000: That table does not have a primary key
ORA-06512: at "SCOTT.F_GET", line 18


SQL>

In SQL Developer, if you want to see prettier output, then

1. double-click anywhere in result set
2. click the pencil button
3. see the result