Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
I'm sure that this has been asked before. But I don't even know what it would be called to search on it. So my apologies in advance.
How would you split out the output of a query, so that it has "parent" columns, and "child" columns.
I'm tasked out with creating triggers for all the tables in a schema. So instead of going table by table trying to copy out column names, I ran a pl/sql block to pull all from all_tab_cols.
SET SERVEROUTPUT ON SIZE 100000
SET FEEDBACK ON
tabnam all_tab_cols%ROWTYPE;
FOR tabnam IN (SELECT table_name, column_name
FROM all_tab_columns
WHERE owner = 'WBBETTERS')
LOOP
DBMS_OUTPUT.PUT_LINE(tabnam.table_name||','||tabnam.column_name||' = :NEW.'||tabnam.column_name);
END LOOP;
very small sample output:
FY14_DSF_DATA,INTERVIEW_TIME_START = :NEW.INTERVIEW_TIME_START
FY14_DSF_DATA,LAST_UPDATE = :NEW.FY14_DSF_DATA,LAST_UPDATE
FY14_DSF_DATA,SITE_CN_FK = :NEW.FY14_DSF_DATA,SITE_CN_FK
FY14_DSF_DATA,AFOREST_CODE = :NEW.FY14_DSF_DATA,AFOREST_CODE
FY14_INDIVIDUAL_DATA,PURPOSE_SITE_WORKING = :NEW.PURPOSE_SITE_WORKING
FY14_INDIVIDUAL_DATA,OVERNIGHT = :NEW.OVERNIGHT
FY14_INDIVIDUAL_DATA,NVEXPAND = :NEW.NVEXPAND
FY14_INDIVIDUAL_DATA,FORM = :NEW.FORM
What I would LIKE to see:
FY14_DSF_DATA INTERVIEW_TIME_START = :NEW.INTERVIEW_TIME_START
LAST_UPDATE = :NEW.FY14_DSF_DATA,LAST_UPDATE
SITE_CN_FK = :NEW.FY14_DSF_DATA,SITE_CN_FK
AFOREST_CODE = :NEW.FY14_DSF_DATA,AFOREST_CODE
FY14_INDIVIDUAL_DATA PURPOSE_SITE_WORKING = :NEW.PURPOSE_SITE_WORKING
OVERNIGHT = :NEW.OVERNIGHT
NVEXPAND = :NEW.NVEXPAND
FORM = :NEW.FORM
This way I can just grab each section and put it into the trigger context to build it.
If you know a better way, I'm open to that as well, but please still answer this question as I can see value in learning this either way.
Thanks.