Returning clause with insert does not work!

On the web there is a lot of material about the new 10g feature returning clause with examples.
However it looks like those who have published these examples have not tested the code and just typed it out.
See : http://orafaq.com/node/34

Returning clause works fine with Update and Delete.

Ex:

declare
tot_count number;
begin
delete from pn_1
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

However with insert

table pn_1 is created with two columns s1 varchar2(30), a2 number

declare
tot_count number;
begin

insert into pn_1
select table_name,1
from user_tables
RETURNING count(a1) INTO tot_count;

dbms_output.put_line(tot_count);
end;

it gives syntax error
ORA-06550: line 5, column 16:
PL/SQL: ORA-00933: SQL command not properly ended

See Metalink Note:302910.1

So still the best way to note how many records have been inserted :


declare

begin
insert into pn_1
select table_name,1 b
from user_tables;
--RETURNING sum(b) INTO tot_count;
dbms_output.put_line(sql%rowcount);
exception
when others then
dbms_output.put_line(sqlerrm);
end;

How to Check ORACLE_HOME from the Database

First Method:

select NVL(substr(file_spec, 1, instr(file_spec, '\', -1, 2) -1) , substr(file_spec, 1, instr(file_spec, '/', -1, 2) -1)) folder
from dba_libraries
where library_name = 'DBMS_SUMADV_LIB'

2nd Method (Requires priv to run sys.dbms_system) :

DECLARE

folder VARCHAR2(100);

BEGIN

sys.dbms_system.get_env('ORACLE_HOME', folder);
dbms_output.put_line(folder);

END;

Archivelog Mode

Just a note of all the things I forget about Archivelog mode

The easiest way to find out if a database is running in archivelog mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

And for more reference:

Archive log related stuff

dbms_stats

There are so many combinations of parameters, how does one decide the most optimum parameters when using dbms_stats?

One method I found useful is checking the accuracy of num_distinct_rows against time taken for gathering statistics. You want good accurate stats but produced in a short time.

First compile this Function to automatically and accurately calculate the number of distinct rows:

create or replace function pn_distinct(p_table in varchar2, p_col in varchar2)
return number
is
q_text varchar2(4000);
v_records number;

begin

q_text := 'select count(distinct ' p_col ') from 'p_table ;execute immediate(q_text) into v_records;
return v_records;

end;

Run the dbms_stat tweaking with the different parameters e.g. to collect stats on all tables in schema that begin with DWH in the example below;

create or replace procedure dwh_gather_stats
as
cursor c1 is
select table_name
from user_tables
where table_name like 'DWH%';

begin

For c1rec in c1 loop
dbms_stats.gather_table_stats(ownname=>'BI_USER',
tabname=>c1rec.table_name,
estimate_percent=>10,
method_opt=>'for all columns size auto',
cascade=>true);

end loop;

end;

record the time taken with each set of parameters.

and then run the following query and decide on a trade off between accuracy of the stats and time taken to gather the stats by comparing the num_distinct and actual_distinct columns. You can use this method to compare dbms_stats against analyze as well.


select p.table_name,p.last_analyzed,t.COLUMN_NAME,t.NUM_DISTINCT,
pn_distinct(p.table_name,t.column_name) actual_distinct
from user_tab_columns T,
user_tables P
where p.table_name like 'DWH%'
and p.table_name = t.TABLE_NAME

nls_characterset etc.

select *
from sys.props$

will provide information on all the nls_ settings.

Using dbms_datapump for import

Doing the import this way can be done straight over a database link so one doesn't even need to do an export.

create or replace procedure dwh_import
as

ind NUMBER; -- Loop index number
jobhandle NUMBER; -- Data Pump job handle
l_job_name varchar2(100); -- Job Name
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- Keeps track of job state
le ku$_LogEntry; -- work-in-progress and error messages
js ku$_JobStatus; -- Job status from get_status

sts ku$_Status; -- Status object returned by get_status

BEGIN

l_job_name := 'DWHIMPORT'to_char(sysdate,'YYYYMMDDHH24MISS');
jobhandle := DBMS_DATAPUMP.OPEN(operation => 'IMPORT',
job_mode => 'TABLE',
remote_link => 'APOLLO',
job_name => l_job_name,
version => 'COMPATIBLE');

dbms_output.put_line('jobhandle is : 'jobhandle);

DBMS_DATAPUMP.add_file( handle => jobhandle,
filename => 'DWHIMPORT.log',
directory => 'DATAPUMPX',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

dbms_output.put_line('Import Log file is created..');

DBMS_DATAPUMP.SET_PARALLEL(jobhandle, 1);
dbms_output.put_line('parallel set to 1');

DBMS_DATAPUMP.METADATA_REMAP(handle => jobhandle,
name => 'REMAP_SCHEMA',
old_value => 'WD_USER',
value => 'BI_USER');
dbms_output.put_line('metadat_remap');

DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'TABLE_EXISTS_ACTION','REPLACE');
dbms_output.put_line('set_parameter');

-- dbms_datapump.metadata_filter (handle => my_handle, name => 'NAME_EXPR',
-- value => 'LIKE ''DWH_%''', object_type => 'TABLE');

DBMS_DATAPUMP.metadata_filter( handle => jobhandle,
name => 'NAME_EXPR',
value => 'IN (''DWH_CANVASS'',''DWH_CANVASS_DATE'',''DWH_CHANNEL'',
''DWH_CUSTOMER'',''DWH_CYCLE_DATE'', ''DWH_DATE'',
''DWH_DOS_APPOINTMENTS'',''DWH_DOS_SALESREP_TARGET'',
''DWH_DOS_STATE_OF_PLAY'',''DWH_DOS_TEAM'',''DWH_GG_ICANVASS'',
''DWH_GG_WD_CHANNEL'',''DWH_GG_WD_PRODUCTS'',''DWH_GP_WD_CHANNEL'',
''DWH_GP_WD_PRODUCTS'',''DWH_MANDAYS_ACTUALS'',
''DWH_MANDAYS_PLANNED'',''DWH_PA_WD_CHANNEL'',''DWH_PA_WD_PRODUCTS'',
''DWH_PM_WD_CHANNEL'',''DWH_PM_WD_PRODUCTS'',''DWH_PRODUCT'',
''DWH_REP'',''DWH_SALES'',''DWH_SALES_STATUS'',''DWH_TARGET'',
''DWH_UNIT'',''DWH_WD_CANVASS_GROUPS'',''DWH_WD_CHANNEL'',
''DWH_WD_CHANNEL_GRP'',''DWH_WD_DAYOFF'',''DWH_WD_FINANCE_MONTH'',
''DWH_WD_PRODUCT'',''DWH_REFRESH_DATES'',''DWH_MTG_TARGET'',''DWH_DAY_TARGET_ALL'',
''DWH_DAY_TARGET_SALES'',''DWH_DAY_TARGET_CANV'')');
dbms_output.put_line('Name Export');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_TAB_SMALL',
value => 'BI_USER_TS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_TAB_LARGE',
value => 'BI_USER_TS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_IND_LARGE',
value => 'USERS');

dbms_datapump.metadata_remap(handle => jobhandle,
name => 'REMAP_TABLESPACE',
old_value => 'DWH_IND_SMALL',
value => 'USERS');

DBMS_DATAPUMP.SET_PARAMETER(jobhandle,'ESTIMATE','STATISTICS');
dbms_output.put_line('Estimate ');

DBMS_DATAPUMP.START_JOB(jobhandle);
dbms_output.put_line('Job Started');

percent_done := 0;
job_state := 'UNDEFINED';

WHILE (job_state != 'COMPLETED') and (job_state != 'STOPPED') LOOP

DBMS_DATAPUMP.get_status(jobhandle,
DBMS_DATAPUMP.ku$_status_job_error +
DBMS_DATAPUMP.ku$_status_job_status +
DBMS_DATAPUMP.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;

IF js.percent_done != percent_done THEN
DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' to_char(js.percent_done));
percent_done := js.percent_done;
END IF;


IF (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) THEN
le := sts.wip;
ELSE
IF (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
le := sts.error;
ELSE
le := null;
END IF;
END IF;

IF le IS NOT NULL THEN
ind := le.FIRST;
WHILE ind IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(le(ind).LogText);
ind := le.NEXT(ind);
END LOOP;
END IF;

END LOOP;


DBMS_OUTPUT.PUT_LINE('Job has completed');
DBMS_OUTPUT.PUT_LINE('Final job state = ' job_state);
DBMS_DATAPUMP.DETACH(jobhandle);

END;

dbms_datapump for Export

Example of dbms_datapump for Export in a procedure that can be automated from dbms_jobsubmit:

CREATE OR REPLACE PROCEDURE "DWH_EXPORT"("P_JOB_NAME" OUT NOCOPY VARCHAR2)
IS

l_dp_handle NUMBER;
l_job_name varchar2(100);
l_file_name varchar2(100);

job_state varchar2(30);
status ku$_Status1010;

begin

l_job_name := 'DWH'to_char(sysdate,'YYYYMMDDHH24MISS');
l_file_name := 'DWH.dmp';

utl_file.fremove('DATAPUMPX',l_file_name);

l_dp_handle := DBMS_DATAPUMP.open( operation => 'EXPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => l_job_name,
version => 'LATEST'
);

DBMS_DATAPUMP.add_file( handle => l_dp_handle,
filename => l_file_name,
directory => 'DATAPUMPX',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

DBMS_DATAPUMP.add_file( handle => l_dp_handle,
filename => 'DWH.log',
directory => 'DATAPUMPX',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

DBMS_DATAPUMP.metadata_filter( handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => 'IN (''WD_USER'')'
);

DBMS_DATAPUMP.metadata_filter( handle => l_dp_handle,
name => 'NAME_EXPR',
value => 'IN (''DWH_CANVASS'',''DWH_CANVASS_DATE'',''DWH_CHANNEL'',
''DWH_CUSTOMER'',''DWH_CYCLE_DATE'', ''DWH_DATE'',
''DWH_DOS_APPOINTMENTS'',''DWH_DOS_SALESREP_TARGET'',
''DWH_DOS_STATE_OF_PLAY'',''DWH_DOS_TEAM'',''DWH_GG_ICANVASS'',
''DWH_GG_WD_CHANNEL'',''DWH_GG_WD_PRODUCTS'',''DWH_GP_WD_CHANNEL'',
''DWH_GP_WD_PRODUCTS'',''DWH_MANDAYS_ACTUALS'',
''DWH_MANDAYS_PLANNED'',''DWH_PA_WD_CHANNEL'',''DWH_PA_WD_PRODUCTS'',
''DWH_PM_WD_CHANNEL'',''DWH_PM_WD_PRODUCTS'',''DWH_PRODUCT'',
''DWH_REP'',''DWH_SALES'',''DWH_SALES_STATUS'',''DWH_TARGET'',
''DWH_UNIT'',''DWH_WD_CANVASS_GROUPS'',''DWH_WD_CHANNEL'',
''DWH_WD_CHANNEL_GRP'',''DWH_WD_DAYOFF'',''DWH_WD_FINANCE_MONTH'',
''DWH_WD_PRODUCT'',''DWH_REFRESH_DATES'',''DWH_MTG_TARGET'',''DWH_DAY_TARGET_ALL'',
''DWH_DAY_TARGET_SALES'',''DWH_DAY_TARGET_CANV'')');


DBMS_DATAPUMP.start_job(l_dp_handle);
p_job_name := l_job_name;

job_state := 'UNDEFINED';

WHILE (job_state != 'COMPLETED') and (job_state != 'STOPPED') LOOP

DBMS_DATAPUMP.GET_STATUS(l_dp_handle,
dbms_datapump.KU$_STATUS_WIP,
-1,
job_state,
status);

END LOOP;

DBMS_DATAPUMP.detach(l_dp_handle);

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Error:' sqlerrm ' for Job:' l_dp_handle);
RAISE_APPLICATION_ERROR (-20100,'Export Failed - 'sqlerrm);

END;