Das Blog

Debugging one line at a time
$1*/ mo WordPress hosting! Get going with GoDaddy!
Menu
  • Home
  • Coding
    • Entity Framework
    • MVC
    • NHibernate
  • JavaScript
    • TypeScript
  • Hosting
  • SEO
  • Social
  • Database
    • MySql
    • SSIS
  • WP
  • Other
    • CakePHP
    • Error Messages
    • Google Maps
Home
Database
How to create an External Table in Oracle from a Function
Database

How to create an External Table in Oracle from a Function

Codex Discipulus January 4, 2010

Recently I had to create an (oracle) external table inside a function and using the parameter as part of the new external table name.
Creating the external table directory under Oracle was the first step.

Running this command within Oracle will set the directory.

1
CREATE OR REPLACE directory ext_dir as '/mydir/myjob/load'

My function to create the external table starting with a prefix “ex_” followed by the name of the file.
The “.DAT” raw data file has the following structure:

1
2
3
4
5
0001234567890123
0012345678901234
0123456789012345
1234567890123456
0012345678901234

Just one column representing the account numbers. No comma delimited fields and no headers or trailers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
FUNCTION fn_create_ext_table(filename IN VARCHAR2)
RETURN VARCHAR2
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  prefix varchar2(20) := 'ex_';
  retTableName varchar2(100);
BEGIN
 
  retTableName := prefix || filename;
 
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE ' || retTableName;
  EXCEPTION WHEN others THEN NULL;
  END;
 
  EXECUTE IMMEDIATE 'create table ' || retTableName ||
      '(
          AccountNumber VARCHAR2(16)
      )
      organization external (
   type    oracle_loader
   default directory ext_dir
   access  parameters (
   records delimited  by newline
   fields terminated by WHITESPACE
   missing field values are null
)
location (''' || filename || '.DAT' || ''')
        )
        reject limit 100';
    return retTableName;
END fn_create_ext_table;

After running the function, the external table is created consisting of one column: “AccountNumber” of VARCHAR2 (16) type and size. And ready to be used loaded with the raw data.

To call the function and pass the parameter in java I used CallableStatement. (Note: Connection already setup in base class)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
private String CreateExternalTable(String filename) throws Exception {
filename = filename.substring(0, filename.lastIndexOf('.')); //strip extension
String ext_tablename = "";
CallableStatement cs = null;
try {
cs = con.prepareCall("{?= call ABC.fn_create_ext_table(?) }");
cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.setString(2, filename);
cs.execute();
ext_tablename = cs.getString(1);
}
catch (Exception e) {
...
throw e;
}
finally {
// close CallableStatement(cs);
}
return ext_tablename;
}

Hope this helps.
Share
Tweet
Google+
Linkedin
Stumble
Email

About The Author

Codex Discipulus

Hello, my name is David and I am a disciple of coding, always learning and always ready to help. Welcome to my blog, I hope you find it useful, please send your comments and share an article with your friends.

2 Comments

  1. Julio Struthers

    Very good blog article.Much thanks again. Really Great.

    April 24, 2012
  2. Bart Rakyta

    Simply wanna remark on few general things, The website style and design is perfect, the subject material is real excellent : D.

    April 25, 2013

Leave a Reply

Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Hosting ASP.Net Core 3.x Worker Service as …
    Using the Worker template now is possible to host an …
  • Calling an HTTPS WCF Service from Net …
    We all know how to call a WCF service from …
  • How to Add a VS Code Project …
    Let’s try to add a Visual Studio Code project to …
  • Step by Step Token based Authentication in …
    Token Authentication is a very popular method to secure a …
  • How to Ignore NuGet Packages in TFS …
    This should be a short post. It is really just …

Categories

  • CakePHP
  • Coding
  • Database
  • Entity Framework
  • Error Messages
  • General
  • Google Maps
  • Hosting
  • JavaScript
  • MVC
  • MySql
  • Net Core
  • NHibernate
  • SEO
  • SSIS
  • TypeScript
  • Wordpress

Tags

ActiveDirectory analytics apps aspnet CakePHP coding database projects design patterns entity framework error messages fatcow first steps ftp github gitlab godaddy google maps api hangfire Hosting iis7 java javascript membership MVC MySql NHibernate nuget nuget packages oracle owin repository SEO sharp ssis t4 tfs thesis token authentication typescript vs2010 vs2015 vs2017 web api Wordpress wp-pagenavi

Das Blog

Debugging one line at a time
Copyright © 2020 Das Blog
ScottsdaleWebStudio.com © All Rights Reserved 2017