Import or load DBF file to Oracle

I use this package to import data from dbase file – foxpro (dbf) to oracle table,

–this is spec
CREATE OR REPLACE PACKAGE DBF2ORA
AS
— PROCEDURE TO A LOAD A TABLE WITH RECORDS
— FROM A FOXPRO FILE.

— USES A BFILE TO READ BINARY DATA AND DBMS_SQL
— TO DYNAMICALLY INSERT INTO ANY TABLE YOU
— HAVE INSERT ON.

— P_DIR IS THE NAME OF AN ORACLE DIRECTORY OBJECT
— THAT WAS CREATED VIA THE CREATE DIRECTORY
— COMMAND

— P_FILE IS THE NAME OF A FILE IN THAT DIRECTORY
— WILL BE THE NAME OF THE FOXPRO FILE

— P_TNAME IS THE NAME OF THE TABLE TO LOAD FROM

— P_CNAMES IS AN OPTIONAL LIST OF COMMA SEPARATED
— COLUMN NAMES. IF NOT SUPPLIED, THIS PKG
— ASSUMES THE COLUMN NAMES IN THE FOXPRO FILE
— ARE THE SAME AS THE COLUMN NAMES IN THE
— TABLE

— P_SHOW BOOLEAN THAT IF TRUE WILL CAUSE US TO JUST
— PRINT (AND NOT INSERT) WHAT WE FIND IN THE
— FOXPRO FILES (NOT THE DATA, JUST THE INFO
— FROM THE FOXPRO HEADERS….)
PROCEDURE LOAD_TABLE (
P_DIR IN VARCHAR2,
P_FILE IN VARCHAR2,
P_TNAME IN VARCHAR2,
P_CNAMES IN VARCHAR2 DEFAULT NULL,
P_SHOW IN BOOLEAN DEFAULT FALSE
);
END DBF2ORA;
/

Continue reading

Advertisements

Using the dbms_metadata package to extract all the schema ddl with one call

The DBMS_METADATA package provides the API for setting the environment parameters: DBMS_METADATA.SET_TRANSFORM_PARAM() procedure.
The private procedure SetEnvironment() contains all the environment setup code. The procedure is called from the package initialization section. Therefore, it only executes once per session which is all you need. You want to set it up once at the very beginning.

To Prevents the output from formatting with indentation and line feeds, use the following code,
— for all objects
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘PRETTY’, false);

To generate segment attributes (physical attributes, storage attributes, tablespace, logging, etc.), storage and tablespace clauses for tables, and indexes’ object definitions:
— for tables and indexes
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘TABLESPACE’, true);

To prevent all of the non-referential and referential constraints from being included in the table’s DDL. It also suppresses emitting table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements:
— for tables only
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘REF_CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS_AS_ALTER’, false);

Here the example script,

Continue reading

Oracle DBMS_JOB

· Introduction

The DBMS_JOB package is known as the job queue.

The Oracle job queue provides programs for submitting and executing jobs, changing job execution parameters,

and removing or temporarily suspending job execution at predefined times and/or repeated job execution at regular intervals.

Users on Oracle 10g and above should use the newer DBMS_SCHEDULER package.

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g.

The older job scheduler, DBMS_JOB, is still available.

· Data dictionary views

Data dictionary views that display information about the Oracle job queue :

DBA_JOBS DBA view describes all the jobs in the database.

ALL_JOBS ALL view describes all jobs that are accessible to the current user.

USER_JOBS USER view describes all jobs owned by the current user

DBA_JOBS_RUNNING Lists all jobs in the database that are currently running. This view can be joined with V$LOCK to identify jobs that have locks.

Displaying Information About a Job

The following query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT job, next_date, next_sec, failures, broken

FROM DBA_JOBS;

Displaying Information About Running Jobs

You can also display information about only the jobs currently running.

The following query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.job, log_user, r.this_date, r.this_sec

FROM DBA_JOBS_RUNNING r, DBA_JOBS j

WHERE r.job = j.job;

Job Queue Locks

Oracle uses job queue locks to ensure that a job is executed in only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job.

You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:

SELECT SID, TYPE, id1, id2

FROM v$lock

WHERE TYPE = ‘JQ’;

SID TY ID1 ID2

——— ——— ———

12 JQ 0 14144

1 row selected.

In the query above, the identifier for the session holding the lock is 12. The ID1 column is always 0 for JQ locks. The ID2 column is the job number of the job the session is running.

This view can be joined with the DBA_JOBS_RUNNING view to obtain more information about the job.

· Initialization Parameter

An initialization parameter in the INIT.ORA file for the database. The parameter is,

1. JOB_QUEUE_PROCESSES Controls how many jobs can run, maximum number of job queue slave processes.

If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed.

The JOB_QUEUE_PROCESSES initialization parameter also specifies the maximum number of Jnnn processes that can concurrently run on an instance.

The maximum number of processes that can be specified is 1000.

SQL> show parameter job_queue_processes

SQL> alter system set job_queue_processes = 2 SCOPE=BOTH;

2. _JOB_QUEUE_INTERVAL This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

3. JOB_QUEUE_KEEP_CONNECTIONS This parameter became obsolete above Oracle 8.1.7.

· Procedures of the DBMS_JOB package

DBMS_JOB.SUBMIT Submits a job to the job queue

DBMS_JOB.REMOVE Removes a specified job from the job queue

DBMS_JOB.CHANGE Alters a specified job that has already been submitted to the job queue (job description, the time at which the job will be run, or the interval between executions of the job)

DBMS_JOB.WHAT Alters the job description for a specified job

DBMS_JOB.NEXT_DATE Alters the next execution time for a specified job

DBMS_JOB.INTERVAL Alters the interval between executions for a specified job

DBMS_JOB.BROKEN Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it

DBMS_JOB.RUN Forces a specified job to run

· Submitting a Job to the Job Queue

It chooses job from the sequence sys.jobseq.

Syntax

DBMS_JOB.SUBMIT (

job OUT BINARY_INTEGER,

what IN VARCHAR2,

next_date IN DATE DEFAULT sysdate,

interval IN VARCHAR2 DEFAULT ‘null’,

no_parse IN BOOLEAN DEFAULT FALSE,

instance IN BINARY_INTEGER DEFAULT any_instance,

force IN BOOLEAN DEFAULT FALSE);

Parameter Description

job Number of the job being run. The job number is automatically generated from the JOBSEQ sequence owned by user SYS.

Once a job is assigned a job number, that number does not change.

what Job defenition or PL/SQL procedure to run. The procedure call can have any number of parameters.

next_date Next date when the job will be run. The default value is SYSDATE.

interval Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.

no_parse A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job.

If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.

For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.

instance When a job is submitted, specifies which instance can run the job.

force If this is TRUE, then any positive integer is acceptable as the job instance.

If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.

Example

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => ‘MIS.CV_BILLING_POS_TEST;’

,next_date => TO_DATE(’01/01/4000 00:00:00′,’dd/mm/yyyy hh24:mi:ss’)

,INTERVAL => ‘SYSDATE+1440/1440’

,no_parse => TRUE

);

SYS.DBMS_JOB.BROKEN

(job => X,

broken => TRUE);

SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || TO_CHAR(x));

END;

/

COMMIT;

The commit here is important to assure the newly created job will run

Job Definition

In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.

The following are examples of valid job definitions:

* ,what => ‘PTR.TARGET_REAL(”002”);’

* ,what => ‘get_target(”HANS”, 2007);’

Running a job from a job is not supported. You will receive an error message

“ORA-32317 cannot run a job from another job” error message in the alert file:

DECLARE

jobno number;

BEGIN

DBMS_JOB.SUBMIT(jobno, ‘DBMS_JOB.RUN(1234);’);

DBMS_JOB.RUN(jobno);

END;

Job Intervals

1. Every Second.

INTERVAL => ‘SYSDATE +1/86400’;

2. Every Minute.

INTERVAL => ‘SYSDATE +1/1440’;

3. Every Ten Minute.

INTERVAL => ‘SYSDATE +10/1440’;

4. Every Half Hour.

INTERVAL => ‘SYSDATE +30/1440’;

INTERVAL => ‘SYSDATE + 1/48’;

5. Every Hour.

INTERVAL => ‘SYSDATE +60/1440’;

6. Every Day.

INTERVAL => ‘SYSDATE +1’;

7. Every Day with Fixed Time. ex: every night 6:00pm.

INTERVAL => ‘trunc(SYSDATE +1) + 18/24’;

8. Every Monday at 3.00pm.

INTERVAL => ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’;

9. Every Midnight 12:00.

INTERVAL => ‘trunc(SYSDATE +1)’;

10. Every Week

INTERVAL => ‘trunc(SYSDATE +7)’;

11. Every Month

INTERVAL => ‘add_months(SYSDATE, 1)’;

12. First Thursday of each quarter

INTERVAL => ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’;

If NULL is specified instead of an interval the job will be run once at the specified first execution time then be removed from the job queue.

· Removing a Job from the Job Queue

To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package.

The following statements remove job number 123 from the job queue:

BEGIN

DBMS_JOB.REMOVE(123);

END;

/

Restrictions:

* You can remove currently executing jobs from the job queue. However, the job will not be interrupted, and the current execution will be completed.

* You can remove only jobs you own. If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

Job Execution Errors

The following can cause queued jobs fail :

* a network or instance failure

* an exception when executing the job

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again.

The first attempt is made after one minute.

The second attempt after two minutes

The third after four minutes, and so on, with the interval doubling between each attempt.

If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

However, between attempts, you have the opportunity to correct the problem that is preventing the job from running.

This will not disturb the retry cycle, and Oracle will eventually attempt to run the job again.

· Running a job manually

To run a job manually, you can call the RUN procedure, it is especially useful if you need to run a job which has been marked as broken.

BEGIN

DBMS_JOB.RUN(JOB => 21);

END;

/

The value for NEXT_DATE will be updated based on the current date and time and your formula.

This becomes important if you have a job running at, say 08:00 pm with an interval of SYSDATE + 1, but then you run it manually at 1:23pm,

the next_date will be recalculated at that time and it will now be run at 1:23pm each day.

Run Oracle Procedure or Package in Background Process

I had a form that created in form builder. It’s run a procedure that always take some times (15-30 minutes). So users always have to wait until the procedure finish but he can’t do anything else.

With this condition, user complain how waste of time to use that form. Then i was thinking if that procedure take long time to execute while run from form applet, why not execute it in background process and ask oracle to do it directly from server. So i was thinking to use oracle job to execute the procedure. Because oracle job run in database server, not at the client process, then time to finish must be shorter. So users didn’t have to wait the procedure finish, and he can do other input, edit, print report or other action.

How can it be done ?

Continue reading