{"id":7508,"date":"2015-09-04T19:52:44","date_gmt":"2015-09-04T22:52:44","guid":{"rendered":"http:\/\/www.orabr.com\/script-para-recuperacao-das-colunas-referente-as-constraints-das-tabelas-do-aol\/"},"modified":"2015-09-04T19:54:08","modified_gmt":"2015-09-04T22:54:08","slug":"SCRIPT-PARA-RECUPERACAO-DAS-COLUNAS-REFERENTE-AS-CONSTRAINTS-DAS-TABELAS-DO-AOL","status":"publish","type":"post","link":"https:\/\/orabr.virttus.com\/?p=7508","title":{"rendered":"SCRIPT PARA RECUPERACAO DAS COLUNAS REFERENTE AS CONSTRAINTS DAS TABELAS DO AOL"},"content":{"rendered":"

<\/p>\n

PROMPT ‘—————————–<\/wbr>——————————<\/wbr>————————–‘<\/u><\/u><\/p>\n

PROMPT ‘– SCRIPT PARA RECUPERACAO DAS COLUNAS REFERENTE AS CONSTRAINTS DAS TABELAS DO AOL –‘<\/u><\/u><\/p>\n

PROMPT ‘—————————–<\/wbr>——————————<\/wbr>————————–‘<\/u><\/u><\/span><\/p>\n

COLUMN APPLICATION_SHORT_NAME FORMAT A4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/u><\/u><\/span><\/p>\n

COLUMN TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT A30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/u><\/u><\/span><\/p>\n

COLUMN PRIMARY_KEY_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT A30\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/u><\/u><\/span><\/p>\n

COLUMN COLUMN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT A27<\/u><\/u><\/span><\/p>\n

COLUMN PKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT 999<\/u><\/u><\/span><\/p>\n

COLUMN FKS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FORMAT 999<\/u><\/u><\/span><\/p>\n

SET LINES 400<\/u><\/u><\/span><\/p>\n

SELECT FA.APPLICATION_SHORT_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FT.TABLE_NAME\u00a0<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,FPK.PRIMARY_KEY_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FPK.PRIMARY_KEY_TYPE<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FC.COLUMN_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FPKC.PRIMARY_KEY_SEQUENCE PKS<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FT2.TABLE_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FFK.FOREIGN_KEY_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FFK.FOREIGN_KEY_RELATION<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FC2.COLUMN_NAME<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 ,FFKC.FOREIGN_KEY_SEQUENCE FKS<\/u><\/u><\/span><\/p>\n

FROM FND_APPLICATION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FA\u00a0\u00a0 ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_TABLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FT\u00a0\u00a0 ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_PRIMARY_KEYS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FPK\u00a0 ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_PRIMARY_KEY_COLUMNS\u00a0 FPKC ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_COLUMNS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FC\u00a0\u00a0 ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_FOREIGN_KEYS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FFK\u00a0 ,\u00a0<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0FND_FOREIGN_KEY_COLUMNS\u00a0 FFKC ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_COLUMNS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FC2\u00a0 ,<\/u><\/u><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0 FND_TABLES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FT2<\/u><\/u><\/span><\/p>\n

WHERE FA.APPLICATION_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FT.APPLICATION_ID\u00a0<\/u><\/u><\/span><\/p>\n

—<\/u><\/u><\/p>\n

— PARA IDA UTILIZE FT E PARA VOLTA UTILIZE FT2<\/u><\/u><\/p>\n

—<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FT2.TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = UPPER(‘&Tabela_Desejada’)\u00a0<\/u><\/u><\/span><\/p>\n

—<\/u><\/u><\/p>\n

— ATE AQUI EU DEFINI SOMENTE O APLICATIVO E A TABELA DESEJADA<\/u><\/u><\/p>\n

— AGORA VAMOS PARA A PARTE DAS CONSTRAINTS<\/u><\/u><\/p>\n

—<\/u><\/u><\/span><\/p>\n

–AND\u00a0\u00a0 FPK.PRIMARY_KEY_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = ‘D’<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPK.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FT.TABLE_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.PRIMARY_KEY_APPLICATION_ID = FPK.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.PRIMARY_KEY_TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FPK.TABLE_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FT2.TABLE_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.APPLICATION_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FT2.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPK.PRIMARY_KEY_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FPKC.PRIMARY_KEY_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPK.APPLICATION_ID\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0= FPKC.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPK.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FPKC.TABLE_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPKC.COLUMN_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC.COLUMN_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPKC.APPLICATION_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FPKC.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC.TABLE_ID<\/u><\/u><\/span><\/p>\n

—<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.FOREIGN_KEY_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FFKC.FOREIGN_KEY_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.APPLICATION_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FFKC.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFK.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FFKC.TABLE_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFKC.COLUMN_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC2.COLUMN_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFKC.APPLICATION_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC2.APPLICATION_ID<\/u><\/u><\/span><\/p>\n

AND\u00a0\u00a0 FFKC.TABLE_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = FC2.TABLE_ID<\/u><\/u><\/span><\/p>\n

ORDER BY 2,3,7,8<\/p>\n

\n

\n

Autor:\u00a0Rafael de Oliveira Souza<\/b><\/p>\n

<\/HTML><\/HTML><\/p>\n","protected":false},"excerpt":{"rendered":"

PROMPT ‘—————————–——————————————————–‘ PROMPT ‘– SCRIPT PARA RECUPERACAO DAS COLUNAS REFERENTE AS CONSTRAINTS DAS TABELAS DO AOL –‘ PROMPT ‘—————————–——————————————————–‘ COLUMN APPLICATION_SHORT_NAME FORMAT A4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLUMN TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,127,124,126,110],"tags":[],"class_list":["post-7508","post","type-post","status-publish","format-standard","hentry","category-base-de-conhecimentos","category-applications","category-database","category-developer-tools","category-estudo-e-pesquisa"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/7508","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7508"}],"version-history":[{"count":1,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/7508\/revisions"}],"predecessor-version":[{"id":7509,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/7508\/revisions\/7509"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7508"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7508"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}