ORACLE External Table – DYNAMIC ACCESS GENERATOR

ORACLE External Table – DYNAMIC ACCESS GENERATOR


Australien - Perth - Impression #australien #perth #impression -------------------------------------------------------------- ctpm - Synergy von Business & Gesundheit #synergyvonbusinessundgesundheit #ctpmsynergyvonbusinessundgesundheit -------------------------------------------------------------- Business-Unit: CTPM - BUSINESS IT-Consulting - Development & Programming - Administration - Business Analysis - Solution Architectures - Testmanagement Management-Consulting - Career Planning - Start-up Coaching & Consulting - Freelancer Management - Recruitment Consultant - Backoffice - PMO Training & Development CTPM - HEALTH Health & Wellness - Burnout - Prevention - Education & Training - Coaching - Health-related Travel Massage & Workout Saltgrotto CTPM - ACCOMMODATION Bed & Breakfast Apartment Conference Room Meetingpoint CTPM - MOVE Corporate Sailing & Hiking - Coaching - Teambuilding & Events Boating School - Boating License - Skippertraining Rent a Skipper Guests Hiking Personal Training -------------------------------------------------------------- Tags #ctpm #ctpm-business #ctpmbusiness #business #it-consulting #itconsulting #it #consulting #development #programming #developmentandprogramming #developmentprogramming #oracle #plsql #oracledba #webdesign #wordpress #oracleadministration #businessanalysis #solutionarchitectures #testmanagement #testmanager #softwarearchitect #management #consulting #managementconsulting #careerplanning #start-upcoaching #start-up-coaching #startup-coaching #startupcoaching #start-upconsulting #start-up-consulting #startup-consulting #startupconsulting #freelancer #freelancermanagement #freelancer-management - #recruitment #consultant #recruitmentconsultant #backoffice #PMO #training #development #traininganddevelopment #trainingdevelopment #ctpm-health #ctpmhealth #health #health #wellness #healthwellness #burnout #Prevention #burnoutprevention #education #training #healthtraining #coaching #healthcoaching #healthrelatedtravel #massage #workout #healthmassage #healthworkout #saltgrotto #saltcave #salzgrotte #ctpm-accommodation #ctpmaccommodation #accommodation #bedandbreakfast #bedbreakfast #bed-breakfast #privatzimmer #gästezimme #hotelzimmer #monteurzimmer #messezimmer #messebetten #messeköln #messecologne #messedüsseldorf #übernachtung #unterkunft #schlafen #Apartment #appartment #ferienwohnung #conferenceroom #conference-room #meetingpoint #büro #büroaufzeit #office #ctpm-move #ctpmmove #move #corporatesailing #corporatehiking #corporate-sailing #corporate-hiking #corporate #sailing #corporate #hiking #movecoaching #teambuilding #teamevents #boating #boatingschool #boating-school #bootsschule #sportbootsschule #segelschule #boatinglicense #boating-license #boating #skippertraining #skipper #skippertrainer #rent-a-skipper #rentaskipper #guestshiking #personaltraining #personaltrainer #personal-training #personal-trainer -- #cologne #köln #koeln #berlin #lindlar #rheinland #germany #deutschland #nrw #europa #nordrhein-westfalen #nordrheinwestfalen #oberbergischerkreis #oberberg

Australien – Perth – Impression


ORACLE – Seminare, Schulungen, Kurse und Workshops

  • Professioneller Einstieg in Oracle SQL
  • Oracle PL/SQL und DB-Programmierung
  • Oracle SQL- / PL/SQL-Tuning
  • Datenmodellierung und Datenbankdesign
  • Oracle SQL für Umsteiger
  • Oracle Datenbankadministration DBA
  • Oracle Backup & Recovery
  • Oracle Forms-Developer / Oracle Developer Suite

ORACLE – Dozenten, Trainer und Coaches

  • Dozenten und Coaches

ORACLE External Table – DYNAMIC ACCESS GENERATOR – Sample

/*

Thema: External Table - Zugriff dynamisch aus COL generieren
-- Drop ..
-- Create table ... organization external
-- create or replace view

Autor: Stefan Müller

*/ 

declare
  v_quelle varchar2(50) := 'XSC_Q.CSV';
  v_target varchar2(30) := 'TARGET_TAB';
  v_table varchar2(30)  := 'NEW_'||v_target;
  v_dir varchar2(30)    := 'IMPORT';

  v_col     clob;
  v_coltype clob;

  rec_rc SYS_REFCURSOR;
  v_rc_sql clob := 'select * from col where tname = '''||v_target||''' order by colno';
  v_rec col%rowtype;

-- Basis - DDL-Syntax
  v_sql clob :=
    'drop table <<tab>>;'||chr(10)||
    'create table <<tab>>'||chr(10)||
    '('||chr(10)||
    '<<coltype>>'||
    ')'||chr(10)||
    'organization external'||chr(10)||
    '('||chr(10)||
    ' type ORACLE_LOADER'||chr(10)||
    ' default directory <<dir>>'||chr(10)||
    ' access parameters '||chr(10)||
    ' ('||chr(10)||
    ' RECORDS DELIMITED BY NEWLINE'||chr(10)||
    ' SKIP 1'||chr(10)||
    ' FIELDS TERMINATED BY '';'''||chr(10)||
    ' missing field values are null'||chr(10)||
    ' ('||chr(10)||
    '<<col>>'||
    ' )'||chr(10)||
    ' )'||chr(10)||
    ' location (<<dir>>:''<<quelle>>'')'||chr(10)||
    ')'||chr(10)||
    'reject limit UNLIMITED;'||chr(10)||
    ''||chr(10)||
    'create or replace view V_<<tab>>'||chr(10)||
    '('||chr(10)||
    '<<col>>'||
    ')'||chr(10)||
    'as select '||chr(10)||
    '<<col>>'||
    'from <<tab>> et;'||chr(10);
begin
-- Directory ersetzen
  v_sql := replace(v_sql,'<<dir>>',v_dir);
-- CSV-Quelle ersetzen
  v_sql := replace(v_sql,'<<quelle>>',v_quelle);
-- Tabelle ersetzen
  v_sql := replace(v_sql,'<<tab>>',v_table);
  open rec_rc for v_rc_sql;
  loop
    fetch rec_rc into v_rec;
    exit when rec_rc%notfound;
    if rec_rc%rowcount = 1 then
       v_col := v_rec.cname||chr(10);
       v_coltype := v_rec.cname||' varchar2(100)'||chr(10);
    else
       v_col := v_col||','||v_rec.cname||chr(10);
       v_coltype := v_coltype||','||v_rec.cname||' varchar2(100)'||chr(10);
    end if;
  end loop;
  close rec_rc;
-- Spalten exkl. Type ersetzen
  v_sql := replace(v_sql,'<<col>>',v_col);
-- Spalten inkl. Type ersetzen
  v_sql := replace(v_sql,'<<coltype>>',v_coltype);
  dbms_output.put_line (v_sql);
end;

--
-- <<quelle>> --> Dateiname im Directory
-- <<tab>> --> Tabellenname
-- <<col>> --> Spalten - Kommasepariert
-- <<coltype>> --> Spalten & Type - Kommasepariert
--

Ergebnis: SAMPLE

drop table NEW_TARGET_TAB;
create table NEW_TARGET_TAB
(
COL1 varchar2(100)
,COL2 varchar2(100)
,COL3 varchar2(100)
)
organization external
(
type ORACLE_LOADER
default directory IMPORT
access parameters
(
RECORDS DELIMITED BY NEWLINE
SKIP 1
FIELDS TERMINATED BY ‚;‘
missing field values are null
(
COL1
,COL2
,COL3
)
)
location (IMPORT:’XSC_Q.CSV‘)
)
reject limit UNLIMITED;

create or replace view V_NEW_TARGET_TAB
(
COL1
,COL2
,COL3
)
as select
COL1
,COL2
,COL3
from NEW_TARGET_TAB et;

Möchten Sie das auch einsetzen?! Worauf wartet Sie? Der Rest vom Leben beginnt JETZT!


TIPP


ctpm – Business
– Oracle Training & Development –
Frankenwerft 3
50667 Köln

+49 221 277446-45