Home » SQL & PL/SQL » SQL & PL/SQL » getting kup-00554 when creating external table
getting kup-00554 when creating external table [message #195637] Fri, 29 September 2006 18:19 Go to next message
jonesks
Messages: 1
Registered: September 2006
Location: Las Vegas Nv
Junior Member
I am a fairly new Oracle DBA (been a DB2 DBA for 6 years) and am trying to write my first PL/SQL procedure to load tables with Oracle_loader by creating an external table with the flat files. It compiles clean in TOAD but when it runs it gets these errors. It creates the table but doesn't load it. I have rsearched and tried to match what I found to what I have but everything I have seemed to match what you should have done so I can't see the trees for the forest. Thanks for any help I can get as I have to create 39 more of these once this one is working. I am getting the following messages:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "end-of-file": expecting one of: "badfile, byteordermark, characterset, colon, column, data, delimited, discardfile, exit, fields, fixed, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string, skip, territory, variable"
KUP-01007: at line 2 column 39
KUP-00031: concat
ORA-06512: at

My code is as follows:

CREATE OR REPLACE PROCEDURE dsdp0001
IS
tmpvar NUMBER;
/******************************************************************************
NAME: DSDP0001
PURPOSE: Load SSASIDB1.ASTU_STUDENT from file created by reformat program
using the file as an external table.

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 9/8/2006 KSJ 1. Created this procedure.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: DSDP0001
Sysdate: 9/8/2006
Date and Time: 9/8/2006, 4:53:09 PM, and 9/8/2006 4:53:09 PM
Username: (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
--Empty yesterday's data from table
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SSASIDB1';

EXECUTE IMMEDIATE 'DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000';

EXECUTE IMMEDIATE 'DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001';

EXECUTE IMMEDIATE 'TRUNCATE TABLE SSASIDB1.ASTU_STUDENT';

--CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
EXECUTE IMMEDIATE 'CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ''/cisdev/u01/app/oracle/product/10.2.0/Db_1/tmp''';

EXECUTE IMMEDIATE 'CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001 AS ''/var/tmp/''';

--CREATE TABLE statement for external table:
------------------------------------------------------------------------
EXECUTE IMMEDIATE 'CREATE TABLE "SYS_SQLLDR_X_EXT_ASTU_STUDENT"
(
"CCSDSTATUS" CHAR(1),
"SCHOOLNUM" CHAR(3),
"STULINK" NUMBER,
"ENTERDATE" DATE,
"LEAVEDATE" DATE,
"ENTERCODE" CHAR(3),
"LEAVECODE" CHAR(3),
"PERMNUM" CHAR(12),
"SOCSECNUM" CHAR(10),
"FAMILYNUM" NUMBER,
"LASTNAME" CHAR(40),
"FIRSTNAME" CHAR(20),
"MIDDLENAME" CHAR(13),
"OTHERNAME" CHAR(20),
"NICKNAME" CHAR(13),
"BIRTHDATE" DATE,
"GENDER" CHAR(1),
"GRADE" CHAR(2),
"TRK" CHAR(1),
"ETHNICCODE" CHAR(3),
"PRNTGUARD" CHAR(35),
"MAILADDR" CHAR(35),
"CITY" CHAR(24),
"ZIPCODE" CHAR(10),
"STATE" CHAR(2),
"GRIDCODE" CHAR(Cool,
"TELEPHONE" CHAR(15),
"BIRTHPLACE" CHAR(20),
"BIRTHMETH" CHAR(2),
"ENGPROF" CHAR(1),
"CCSDLANG" CHAR(3),
"HOMELNG" CHAR(3),
"HOMELNGCOR" CHAR(3),
"CCSD504" CHAR(1),
"FREEMEALS" CHAR(1),
"USCITIZEN" CHAR(1),
"COUNSELOR" CHAR(3),
"TEACHER" CHAR(3),
"NEXTSCHOOL" CHAR(3),
"NEXTGRADE" CHAR(2),
"NEXTTRACK" CHAR(1),
"RETAIN" CHAR(1),
"CREDATMPT" NUMBER(7,3),
"CREDEARNED" NUMBER(7,3),
"CUMMGPA1" NUMBER(9,4),
"CUMMGPA2" NUMBER(9,4),
"CUMMGPA3" NUMBER(9,4),
"CUMMGPA4" NUMBER(9,4),
"CURGPA1" NUMBER(9,4),
"CURGPA2" NUMBER(9,4),
"CURGPA3" NUMBER(9,4),
"CURGPA4" NUMBER(9,4),
"CUM2GPA1" NUMBER(7,4),
"CUM2GPA2" NUMBER(7,4),
"CUM2GPA3" NUMBER(7,4),
"CUM2GPA4" NUMBER(7,4),
"CUR2GPA1" NUMBER(7,4),
"CUR2GPA2" NUMBER(7,4),
"CUR2GPA3" NUMBER(7,4),
"CUR2GPA4" NUMBER(7,4),
"CLASSRANK1" NUMBER,
"CLASSRANK2" NUMBER,
"CLASSSIZE" NUMBER,
"GRDAVERAGE" NUMBER,
"INSTRSET" CHAR(5),
"LASTSCHOOL" CHAR(3),
"QUERYTAG" CHAR(1),
"LOCATORTAG" CHAR(1),
"CUSTOMTAG1" CHAR(1),
"CUSTOMTAG2" CHAR(1),
"LOCKER" CHAR(6),
"ALTLOCKER" CHAR(6),
"LOWPERIOD" CHAR(2),
"HIGHPERIOD" CHAR(2),
"SCHOOLRES" CHAR(3),
"DISTRICTRES" CHAR(6),
"CONCSCHOOL" CHAR(3),
"CONCISHOME" CHAR(1),
"RESTDIRINF" CHAR(1),
"UNLISTTEL" CHAR(1),
"AUTODIAL" CHAR(1),
"MIGRANT" CHAR(1),
"REFUGEE" CHAR(1),
"HOMELESS" CHAR(1),
"POSTSECOND" CHAR(2),
"DRVTRNELG" CHAR(1),
"DRVTRNDATE" DATE,
"AMERICAN_INDIAN" CHAR(1),
"HOMEROOM" CHAR(6),
"ENTERGRADE" CHAR(2),
"ORIGENTCD" CHAR(3),
"ORIGENTDT" DATE,
"PCTAPPORT" NUMBER,
"ATTPRMCODE" CHAR(1),
"ATTPRMDATE" DATE,
"BUSINFO" CHAR(6),
"ORIGYRGRAD" CHAR(4),
"SPEDELIG" CHAR(Cool,
"SPEDPLACE" CHAR(Cool,
"GRADEEQV" CHAR(Cool,
"USERCD4" CHAR(Cool,
"USERCD5" CHAR(Cool,
"HLTHDIS1" CHAR(Cool,
"HLTHDIS2" CHAR(Cool,
"HLTHDIS3" CHAR(Cool,
"HLTHDIS4" CHAR(Cool,
"ELIGADA" CHAR(1),
"TEAMNUM" CHAR(3),
"BALCODE" CHAR(1),
"GETSAID" CHAR(1),
"ABSENCETAG" CHAR(1),
"ABSTAGDATE" DATE,
"CLASSRANK3" NUMBER,
"CLASSRANK4" NUMBER,
"MULTIENR" CHAR(1),
"ENDYRST" CHAR(3),
"USERNUM1" NUMBER(9,2),
"USERNUM2" NUMBER(9,2),
"USERNUM3" NUMBER(9,2),
"USERNUM4" NUMBER(9,2),
"CLSSFTION" CHAR(3),
"CCSDEPS" CHAR(3),
"USERNUM5" NUMBER(9,2),
"CCSDUPDT" CHAR(13),
"ALTID2" CHAR(13),
"HOUSE" CHAR(1),
"FEDCONNECTED" CHAR(1),
"FEEBAL" NUMBER(7,2),
"HNRROLLTAG" CHAR(1),
"PRIDAYENR" NUMBER,
"SUMMERSCHL" CHAR(3),
"SCHOOL_ATT" CHAR(3),
"GRADDATE" DATE,
"MSGCODE" CHAR(3),
"COMP_ED" CHAR(1),
"RESADDR" CHAR(35),
"RESCITY" CHAR(24),
"RESZIPCODE" CHAR(10),
"RESSTATE" CHAR(2),
"RESHOUSE" CHAR(9),
"RESSTDIR" CHAR(2),
"RESSTNAME" CHAR(25),
"RESSTTYPE" CHAR(4),
"RESAPTNUM" CHAR(10),
"BUSINFO2" CHAR(6),
"DSTAMPREQ" DATE,
"TSTAMPREQ" CHAR(Cool,
"DSTAMPSCH" DATE,
"TSTAMPSCH" CHAR(Cool,
"SSOUTDATED" CHAR(1),
"SCHEDOPEN" CHAR(1),
"PASSPORT" CHAR(15),
"COUNTRY" CHAR(30),
"PROVINCE" CHAR(30),
"CCSDGENERATION" CHAR(4),
"RESFRACT" CHAR(3),
"PRFSESSION" CHAR(1),
"SPSTATFLAG" NUMBER,
"FAMILYLINK" NUMBER,
"USERSTAMP" CHAR(10),
"DATESTAMP" DATE,
"TIMESTMP" CHAR(Cool,
"CCSD_ID" CHAR(6),
"CCSDFULLNAME" CHAR(76),
"DIPLOMATYP" CHAR(3),
"GRADPGRM" CHAR(3),
"DISUSERCD" CHAR(Cool,
"MEMBERSHIP1" NUMBER,
"MEMBERSHIP2" NUMBER,
"MEMBERSHIP3" NUMBER,
"MEMBERSHIP4" NUMBER,
"MEMBERSHIP5" NUMBER,
"MEMBERSHIP6" NUMBER,
"MEMBERSHIP7" NUMBER,
"MEMBERSHIP8" NUMBER,
"MEMBERSHIP9" NUMBER,
"MEMBERSHIP10" NUMBER,
"MEMBERSHIP11" NUMBER,
"MEMBERSHIP12" NUMBER,
"MEMBERSHIP13" NUMBER,
"MEMBERSHIP14" NUMBER,
"MEMBERSHIP15" NUMBER,
"MEMBERSHIP16" NUMBER,
"MEMBERSHIP17" NUMBER,
"MEMBERSHIP18" NUMBER,
"MEMBERSHIP19" NUMBER,
"MEMBERSHIP20" NUMBER,
"MEMBERSHIP21" NUMBER,
"BIRTHCNTRY" CHAR(5),
"TITLE_3_IMMG" CHAR(1),
"US_SCH_ENT_DT" DATE
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001
ACCESS PARAMETERS
(
RECORDS FIXED 1505 CHARACTERSET US7ASCII
BADFILE SYS_SQLLDR_XT_TMPDIR_00000/ASTU.bad
DISCARDFILE SYS_SQLLDR_XT_TMPDIR_00000/ASTU.dsc
LOGFILE ''ASTU.log_xt''
READSIZE 1048576
FIELDS NOTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"CCSDSTATUS" (1:1) CHAR(1),
"SCHOOLNUM" (2:4) CHAR(3),
"STULINK" (5:15) INTEGER EXTERNAL(11),
"ENTERDATE" (16:25) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY",
"LEAVEDATE" (26:35) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("LEAVEDATE" = BLANKS),
"ENTERCODE" (38:40) CHAR(3),
"LEAVECODE" (41:43) CHAR(3),
"PERMNUM" (44:55) CHAR(12),
"SOCSECNUM" (56:65) CHAR(10),
"FAMILYNUM" (66:76) INTEGER EXTERNAL(11),
"LASTNAME" (77:116) CHAR(40),
"FIRSTNAME" (117:136) CHAR(20),
"MIDDLENAME" (137:149) CHAR(13),
"OTHERNAME" (150:169) CHAR(20),
"NICKNAME" (170:182) CHAR(13),
"BIRTHDATE" (183:192) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("BIRTHDATE" = BLANKS),
"GENDER" (195:195) CHAR(1),
"GRADE" (196:197) CHAR(2),
"TRK" (198:198) CHAR(1),
"ETHNICCODE" (199:201) CHAR(3),
"PRNTGUARD" (202:236) CHAR(35),
"MAILADDR" (237:271) CHAR(35),
"CITY" (272:295) CHAR(24),
"ZIPCODE" (296:305) CHAR(10),
"STATE" (306:307) CHAR(2),
"GRIDCODE" (308:315) CHAR(Cool,
"TELEPHONE" (316:330) CHAR(15),
"BIRTHPLACE" (331:350) CHAR(20),
"BIRTHMETH" (351:352) CHAR(2),
"ENGPROF" (353:353) CHAR(1),
"CCSDLANG" (354:356) CHAR(3),
"HOMELNG" (357:359) CHAR(3),
"HOMELNGCOR" (360:362) CHAR(3),
"CCSD504" (363:363) CHAR(1),
"FREEMEALS" (364:364) CHAR(1),
"USCITIZEN" (365:365) CHAR(1),
"COUNSELOR" (366:368) CHAR(3),
"TEACHER" (369:371) CHAR(3),
"NEXTSCHOOL" (372:374) CHAR(3),
"NEXTGRADE" (375:376) CHAR(2),
"NEXTTRACK" (377:377) CHAR(1),
"RETAIN" (378:378) CHAR(1),
"CREDATMPT" (380:387) INTEGER EXTERNAL(Cool,
"CREDEARNED" (389:396) INTEGER EXTERNAL(Cool,
"CUMMGPA1" (398:407) INTEGER EXTERNAL(10),
"CUMMGPA2" (409:418) INTEGER EXTERNAL(10),
"CUMMGPA3" (420:429) INTEGER EXTERNAL(10),
"CUMMGPA4" (431:440) INTEGER EXTERNAL(10),
"CURGPA1" (442:451) INTEGER EXTERNAL(10),
"CURGPA2" (453:462) INTEGER EXTERNAL(10),
"CURGPA3" (464:473) INTEGER EXTERNAL(10),
"CURGPA4" (475:484) INTEGER EXTERNAL(10),
"CUM2GPA1" (486:493) INTEGER EXTERNAL(Cool,
"CUM2GPA2" (495:502) INTEGER EXTERNAL(Cool,
"CUM2GPA3" (505:511) INTEGER EXTERNAL(7),
"CUM2GPA4" (513:520) INTEGER EXTERNAL(Cool,
"CUR2GPA1" (522:529) INTEGER EXTERNAL(Cool,
"CUR2GPA2" (531:538) INTEGER EXTERNAL(Cool,
"CUR2GPA3" (540:547) INTEGER EXTERNAL(Cool,
"CUR2GPA4" (549:556) INTEGER EXTERNAL(Cool,
"CLASSRANK1" (557:567) INTEGER EXTERNAL(11),
"CLASSRANK2" (568:578) INTEGER EXTERNAL(11),
"CLASSSIZE" (579:589) INTEGER EXTERNAL(11),
"GRDAVERAGE" (590:595) INTEGER EXTERNAL(6),
"INSTRSET" (596:600) CHAR(5),
"LASTSCHOOL" (601:603) CHAR(3),
"QUERYTAG" (604:604) CHAR(1),
"LOCATORTAG" (605:605) CHAR(1),
"CUSTOMTAG1" (606:606) CHAR(1),
"CUSTOMTAG2" (607:607) CHAR(1),
"LOCKER" (608:613) CHAR(6),
"ALTLOCKER" (614:619) CHAR(6),
"LOWPERIOD" (620:621) CHAR(2),
"HIGHPERIOD" (622:623) CHAR(2),
"SCHOOLRES" (624:626) CHAR(3),
"DISTRICTRES" (627:632) CHAR(6),
"CONCSCHOOL" (633:635) CHAR(3),
"CONCISHOME" (636:636) CHAR(1),
"RESTDIRINF" (637:637) CHAR(1),
"UNLISTTEL" (638:638) CHAR(1),
"AUTODIAL" (639:639) CHAR(1),
"MIGRANT" (640:640) CHAR(1),
"REFUGEE" (641:641) CHAR(1),
"HOMELESS" (642:642) CHAR(1),
"POSTSECOND" (643:644) CHAR(2),
"DRVTRNELG" (645:645) CHAR(1),
"DRVTRNDATE" (646:655) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("DRVTRNDATE" = BLANKS),
"AMERICAN_INDIAN" (658:658) CHAR(1),
"HOMEROOM" (659:664) CHAR(6),
"ENTERGRADE" (665:666) CHAR(2),
"ORIGENTCD" (667:669) CHAR(3),
"ORIGENTDT" (670:679) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("ORIGENTDT" = BLANKS),
"PCTAPPORT" (682:687) INTEGER EXTERNAL(6),
"ATTPRMCODE" (688:688) CHAR(1),
"ATTPRMDATE" (689:698) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("ATTPRMDATE" = BLANKS),
"BUSINFO" (701:706) CHAR(6),
"ORIGYRGRAD" (707:710) CHAR(4),
"SPEDELIG" (711:718) CHAR(Cool,
"SPEDPLACE" (719:726) CHAR(Cool,
"GRADEEQV" (727:734) CHAR(Cool,
"USERCD4" (735:742) CHAR(Cool,
"USERCD5" (743:750) CHAR(Cool,
"HLTHDIS1" (751:758) CHAR(Cool,
"HLTHDIS2" (759:766) CHAR(Cool,
"HLTHDIS3" (767:774) CHAR(Cool,
"HLTHDIS4" (775:782) CHAR(Cool,
"ELIGADA" (783:783) CHAR(1),
"TEAMNUM" (784:786) CHAR(3),
"BALCODE" (787:787) CHAR(1),
"GETSAID" (788:788) CHAR(1),
"ABSENCETAG" (789:789) CHAR(1),
"ABSTAGDATE" (790:799) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("ABSTAGDATE" = BLANKS),
"CLASSRANK3" (802:812) INTEGER EXTERNAL(11),
"CLASSRANK4" (813:823) INTEGER EXTERNAL(11),
"MULTIENR" (824:824) CHAR(1),
"ENDYRST" (825:827) CHAR(3),
"USERNUM1" (829:838) INTEGER EXTERNAL(10),
"USERNUM2" (840:849) INTEGER EXTERNAL(10),
"USERNUM3" (851:860) INTEGER EXTERNAL(10),
"USERNUM4" (862:871) INTEGER EXTERNAL(10),
"CLSSFTION" (872:874) CHAR(3),
"CCSDEPS" (875:877) CHAR(3),
"USERNUM5" (879:888) INTEGER EXTERNAL(10),
"CCSDUPDT" (889:901) CHAR(13),
"ALTID2" (902:914) CHAR(13),
"HOUSE" (915:915) CHAR(1),
"FEDCONNECTED" (916:916) CHAR(1),
"FEEBAL" (918:925) INTEGER EXTERNAL(Cool,
"HNRROLLTAG" (926:926) CHAR(1),
"PRIDAYENR" (927:932) INTEGER EXTERNAL(6),
"SUMMERSCHL" (933:935) CHAR(3),
"SCHOOL_ATT" (936:938) CHAR(3),
"GRADDATE" (939:948) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("GRADDATE" = BLANKS),
"MSGCODE" (951:953) CHAR(3),
"COMP_ED" (954:954) CHAR(1),
"RESADDR" (955:989) CHAR(35),
"RESCITY" (990:1013) CHAR(24),
"RESZIPCODE" (1014:1023) CHAR(10),
"RESSTATE" (1024:1025) CHAR(2),
"RESHOUSE" (1026:1034) CHAR(9),
"RESSTDIR" (1035:1036) CHAR(2),
"RESSTNAME" (1037:1061) CHAR(25),
"RESSTTYPE" (1062:1065) CHAR(4),
"RESAPTNUM" (1066:1075) CHAR(10),
"BUSINFO2" (1076:1081) CHAR(6),
"DSTAMPREQ" (1082:1091) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("DSTAMPREQ" = BLANKS),
"TSTAMPREQ" (1094:1101) CHAR(Cool,
"DSTAMPSCH" (1104:1113) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("DSTAMPSCH" = BLANKS),
"TSTAMPSCH" (1116:1123) CHAR(Cool,
"SSOUTDATED" (1126:1126) CHAR(1),
"SCHEDOPEN" (1127:1127) CHAR(1),
"PASSPORT" (1128:1142) CHAR(15),
"COUNTRY" (1143:1172) CHAR(30),
"PROVINCE" (1173:1202) CHAR(30),
"CCSDGENERATION" (1203:1206) CHAR(4),
"RESFRACT" (1207:1209) CHAR(3),
"PRFSESSION" (1210:1210) CHAR(1),
"SPSTATFLAG" (1211:1221) INTEGER EXTERNAL(11),
"FAMILYLINK" (1222:1232) INTEGER EXTERNAL(11),
"USERSTAMP" (1233:1242) CHAR(10),
"DATESTAMP" (1243:1252) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("DATESTAMP" = BLANKS),
"TIMESTMP" (1255:1262) CHAR(Cool,
"CCSD_ID" (1265:1270) CHAR(6),
"CCSDFULLNAME" (1271:1346) CHAR(76),
"DIPLOMATYP" (1347:1349) CHAR(3),
"GRADPGRM" (1350:1352) CHAR(3),
"DISUSERCD" (1353:1360) CHAR(Cool,
"MEMBERSHIP1" (1361:1366) INTEGER EXTERNAL(6),
"MEMBERSHIP2" (1367:1372) INTEGER EXTERNAL(6),
"MEMBERSHIP3" (1373:1378) INTEGER EXTERNAL(6),
"MEMBERSHIP4" (1379:1384) INTEGER EXTERNAL(6),
"MEMBERSHIP5" (1385:1390) INTEGER EXTERNAL(6),
"MEMBERSHIP6" (1391:1396) INTEGER EXTERNAL(6),
"MEMBERSHIP7" (1397:1402) INTEGER EXTERNAL(6),
"MEMBERSHIP8" (1403:1408) INTEGER EXTERNAL(6),
"MEMBERSHIP9" (1409:1414) INTEGER EXTERNAL(6),
"MEMBERSHIP10" (1415:1420) INTEGER EXTERNAL(6),
"MEMBERSHIP11" (1421:1426) INTEGER EXTERNAL(6),
"MEMBERSHIP12" (1427:1432) INTEGER EXTERNAL(6),
"MEMBERSHIP13" (1433:1438) INTEGER EXTERNAL(6),
"MEMBERSHIP14" (1439:1444) INTEGER EXTERNAL(6),
"MEMBERSHIP15" (1445:1450) INTEGER EXTERNAL(6),
"MEMBERSHIP16" (1451:1456) INTEGER EXTERNAL(6),
"MEMBERSHIP17" (1457:1462) INTEGER EXTERNAL(6),
"MEMBERSHIP18" (1463:1468) INTEGER EXTERNAL(6),
"MEMBERSHIP19" (1469:1474) INTEGER EXTERNAL(6),
"MEMBERSHIP20" (1475:1480) INTEGER EXTERNAL(6),
"MEMBERSHIP21" (1481:1486) INTEGER EXTERNAL(6),
"BIRTHCNTRY" (1487:1491) CHAR(5),
"TITLE_3_IMMG" (1492:1492) CHAR(1),
"US_SCH_ENT_DT" (1493:1502) CHAR(10)
DATE_FORMAT DATE MASK "MM/DD/YYYY"
NULLIF ("US_SCH_ENT_DT" = BLANKS)
)
)
location
(
''SSASITSC.EXTRACT1.DAT'',
''SSASITSC.EXTRACT2.DAT'',
''SSASITSC.EXTRACT3.DAT'',
''SSASITSC.EXTRACT4.DAT'',
''SSASITSC.EXTRACT5.DAT'',
''SSASITSC.EXTRACT6.DAT''
)
)REJECT LIMIT UNLIMITED';

--INSERT statements used to load internal tables:
------------------------------------------------------------------------
EXECUTE IMMEDIATE 'INSERT /*+ append */INTO ssasidb1.astu_student
(ccsdstatus, schoolnum, stulink, enterdate, leavedate,entercode, leavecode, permnum, socsecnum, familynum,
lastname, firstname, middlename, othername, nickname,
birthdate, gender, grade, trk, ethniccode, prntguard,
mailaddr, city, zipcode, state, gridcode, telephone,
birthplace, birthmeth, engprof, ccsdlang, homelng,
homelngcor, ccsd504, freemeals, uscitizen, counselor,
teacher, nextschool, nextgrade, nexttrack, retain, credatmpt,
credearned, cummgpa1, cummgpa2, cummgpa3, cummgpa4, curgpa1,
curgpa2, curgpa3, curgpa4, cum2gpa1, cum2gpa2, cum2gpa3,
cum2gpa4, cur2gpa1, cur2gpa2, cur2gpa3, cur2gpa4, classrank1,
classrank2, classsize, grdaverage, instrset, lastschool,
querytag, locatortag, customtag1, customtag2, locker,
altlocker, lowperiod, highperiod, schoolres, districtres,
concschool, concishome, restdirinf, unlisttel, autodial,
migrant, refugee, homeless, postsecond, drvtrnelg,
drvtrndate, american_indian, homeroom, entergrade, origentcd,
origentdt, pctapport, attprmcode, attprmdate, businfo,
origyrgrad, spedelig, spedplace, gradeeqv, usercd4, usercd5,
hlthdis1, hlthdis2, hlthdis3, hlthdis4, eligada, teamnum,
balcode, getsaid, absencetag, abstagdate, classrank3,
classrank4, multienr, endyrst, usernum1, usernum2, usernum3,
usernum4, clssftion, ccsdeps, usernum5, ccsdupdt, altid2,
house, fedconnected, feebal, hnrrolltag, pridayenr,
summerschl, school_att, graddate, msgcode, comp_ed, resaddr,
rescity, reszipcode, resstate, reshouse, resstdir, resstname,
ressttype, resaptnum, businfo2, dstampreq, tstampreq,
dstampsch, tstampsch, ssoutdated, schedopen, passport,
country, province, ccsdgeneration, resfract, prfsession,
spstatflag, familylink, userstamp, datestamp, timestmp,
ccsd_id, ccsdfullname, diplomatyp, gradpgrm, disusercd,
membership1, membership2, membership3, membership4,
membership5, membership6, membership7, membership8,
membership9, membership10, membership11, membership12,
membership13, membership14, membership15, membership16,
membership17, membership18, membership19, membership20,
membership21, birthcntry, title_3_immg, us_sch_ent_dt)
SELECT "CCSDSTATUS", "SCHOOLNUM", "STULINK", "ENTERDATE", "LEAVEDATE","ENTERCODE", "LEAVECODE", "PERMNUM", "SOCSECNUM", "FAMILYNUM","LASTNAME", "FIRSTNAME", "MIDDLENAME", "OTHERNAME", "NICKNAME", "BIRTHDATE", "GENDER", "GRADE", "TRK", "ETHNICCODE",
"PRNTGUARD", "MAILADDR", "CITY", "ZIPCODE", "STATE", "GRIDCODE",
"TELEPHONE", "BIRTHPLACE", "BIRTHMETH", "ENGPROF", "CCSDLANG",
"HOMELNG", "HOMELNGCOR", "CCSD504", "FREEMEALS", "USCITIZEN",
"COUNSELOR", "TEACHER", "NEXTSCHOOL", "NEXTGRADE", "NEXTTRACK",
"RETAIN", "CREDATMPT", "CREDEARNED", "CUMMGPA1", "CUMMGPA2",
"CUMMGPA3", "CUMMGPA4", "CURGPA1", "CURGPA2", "CURGPA3",
"CURGPA4", "CUM2GPA1", "CUM2GPA2", "CUM2GPA3", "CUM2GPA4",
"CUR2GPA1", "CUR2GPA2", "CUR2GPA3", "CUR2GPA4", "CLASSRANK1",
"CLASSRANK2", "CLASSSIZE", "GRDAVERAGE", "INSTRSET",
"LASTSCHOOL", "QUERYTAG", "LOCATORTAG", "CUSTOMTAG1",
"CUSTOMTAG2", "LOCKER", "ALTLOCKER", "LOWPERIOD", "HIGHPERIOD",
"SCHOOLRES", "DISTRICTRES", "CONCSCHOOL", "CONCISHOME",
"RESTDIRINF", "UNLISTTEL", "AUTODIAL", "MIGRANT", "REFUGEE",
"HOMELESS", "POSTSECOND", "DRVTRNELG", "DRVTRNDATE",
"AMERICAN_INDIAN", "HOMEROOM", "ENTERGRADE", "ORIGENTCD",
"ORIGENTDT", "PCTAPPORT", "ATTPRMCODE", "ATTPRMDATE", "BUSINFO",
"ORIGYRGRAD", "SPEDELIG", "SPEDPLACE", "GRADEEQV", "USERCD4",
"USERCD5", "HLTHDIS1", "HLTHDIS2", "HLTHDIS3", "HLTHDIS4",
"ELIGADA", "TEAMNUM", "BALCODE", "GETSAID", "ABSENCETAG",
"ABSTAGDATE", "CLASSRANK3", "CLASSRANK4", "MULTIENR", "ENDYRST",
"USERNUM1", "USERNUM2", "USERNUM3", "USERNUM4", "CLSSFTION",
"CCSDEPS", "USERNUM5", "CCSDUPDT", "ALTID2", "HOUSE",
"FEDCONNECTED", "FEEBAL", "HNRROLLTAG", "PRIDAYENR",
"SUMMERSCHL", "SCHOOL_ATT", "GRADDATE", "MSGCODE", "COMP_ED",
"RESADDR", "RESCITY", "RESZIPCODE", "RESSTATE", "RESHOUSE",
"RESSTDIR", "RESSTNAME", "RESSTTYPE", "RESAPTNUM", "BUSINFO2",
"DSTAMPREQ", "TSTAMPREQ", "DSTAMPSCH", "TSTAMPSCH",
"SSOUTDATED", "SCHEDOPEN", "PASSPORT", "COUNTRY", "PROVINCE",
"CCSDGENERATION", "RESFRACT", "PRFSESSION", "SPSTATFLAG",
"FAMILYLINK", "USERSTAMP", "DATESTAMP", "TIMESTMP", "CCSD_ID",
"CCSDFULLNAME", "DIPLOMATYP", "GRADPGRM", "DISUSERCD",
"MEMBERSHIP1", "MEMBERSHIP2", "MEMBERSHIP3", "MEMBERSHIP4",
"MEMBERSHIP5", "MEMBERSHIP6", "MEMBERSHIP7", "MEMBERSHIP8",
"MEMBERSHIP9", "MEMBERSHIP10", "MEMBERSHIP11", "MEMBERSHIP12",
"MEMBERSHIP13", "MEMBERSHIP14", "MEMBERSHIP15", "MEMBERSHIP16",
"MEMBERSHIP17", "MEMBERSHIP18", "MEMBERSHIP19", "MEMBERSHIP20",
"MEMBERSHIP21", "BIRTHCNTRY", "TITLE_3_IMMG", "US_SCH_ENT_DT"
FROM "SYS_SQLLDR_X_EXT_ASTU_STUDENT"';

--statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
EXECUTE IMMEDIATE 'DROP TABLE "SYS_SQLLDR_X_EXT_ASTU_STUDENT"';

EXECUTE IMMEDIATE 'DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001';

EXECUTE IMMEDIATE 'DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000';
--EXCEPTION
-- WHEN OTHERS
-- THEN
-- ROLLBACK;
END dsdp0001;


Kathy Jones
DBA
Clark County School District
LAs Vegas NV
Re: getting kup-00554 when creating external table [message #195642 is a reply to message #195637] Fri, 29 September 2006 20:39 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following two lines:

BADFILE SYS_SQLLDR_XT_TMPDIR_00000/ASTU.bad
DISCARDFILE SYS_SQLLDR_XT_TMPDIR_00000/ASTU.dsc

should be:

BADFILE ''SYS_SQLLDR_XT_TMPDIR_00000'':''ASTU.bad''
DISCARDFILE ''SYS_SQLLDR_XT_TMPDIR_00000'':''ASTU.dsc''

There may be other errors or problems with permissions. It would be better if you did as much as possible in sql rather than having to use dynamic sql in a pl/sql procedure. It is easier to debug if you first test your code in sql.
Previous Topic: I keep trying fix these but it keep coming up syntax errors. PLEASE HELP (merged 3 threads)
Next Topic: Converting from normal table to partition table
Goto Forum:
  


Current Time: Thu Dec 08 10:09:53 CST 2016

Total time taken to generate the page: 0.05346 seconds