The most important wget command line options (flags)

Note to self: remember those wget flags and you'll be fine:

-r, --recursive specify recursive download. -H, --span-hosts go to foreign hosts when recursive. -l, --level=NUMBER maximum recursion depth (inf or 0 for infinite). -np, --no-parent don't ascend to the parent directory. -nd, --no-directories don't create directories. -x, --force-directories force creation of directories. -nc, --no-clobber skip downloads that would download to existing files. -k, --convert-links make links in downloaded HTML point to local files. -p, --page-requisites get all images, etc. needed to display HTML page. -A, --accept=LIST comma-separated list of accepted extensions. -R, --reject=LIST comma-separated list of rejected extensions. -w, --wait=SECONDS wait SECONDS between retrievals.

Inserting and selecting CLOBs with DBD::Oracle

Here's a table with a CLOB:

create table tq84_lob ( id number primary key, c clob )

With Perl and DBD::Oracle, the CLOB in the table can be filled like so:

my $sth = $dbh -> prepare(q{ insert into tq84_lob values ( 1, empty_clob() ) }); # setting ora_auto_lob to false: # fetch the «LOB Locator» instead of the CLOB # (or BLOB) content: my $c = $dbh -> selectrow_array( "select c from tq84_lob where id = 1 for update", {ora_auto_lob => 0} ); $dbh -> ora_lob_write( $c, 1, # offset, starts with 1! join '-', (1 .. 10000) );

A CLOB can be selected like so:

my $c = $dbh -> selectrow_array( "select c from tq84_lob where id = 1", {ora_auto_lob => 0}); my $count = 0; while (my $buf = $dbh->ora_lob_read($c, 1+$count*1000, 1000)) { print $buf; $count++; }

A Perl wrapper for Oracle's UTL_FILE package

I finally found time to write a simple wraper for Oracle's UTL_FILE package that allows to read a file on the database server with perl.

Here's a simple perl script that demonstrates its use:

use warnings; use strict; use OracleTool qw(connect_db); use OracleTool::UtlFile; my $dbh = connect_db('username/password@database') or die; my $f=OracleTool::UtlFile->fopen($dbh,'DIR','file.txt','r'); my $line; while ($f -> get_line($line)) { print "$line\n"; }

The code is on github: OracleTool.pm and UtlFile.pm.

Checking the value of NLS_LANG in SQL*Plus on Windows

Oracle Support Note *179113.1* offers a, ahem, clever way to display the used value for NLS_LANG on Windows in SQL*Plus.

First, it can be verified if the environment variable NLS_LANG is set:

SQL> host echo %NLS_LANG%

SQL*Plus will answer with either something similar to

AMERICAN_AMERICA.WE8MSWIN1252

or with

%NLS_LANG%

In the first case, the environment variable is set and its value, as displayed by the echo command is the value for NLS_LANG.

If the variable is not set, that is in the second case, the following trick allows to determine its value none the less:

SQL> @.[%NLS_LANG%].

There are again two possibilities how SQL*Plus will react. Either

SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]..sql"

or

SP2-0310: unable to open file ".[%NLS_LANG%]."

In the first case, the value for NLS_LANG is set in the Windows registry (to the value between [ and ]). In the second case, NLS_LANG is not even set in the Windows registry.

Incidentally, this seems to be achieved much easier like so

SQL> select sys_context('userenv', 'language') from dual;

Little things that make live easier #8: Using ghostscript to merge PDFs

Today, a friend asked me if I could join multiple PDFs into one. Apparently, she tried it with one of the various online mergers, but without success.

A quick google search brought me to the always helpful superuser.com site, particularly this answer.

In short, multiple PDFs can be joined so

c:\> gswin32c.exe -dBATCH -dNOPAUSE -sDEVICE=pdfwrite ^ -sOutputFile="final.pdf" ^ 1.pdf 2.pdf 3.pdf ... n.pdf

Creating an (import-) SQL file with DBMS_DATAPUMP

DBMS_DATAPUMP can create SQL files from a schema so that these files can later be run to re-create the schema.

This is described in Oracle Note 1519981.1: How to Generate A SQL File Using The DBMS_DATAPUMP_API?. Unfortunately, the note does not explicitely state that the creation of such an sql file consists of two steps, first the schema has to be dumped ordinarly, then, the dumped file has to be turned into the desired SQL file.

Here are the steps to create such an SQL file.

First step: creating the dump file

declare datapump_job number; job_state varchar2(20); begin datapump_job := dbms_datapump.open( operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => null, job_name => 'Export dump file', version => 'LATEST' ); dbms_output.put_line('datapump_job: ' || datapump_job); dbms_datapump.add_file( handle => datapump_job, filename => 'export.dmp', directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); dbms_datapump.start_job( handle => datapump_job, skip_current => 0, abort_step => 0); dbms_datapump.wait_for_job(datapump_job, job_state); dbms_output.put_line('Job state: ' || job_state); dbms_datapump.detach(datapump_job); end; /

Second step: turning the dump file into an SQL file

declare datapump_job number; job_state varchar2(20); begin datapump_job := dbms_datapump.open( operation => 'SQL_FILE', job_mode => 'SCHEMA', remote_link => null, job_name => 'Export SQL file', version => 'LATEST' ); dbms_output.put_line('datapump_job: ' || datapump_job); dbms_datapump.add_file( handle => datapump_job, filename => 'export.dmp', directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); dbms_datapump.add_file( handle => datapump_job, filename => 'schema.sql', directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_sql_file); dbms_datapump.start_job( handle => datapump_job, skip_current => 0, abort_step => 0); dbms_datapump.wait_for_job(datapump_job, job_state); dbms_output.put_line('Job state: ' || job_state); dbms_datapump.detach(datapump_job); end; /

Links

Source code on github

My question on dba.stackexchange.com

Cloning an Oracle schema with DBMS_DATAPUMP

Cloning an Oracle schema with dbms_datapump

I have a schema (FROM_SCHEMA_A) that I need to clone on the same database. So, the cloned schema will go by another name, in my case by TO_SCHEMA_A).

In order to make things a bit more complicated, FROM_SCHEMA_A references objects in another schema (named SCHEMA_B). This other schema won't be cloned.

Schema definitions

Before starting with the object definitions in the schemas, I create the needed schemas:

create user from_schema_A identified by p quota unlimited on users; grant create procedure, create session, create table, create trigger, create view to from_schema_A; create user schema_B identified by p quota unlimited on users; grant create session, create table to schema_B;

Here's the definition for the SCHEMA_B schema. It consisists of one table only:

create table table_b_1 ( a number, b varchar2(10) );

Since FROM_SCHEMA_A references this table, it needs some grants:

grant insert, select on table_b_1 to from_schema_A;

Here's the definition for the FROM_SCHEMA_A schema:

create table table_a_1 ( c number, d varchar2(20) ); insert into table_a_1 values (10, 'ten' ); insert into table_a_1 values (11, 'eleven'); create view view_a_1 as select * from schema_b.table_b_1; create package package_a_1 as function count_a return number; function count_b return number; end package_a_1; / create package body package_a_1 as function count_a return number is ret number; begin select count(*) into ret from table_a_1; return ret; end count_a; function count_b return number is ret number; begin select count(*) into ret from view_a_1; return ret; end count_b; end package_a_1; / create trigger trigger_a before insert on table_a_1 for each row begin insert into schema_b.table_b_1 values (:new.c, :new.d); end trigger_a; / -- There's a trigger on the table, so the -- following insersts should fill table_b_1 -- (in schema_b): insert into table_a_1 values (1, 'one'); insert into table_a_1 values (2, 'two');

Export/Import administrator

In order to perform the export and the import, I create a special export import administrator:

create user exp_imp_admin identified by p; grant exp_full_database, imp_full_database to exp_imp_admin; alter user exp_imp_admin quota unlimited on users;

Performing the export and import

With that user, I am able to export the schema:

connect exp_imp_admin/p declare datapump_job number; job_state varchar2(20); begin datapump_job := dbms_datapump.open( operation => 'EXPORT', job_mode => 'SCHEMA', remote_link => null, job_name => 'Clone schema A, export', version => 'LATEST', compression => dbms_datapump.ku$_compress_metadata ); dbms_output.put_line('datapump_job: ' || datapump_job); dbms_datapump.metadata_filter( handle => datapump_job, name =>'SCHEMA_LIST', value =>'''FROM_SCHEMA_A''' ); dbms_datapump.add_file( handle => datapump_job, filename => 'clone_schema_a_export.log', directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); dbms_datapump.add_file( handle => datapump_job, filename => 'clone_schema_a.dmp', -- Note, created will be in UPPERCASE! directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); dbms_datapump.start_job (datapump_job); dbms_datapump.wait_for_job(datapump_job, job_state); dbms_output.put_line('Job state: ' || job_state); dbms_datapump.detach(datapump_job); exception when others then dbms_output.put_line(sqlcode); dbms_output.put_line(sqlerrm); if datapump_job is not null then dbms_datapump.detach(datapump_job); end if; end; /

And the following import actually clones the schema. Of particular insterest is the call of dbms_datapump.metadata_remap

declare datapump_job number; job_state varchar2(20); begin datapump_job := dbms_datapump.open( operation => 'IMPORT', job_mode => 'SCHEMA', remote_link => null, job_name => 'Clone schema A, import', version => 'LATEST', compression => dbms_datapump.ku$_compress_metadata ); dbms_output.put_line('datapump_job: ' || datapump_job); dbms_datapump.metadata_remap( datapump_job, 'REMAP_SCHEMA', 'FROM_SCHEMA_A', 'TO_SCHEMA_A'); dbms_datapump.add_file( handle => datapump_job, filename => 'clone_schema_a_import.log', directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); dbms_datapump.add_file( handle => datapump_job, filename => 'clone_schema_a.dmp', -- Note: export has created the file with UPPERCASE letters directory => 'DATAPUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); dbms_datapump.start_job (datapump_job); dbms_datapump.wait_for_job(datapump_job, job_state); dbms_output.put_line('Job state: ' || job_state); dbms_datapump.detach(datapump_job); exception when others then dbms_output.put_line(sqlcode); dbms_output.put_line(sqlerrm); if datapump_job is not null then dbms_datapump.detach(datapump_job); end if; end; /

Links

Creating an (import-) SQL file with DBMS_DATAPUMP

Source code on github