set echo off
clear columns
clear breaks
--set linesize 900
set termout on
set feedback off
set head off
set pagesize 0
set arraysize 1
set feedback off
set heading off
set verify off
set recsep off
-- **************************************************************
-- *** Feel free to use this SQL-code, but please             ***
-- *** do not remove these copyright lines.                   ***
-- *** (C) Roger Felix - Lund - Sweden                        ***
-- *** [roger_felix@hotmail.com, web_master@work.utfors.se]   ***
-- *** URL: http://hem.fyristorg.com/publish/index.html       ***
-- **************************************************************
COLUMN U NEW_VALUE UB
SELECT '&OWNER' U FROM DUAL;
DEFINE owner_name = &UB

COLUMN T NEW_VALUE TB
SELECT '&TABLE_WILDCARD' T FROM DUAL;
DEFINE table_name = &TB

   select ' ' from dual;
select decode(position,1,'ALTER TABLE '||a1.owner||'.'||a1.table_name||chr(10)||
             '    ADD CONSTRAINT '||c1.constraint_name||chr(10)||
             '        PRIMARY KEY ('||chr(10)||
             '            ','           ,')||
             c1.column_name||
     decode(position,(
          select max(c2.position) 
              from DBA_CONS_COLUMNS c2
                  where c2.constraint_name = c1.constraint_name
                     and c2.owner = upper('&&owner_name')
           ),chr(10)||'       )'||chr(10)||'/'||chr(10)||chr(10),'')
          from DBA_constraints a1,
              DBA_CONS_COLUMNS c1
     where a1.constraint_type ='P'
       and a1.owner = upper('&&owner_name')
           and c1.constraint_name = a1.constraint_name
                 and a1.table_name like upper('&table_name')
order by a1.table_name, c1.position;

select ' ' from dual;
set heading on;
set feedback on;
set recsep wrap;
set verify on;
set echo on;