Dear Y'all -
I <[EMAIL PROTECTED]> writes:
> My question is does DBI or the Oracle DBD provide any mechanism to
> extract the names of all the source tables from the SQL text that
> generates the view.
With your help I found the answer; as follows:
------------------------------------------------------------
#!/usr/bin/perl
use strict;
use Data::Dumper;
use SQL::Parser;
my $sql_cmd = "SELECT EMPLID , EFFDT ,'A' , PER_TYPE , MAR_STATUS , MAR_STATUS_DT ,
SEX , AGE_STATUS , HIGHEST_EDUC_LVL , FT_STUDENT , LANG_CD , YEARS_OF_EXP , APPLID ,
APP_DT , ALTER_EMPLID , BILINGUALISM_CODE , HEALTH_CARE_NBR , HEALTH_CARE_STATE ,
GUARDIAN_CHE , MILIT_SITUATN_ESP , SOC_SEC_AFF_DT , GVT_CRED_MIL_SVCE ,
GVT_MILITARY_COMP , GVT_MIL_GRADE , GVT_MIL_RESRVE_CAT , GVT_MIL_SEP_RET ,
GVT_MIL_SVCE_END, GVT_MIL_SVCE_START , GVT_MIL_VERIFY , GVT_PAR_NBR_LAST ,
GVT_UNIF_SVC_CTR , GVT_VET_PREF_APPT , GVT_VET_PREF_RIF , GVT_CHANGE_FLAG ,
GVT_DRAFT_STATUS , GVT_YR_ATTAINED , DISABLED_VET , DISABLED , ETHNIC_GROUP ,
GVT_DISABILITY_CD , GRADE , SAL_ADMIN_PLAN , GVT_CURR_AGCY_EMPL , GVT_CURR_FED_EMPL ,
GVT_HIGH_PAY_PLAN , GVT_HIGH_GRADE , GVT_PREV_AGCY_EMPL , GVT_PREV_FED_EMPL ,
GVT_SEP_INCENTIVE , GVT_SEP_INCENT_DT , GVT_TENURE , GVT_PAY_PLAN , BARG_UNIT ,
ENTRY_DT_FRA , MILIT_SITUATN_FRA , CPAMID , MILITARY_STAT_GER , EXPCTD_MILITARY_DT ,
HR_RESPONSIBLE_ID, MILITARY_STAT_!
ITA , MILITARY_TYPE_ITA , MILITARY_RANK_ITA , MILITARY_END_ITA , HONSEKI_JPN ,
US_WORK_ELIGIBILTY , MILITARY_STATUS , CITIZEN_PROOF1 , CITIZEN_PROOF2 , SMOKER ,
MEDICARE_ENTLD_DT , SMOKER_DT , FP_ACTION_2 , ACTION_REASON , FP_ACTION_REQ ,
FP_SUPDOC_REQ , LAST_UPDATE_DATE , LOAD_DTTM , LASTUPDDTTM , ERROR_FLAG FROM
PS_PERS_DATA_EFFDT PDE WHERE PDE.EFFDT = ( SELECT MAX(B.EFFDT) FROMPS_PERS_DATA_EFFDT
B WHERE B.EMPLID = PDE.EMPLID AND ( B.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') OR (B.EFFDT >
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')< ( SELECT MIN(C.EFFDT) FROM
PS_PERS_DATA_EFFDT C WHERE C.EMPLID = B.EMPLID) ) ) )";
my $parser = SQL::Parser->new( 'AnyData');
$parser->parse($sql_cmd);
use Data::Dumper;
print Dumper $parser->structure;
-----------------------------------------------------------------
Note especially:
'table_names' => [
'PS_PERS_DATA_EFFDT'
],
Yours - Billy
============================================================
William Goedicke [EMAIL PROTECTED]
http://www.goedsole.com:8080
============================================================
Lest we forget:
It's a god-ridden country.
- Victor Goedicke
_______________________________________________
Boston-pm mailing list
[EMAIL PROTECTED]
http://mail.pm.org/mailman/listinfo/boston-pm
