Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Data center
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
0
This post first appeared at Oracleinaction by Anju Garg (Myself).

I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasons for duplicating the storage of undo.

First, let’s demonstrate that
  • The Oracle redo contains undo
  • Checkpointing causes
    • Dirty blocks to be written to datafiles
    • Buffers containing undo to be written to undo tablespace
Overview:

Steps
  1. Find out name of the current undo tablespace 
  2. Create an undo tablespace undotbs2
  3. Create a tablespace test_undo
  4. Create a table test_undo_tab in tablespace test_undo
  5. Insert two rows with txt – teststring1, teststring2 in test_undo_tab
  6. Perform manual checkpoint
  7. Switch logs so that new log does not contain string teststring1
  8. Find out name of current redo log
  9. Switch undo tablespace to undotbs2
  10. Update the column value from teststring1 to teststring_uncommitted and do not commit
  11. In another session, update the column value from teststring2 to teststring_committed and commit
  12. Check that both new and old (undo) values have been written to current redo log
  13. Check that undo tablespace does not contain pre update values i.e. teststring1 and teststring2 as undo information is still in buffer cache and checkpoint has not taken place yet
  14. Perform manual checkpoint so that dirty buffers as well as buffers containing undo information are flushed to disk
  15. Check that datafile does contain updated values (both committed and uncommitted)
0
 
LVL 1

Author Comment

by:Anjugarg66
Comment Utility
I have completed the setup for Google authorship.

Kindly do the needful.

Regards
Anju Garg
0
 
LVL 1

Author Comment

by:Anjugarg66
Comment Utility
Hello mlmcc,

    I have made the required change.

Regards
Anju Garg
0
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
2
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
0
SQL Scripts
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
2
SQL Command Tool
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.
0
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
2
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Good Article Voted Yes!!
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
thanks Yashwant for your vote.

regards,
Sloba
0
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
1
 

Expert Comment

by:alsatham hussain
Comment Utility
i can easily understand.

Thanks
0
Meta characters are the characters in a programming language that are interpreted by the programming language to have specific meanings within the language itself.
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

A short introduction on Oracle Apex: 
Oracle Application Express is mainly used to develop Web based applications with SQL and PL/SQL. The Web-based applications can be developed using a web browser which can easily be deployed for any desktop or mobile applications. There are various version that are been released by Oracle. In recent release on May 2015 they released APEX version 5. Oracle recommends that Oracle APEX to be used on latest web browsers available to have a good experience.

Below are the few listed browsers that oracle suggests:
  • Mozilla Firefox 35
  • Google Chrome 40
  • Apple Safari 7
  • Microsoft Internet Explorer 9
 
We can also use the earlier versions of the web browsers by using the legacy themes.

To access full documentation on Oracle APEX, you can open the link from by clicking on the link here

In this article we will see how we can upgrade the existing APEX version to Oracle Application Express Release 5.0.1 version. You can also see my other articles on upgrading APEX version from version 3 to 4 on 11g.
 
Here my DB version is 11g Release 11.2.0.1.0 where my current APEX version is 4.2.6.00.03:
 
Connected to Oracle Database 11g Release 11.2.0.1.0 
Connected as SYS


SQL> SELECT 

Open in new window

0
The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. http://www.linuxmint.com. Once you open the link you will see a download option at the right side of the page, go to the download page from the menu :

pic-2.pngYou can download 32 bit or 63 bit image and I am using 32 bit as my system is with 32 bit OS . I have downloaded and kept it on my specific directory where I can map to my Virtual Box while installing it.
Now open the VMBox and click on New to create virtual machine and map the ISO image to proceed with installing.  Once you set up all the necessary settings on memory space and disk space you will see the screen like below and then you can click on start button to start the installation.

Follow below steps shown with screen shot to start the virtual machine creation: 
2015-07-05-19-21-31-.jpgCick on New and proceed next :
2015-07-05-19-19-08-Oracle-VM-VirtualBoxGive any name to the virtual machine and select the type and version , click Next:
 
2015-07-05-19-19-26-.jpgSelect the memory size you want the VM to have -  I am using 1GB of RAM i.e. 1024 MB approximately. 

2015-07-05-19-19-46-Oracle-VM-VirtualBox
Click on Create button.
2015-07-05-19-20-52-.jpgClick Next. 

2015-07-05-19-21-00-Oracle-VM-VirtualBoxI am trying all default setting for installing the OS but you can choose as per your need. 
2015-07-05-19-21-18-.jpgHere we can set the size of the virtual hard drive . Once filled click on Create button. 

2015-07-05-19-21-31-.jpg Now set the ISO file which was downloaded from Linux Mint website. 
sloba-000006.jpg
1
Elementary operating system is a very pretty, high speed, lightweight Linux operating system for 32 and 64-bit x86 computers. This OS is built on an Ubuntu core. To install elementary OS we need to download the ISO file from its website i.e. https://elementary.io/
pic-1.pngIf you scroll down on the website you will see like below, to download click on Download button as shown below:

pic-2.png
Now you can choose 32 0r 64 bit ISO file:

pic-3.png
pic-4.pngNow open your virtual machine box to create our new VM.
pic-6.pngClick on New button to start:
pic-7.png
Provide the name of the VM you want to create, I used as “elementaryOS”.  Now click on next button and proceed further. 
pic-8.pngSelect the memory size and click on next.

pic-9.pngClick on create.

pic-10.pngClick next.

pic-11.pngpic-12.pngKeep as default and click next and then choose the directory where you want the VM to be installed. Finally click on create button. 

pic-13.pngNow click on the setting gear icon to map the iso file. 


pic-14.png
Now “Ok” button.  After all the basic settings start the VM to install the OS. 

pic-15.png
pic-1.pngNow click on “Install elementary”.

pic-2.pngClick on continue. 

pic-3.pngClick Install Now and proceed with default selection including location as below. 

pic-4.pngSelect the Keyboard type you want to choose I am going with the default option with US English. 

pic-5.pngpic-6.png
0
With the help of flashback queries we have the option to look back in time to a certain state of a database, for example say we did some operation yesterday at 10 AM and today we want to go back to the same state where we started yesterday. So using Oracle flashback queries we have the ability to go back into time to a certain state of a database.

This can be achieved by using the system change number (SCN) or using a timestamp. We also have the option to select a range of system change number or timestamp values that will help us to go back to the point of time. This option will only help us to see the past stage of the database, not the future.

How this is possible is the first question that we can think about. This is possible as because all the data in databases are stored in data files and the redo log file is more essential here; in other words we are doing a rollback.  

Syntax of flashback queries:
 
SELECT … 
FROM [schema.]table [alias]
[
	  AS OF { SCN | TIMESTAMP } expr
	| VERSIONS BETWEEN { SCN | TIMESTAMP}
		{ expr | MINVALUE } AND { expr | MAXVALUE } 
]
[ WHERE … ] 
[ GROUP BY … ] 
[ ORDER BY … ]

Open in new window

AS OF” will take you to back in time to a previous state of a table against a time or system change number.  For example we want to see the records in a table 10 minutes ago then we can use the “AS OF" condition with the SCN or timestamp with additional conditions, if required, like “where” / ”group by” / “order by” . The “VERSIONS BETWEEN
0
I got a requirement to export the table data into CSV file. The major issue was like the column values from the table needed to be transposed and used as a header on the flat file. The challenge was that the column values were more than 30 characters and can reach up to 4000 characters.
 
I am using the sample table to simulate the issue and to check how we can resolve this problem.
 
Here is my sample table:

pic-4.JPG
Create the sample table:
 
create table MY_SURVEY_RESP
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME	VARCHAR2(4000),
RESPONDENT	VARCHAR2(100),
RESPONSE  VARCHAR2(100)
);

Open in new window


To insert some sample data let's execute the below PLSQL block:
 
BEGIN
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
            

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
Comment Utility
For reader reference:

Here's the question referenced above where slobaray asked how to do it.
http://www.experts-exchange.com/Database/Oracle/Q_28680635.html
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
Thanks @ sdstuber : I provided the link to the question on the article including the information.

Regards,
Sloba
0
Introduction: When a new description on subject like subqueries is read, with various types and each with unique features, often readers try to make a mental picture of the process. Such a mental picture as a point of reference becomes very helpful when one attempts to apply the techniques to solve a problem. This is why the old saying "One Picture is Worth a Thousand Words" holds true. 

I have prepared the content in this article with visual presentation followed by descriptions and samples to make the path much easier for those interested in having a good understanding of the world of subqueries and retention of techniques to apply them later. Your comment below and/or indicating if this article was helpful is very much appreciated and could be used to improve my future work.

You may download Northwind.bak to restore in your local SQL Server from https://northwinddatabase.codeplex.com/ to practice or test most of the examples included in this article.  Also, consider printing figure 1 below to keep it handy for reference as you are reading the article.

0x.pngIn T-SQL we can use the results returned from one query in another. The embedded or referenced subquery (inner query) returns required data to enable the outer query to perform more complex operations compared to a simple select clause. Below is the list of different subqueries discussed later in detail:

1) Self-contained subqueries (embedded).
2) Correlated …
5
 

Expert Comment

by:Rayne
Comment Utility
Thank you Mike, this really clears a lot of key concepts :)
0
This article started out as an answer to a question asked here on EE on why someone would want to use ETL ("Extract, Transform, Load") tools rather than writing application code or using tools native to the database platforms. My response and this article are written from the perspective of a database guy.

I've seen a few ETL tools like the open source Talend; but the only tool I've actually used in a production environment is Informatica so that tool heavily influences my perception. I never became an expert with Informatica but I got to work with some people that were; and, having survived, I'm glad for the experience.

First a few things that I turned out to LOVE.
 
  1. Self documenting Data Flow. Trying to answer the question "Where did you get this piece of information?" can be difficult (and that's being kind) with scripts, programs, stored procedures and so on. However, tracing through a workflow is easy. Of course, if the processing is complex with lots of data sources and many transformations it may still take awhile to decipher it all; but the flow itself is still essentially just connecting the dots.
  2. Semi-agnostic processing. I absolutely detest platform agnostic development. BUT... tools like Informatica have a nice compromise to them. If you change platforms of your data sources, you must rewrite your source qualifiers, but... after that you're essentially done. So, your inputs can use SQL syntax, stored procedures,
5
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Excellent article, voted Yes.

A couple of thoughts based on SSIS experience:
Aggregates and sorts are referred to as blocking transformations, where the entire set of data needs to be loaded before a sum/count/sort can be performed, which would decrease a lot of the benefits of running streams of data.
File manipulation is not difficult in SSIS, especially with the third party tool Pragmatic Works' Task Factory, however most large shops I've worked in have a separate FTP/Feed Transport guy/team where that person was in charge of moving/encrypting files.
0
Normally when we create a table we need to specify the datatypes of the columns that are need to be created in Oracle, but here we used to have few limits on Varchar2 , NVarchar , Raw datatypes .... 
For example if I need to store a string having more than 4000 characters in length then I cannot store it on a Varchar2 column , till 11g we used to store in a CLOB  column. If we see realistically I don't think that we really need the clob column here might be we need only 7000 characters space within a column where we can store the data but till 11g we can still utilize it within PLSQL which can handle till 32k. So in short we can say like before Oracle version 12c we were able to use the datatype till 4000 on Varchar2, NVarchar2, Raw Data Types but now in 12c onwards this is been extended till 32767 but internally in 12c this get stores in CLOB column for extended varchar2 columns [This point was suggested by slightwv]. Till 11gR2 we were able to use the 32k datatype on PLSQL but not on table level.  Many time we see that the datatype might not required to be a CLOB but might just need few more characters then 4000. Where we normally use a substring to insert only 4000 characters , which is not not requuired in 12c database by using the extending the string size.
This can be achieved by extending the max_string_size parameter on system level and executing utl32k.sql present on oracle home folder after …
1
 
LVL 7

Expert Comment

by:Docteur_Z
Comment Utility
Question : Is there any counterpart to activating this feature ?
My idea : why not make it default on all new 12c DBs ?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Question : Is there any counterpart to activating this feature ?

I do not understand what you mean by 'counterpart'.

If you mean any other way to store more than 4000 characters in a varchar2, then no.
If you mean a different way to allow the use, no as well.

The online docs are always a great source of information:
http://docs.oracle.com/database/121/REFRN/refrn10321.htm#REFRN10321

>>My idea : why not make it default on all new 12c DBs ?

CLOBs come with a cost.  The decision to use them must be made by the individual not 'chosen' by Oracle.
0
Web application components that can be published and used on the Internet are called web services. Here we will see how we can use Oracle PLSQL to access web services.

In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the  web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/webservices/tempconvert.asmx?op=CelsiusToFahrenheit

We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.

Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:

pic-1.jpgLet's put 10 as input to the parametrer value for Celsius and check what output we are getting:

pic-2.jpgAfter giving the input click on Invoke button to get the response as like below:

pic-3.jpgSo from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.
<string xmlns="http://www.w3schols.com/webservices/">50</string>

Open in new window

2
Microsoft Certification Exam 74-409
LVL 1
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

This article shows how to upgrade from APEX version 3.2.1.00.10 to 4.2.6.00.03 on Oracle Database 11g Release 11.2.0.1.0.
First of all why we need to upgrade, the default version provided by Oracle doesn't support few of the features which is later version they improved the PDF printing options , Mobile application builder and many more.

The details on the version changes can be retrieved from the below link:
By upgrading the APEX version we are not upgrading the Oracle database, this will remain as it is but all the latest options to build an small application can be done having more features on upgrading the application side. This is not make any significance of performance improvement lastly this supports HTML 5 too and the basic themes are also present, where as we can also configure it. 

By default APEX version 3  comes when we install Oracle database from 10g onwards.  Once we have it installed we need to unlock the "anonymous " user and check if our HTTP port by executing the below SQL :
 
SQL> select dbms_xdb.getHTTPPort from dual;

Open in new window

Now if we see any result then fine else we can set the HTTP port by executing the below code:
 
EXEC dbms_xdb.sethttpport(8082);

Open in new window

Now if we open the URL on our local we will see the below screen for login:

PIC-1.pngInitially the screen looks as like above. To check the version details we can login to our database to verify :

Login as sys user and check:


Open in new window

0
If you are anything like me, everytime you get a new computer or need to do a fresh install of your work computer you immediatly go and re-install SQL Developer on your machine.  You get all your connections setup and you think you are good to go.  Then you try to write your first query. 
 
Select * from my_table where date_column > '2010-05-05'. 

Open in new window


You run the query and you get an error message that the date is in the wrong format.   SQL Developer comes preset with a date format that it wants to use, and it is never the one I want.

Now to fix this you can always use the to_date function provided in pl/sql, but if you are writing a lot of queries that use dates this can become annoying.  It is nice to be able to just plugin what ever date format you always use and have SQL Developer remember this syntax.  The good news is you can do this in SQL Developer.  I always have a hard time finding the exact setting, so here is exactly how you would do it.

In the top menu go to the Tools -> Preferences -> Database -> NLS

SQL-Developer-Date-Step-1.jpg
SQL-Developer-Date-Step-2.jpg
Within the NL set the Date Format, Timestamp Format and the Timestamp TZ Format.  Being from the United States, below are the values I like to use.

Date Format: YYYY-MM-DD HH24:MI:SS
Timestamp Format: YYYY-MM-DD HH24:MI:SSXFF
Timestamp TZ Format: YYYY-MM-DD HH24:MI:SSXFF TZR

Once you hit 'OK', your settings will now be updated.  Now you will be able to write queries with specified dates in the format you wanted to use.  Now you run your query from above again.
 

Open in new window

2
I'm working on giving a course in Oracle advanced SQL. One of the topics is the MODEL clause. I needed documentation to explain this clause, so I made an article for this.

This clause allows you to use your data like an array, do calculations on that array and then return the calculated data.  This sounds a lot like working with a spreadsheet.

In this part I I'll be looking at the model clause with a single dimension.
 

Prerequisites: A running database, data and a user.

To get everything out of this, I installed my very first 12c Oracle enterprise database with DBCA. It's a single instance (non-plugged) enterprise edition 12.1.0.1 database on a Windows Server 2008 machine named TESTU12. As data I used the separate samples download from oracle.com that contains shemas hr, pm, sh, oe, ix and bi. I created user TEST with the DBA role and create session granted. (It's for testing so the DBA role is least troublesome). For testing I'm using SQLplus.
 

The model clause syntax

Syntactically the model clause sits behind the group by clause in the query block:
 
query-block.gifThe model clause itself has the MODEL keyword, three optional parts and the main model section:
main-model.gif
The main model section has two mandatory sections: the model column clause and the model rules clause:
main-model.gifThe model columns clauses has this definition:
model-column-clauses.gifand the model rules clause is like so :
model-rules-clause.gif


Minimal select statement with model clause

2
 
LVL 15

Administrative Comment

by:Eric AKA Netminder
Comment Utility
Geert,

Congratulations! Your article has been Accepted and has been awarded Approved status.

ericpete
Page Editor
0
BRIEF HISTORY

From time to time during my journey through the "IT" realm, I have been faced with the necessity to code a program or module to parse some source text and produce an array containing the tokens, operators and delimiters thereof, the ultimate goal being that of performing some type of analysis of the original sentence.

All started during the dinosaur era when I was tasked with coding a program to translate one flavor of “Business Basic” to another flavor of “Business Basic”. And surely there was the need to code a parsing routine to produce a structure that could be analyzed and cross-referenced in order to automatically produce the equivalent statements in the target syntax.

Some years passed by and during a project to design a relational database we needed to create a metadata catalog from the source COBOL-based legacy application’s record definitions, working-storage areas, CICS screens, programs and other related structures . Consequently a parsing module was required.

More years passed by, until recently I needed to analyze arithmetic formulas using the Shunting Yard algorithm and yet another parsing program was coded to go.

OVERVIEW

According to Wikipedia, the term ”parsing” is used to refer to the formal analysis by a computer of a sentence or string of words and breaking it down into its constituents, resulting in a parse tree showing their syntactic relation …
1
 
LVL 29

Author Comment

by:MikeOM_DBA
Comment Utility
Thanks!
The source code seems to be missing -- added the source code.
0
 
LVL 29

Author Comment

by:MikeOM_DBA
Comment Utility
Corrected "Shunting_Yard" and other typos in the PL/SQL code.

   text2token-pkg.sql
0
You have a query that takes too long and you want help to analyze the execution plan?

Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.

Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.
 

Method 1 - Without Tuning Pack

I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:
 
 
set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Open in new window

 
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:
 
 
-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Open in new window

 
Finally I get the execution plan to a text file:
 
 
spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
spool off

Open in new window

 
The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.
 

Method 2 - With Tuning Pack


When you have tuning pack, you have access to the great SQL monitoring feature.
 

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
When I add the first two blocks (without parameters) above and below my query and then execute, I receive the set in the below image, with no mention of where the .txt file is, so it's not clear how I should  proceed. Screen-Shot-2016-01-28-at-3.18.13-PM.pngOracle SQL Developer v4.1.2.20.
0
 

Expert Comment

by:Jacob Forest
Comment Utility
You can do it with dbForge Studio for Oracle (https://www.devart.com/dbforge/oracle/studio/). It provides execution plan, step-by-step code execution, breakpoints, watches, a call stack and other useful features.
0
Overview

This past end of year brought us new requirements to download “compliance” files from government entities and the additional burden of maintaining these file updated monthly.

Some of these files are ‘Dbase IV’ (.dbf file format) which made it a manual procedure to load and maintain the data in Oracle tables.

To deal with this situation and be able to automate this process we coded a PL/SQL procedure to transform these “.dbf” files to pipe-delimited files and use as part of the ETL script.

This “ATTACH_DBF_FILE” procedure  provides the means to convert a ‘Dbase IV’ database file to a pipe-delimited file and attach the converted file to an oracle database as an external table.

Perhaps there already exists a Unix utility that does this, but with budget and time constraints we had to code our own.

How it Operates
 

1.

Open the dbf file as BLOB.

2.

Analyze and extract metadata for the structure and fields from the dbf file header:

a.      Field name
b.      Type
c.      Length
d.      Decimals
e.      Auto-Increment

3.

Build and execute DDL to create the external table.

4.

Extract the data from the dbf file and write to pipe delimited file.
Parameters

PROCEDURE Attach_Dbf_File
(
  P_Directory         VARCHAR2
, P_File_Name        VARCHAR2
, P_Schema            VARCHAR2
, P_Delimiter         VARCHAR2    DEFAULT '|'
)

Parameter   Description
3
 
LVL 38

Expert Comment

by:Geert Gruwez
Comment Utility
nice article ... i could have used this 2 decades ago !
0

Oracle Database

78K

Solutions

25K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.