No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 7600
3 B. Vroman 4800
4 P. Wisse 4000
5 J. Schnackenberg 3200
6 J. PĂ©ran 2200
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48534
Total active users1395
Act. users last 24h0
Act. users last hour0
Registered user hits last week82
Registered user hits last month353
Go up

DBPUMP API notg working properly
Next thread: Datapump stuck
Prev thread: Find all combinations for n!

Message Score Author Date
I am trying to import a partition of table over th...... Nishant Kariya Mar 14, 2020, 17:23
I don't understand what is wrong here. Partition n...... Nishant Kariya Mar 14, 2020, 17:25
Hi, well, you should start debugging by removin...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Mar 14, 2020, 17:40
Hi, Problem here is the code is not going in ex...... Nishant Kariya Mar 14, 2020, 17:56
Why didn't you feedback in your previous topic? ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Mar 14, 2020, 18:03
Hi Michel, I am so sorry if I missed to do so. ...... Nishant Kariya Mar 14, 2020, 18:22
Problem in above code is in line where I specify N...... Nishant Kariya Mar 14, 2020, 18:30
Hi, did you check datapump log? Regards, A...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Mar 14, 2020, 18:30
To specify requirement in short it is - I want...... Nishant Kariya Mar 14, 2020, 18:32
I checked once but It had nothing. I can't check r...... Nishant Kariya Mar 14, 2020, 18:35
If I remove partition and colon then code works fi...... Nishant Kariya Mar 14, 2020, 18:39
Hi, if you don't have access to the directory w...... Ales Kavsek Mar 14, 2020, 18:40
Yes I shall ask that from my DBA... Nishant Kariya Mar 15, 2020, 09:23

Follow up by mail Click here


Subject: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 17:23, 189 days ago
Message: I am trying to import a partition of table over the network link but it is not working . Code is getting executed without errors but nothing gets imported.


set scan off
set serveroutput on
set escape off
DECLARE
h1 number;
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
l_ku$status ku$_status1020;
l_job_state varchar2(4000);

percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
begin
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', version => 'COMPATIBLE', remote_link =>'DBPUMP_TEST_LINK' );
DBMS_OUTPUT.PUT_LINE('OPENED');
tryGetStatus := 1;
dbms_datapump.set_parallel(handle => h1, degree => 1);
DBMS_OUTPUT.PUT_LINE('PARALLEL');
dbms_datapump.add_file(handle => h1, filename => 'POC-'||to_char(sysdate,'yyyy-mm-dd-hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP', filetype => 3);
DBMS_OUTPUT.PUT_LINE('ADD_FILE');
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
DBMS_OUTPUT.PUT_LINE('KEEP_MASTER');
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value =>'''FDW_PUMP_TEST''');
DBMS_OUTPUT.PUT_LINE('SCHEM_LIST');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value =>'''FDW_PUMP_TEST.FDW_ASSET_MI_F:SYS_P33041''');
DBMS_OUTPUT.PUT_LINE('TABLE_LIST');
dbms_datapump.set_parallel(h1,4);
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0);
DBMS_OUTPUT.PUT_LINE('INCLUDE_METADATA');
--dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'DIRECT_PATH');
--DBMS_OUTPUT.PUT_LINE('DATA_ACCESS_METHOD');
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
DBMS_OUTPUT.PUT_LINE('ESTIMATE');
DBMS_DATAPUMP.SET_PARAMETER(handle => h1,name => 'TABLE_EXISTS_ACTION',value => 'TRUNCATE');
DBMS_OUTPUT.PUT_LINE('TABLE_EXISTS_ACTION');
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
DBMS_OUTPUT.PUT_LINE('JOB STARTED');
--dbms_datapump.detach(handle => h1);
errorvarchar := 'NO_ERROR';
DBMS_OUTPUT.PUT_LINE('NO_ERROR');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS'));

percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
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 the percentage done changed, display the new value.

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;
end loop;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
DBMS_OUTPUT.PUT_LINE('LANDED IN EXCEPTION');
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
END;
/
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API not working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 17:25, 189 days ago
Message: I don't understand what is wrong here. Partition name is taken from the environment from where I am trying to import
(source).

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Ales Kavsek, Slovenia
Date: Mar 14, 2020, 17:40, 189 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

well, you should start debugging by removing silly part of the code:


EXCEPTION
WHEN OTHERS THEN
NULL;

You should consider this part of the code as a bug anyway. You should never silence the error message stack. Leave the exception handler section as:


EXCEPTION
WHEN OTHERS THEN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
DBMS_OUTPUT.PUT_LINE('LANDED IN EXCEPTION');
END IF;
RAISE;
...
...


...so that you'll receive full error message stack.

And please, use proper tags when posting code, as I did, otherwise code is unreadable!

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 17:56, 189 days ago
Message: Hi,

Problem here is the code is not going in exception section.
I get all the message tags printed =, showing that each line was executed. See the output below:

But the table is not getting imported

Output:

OPENED
PARALLEL
ADD_FILE
KEEP_MASTER
SCHEM_LIST
TABLE_LIST
INCLUDE_METADATA
ESTIMATE
TABLE_EXISTS_ACTION
JOB STARTED
NO_ERROR
14-MAR-2020 04:54:28
*** Job percent done = 100
14-MAR-2020 04:54:29
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Michel Cadot, France
Date: Mar 14, 2020, 18:03, 189 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
Why didn't you feedback in your previous topic?
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=94586
Do you think my answer was useless, pointless or what else?
I spent a couple hours to make it and help you.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 18:22, 189 days ago
Message: Hi Michel,

I am so sorry if I missed to do so. Actually I started taking this import automation task and then spent a month in production issue.

I will provide feedback now. None of your answers are useless. They are always full of knowledge. Apologies again!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 18:30, 189 days ago
Message: Problem in above code is in line where I specify NAME_LIST

Partition SYS_P33041 is there in source database.I double checked that.

Now I am thinking is it something to do with syntax that code is not understanding that I mean to say - pull partition SYS_P33041 from the table FDW_ASSET_MI_F

Should it be like one of the formats as given below:
'''FDW_ASSET_MI_F : SYS_P33041@DBPUMP_TEST_LINK''' (with dblink name)
'''FDW_PUMP_TEST.FDW_ASSET_MI_F : SYS_P33041@DBPUMP_TEST_LINK''' (with schema name and dblink name)
'''FDW_ASSET_MI_F : SYS_P33041'''
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Ales Kavsek, Slovenia
Date: Mar 14, 2020, 18:30, 189 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

did you check datapump log?

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 18:32, 189 days ago
Message: To specify requirement in short it is -

I want to pull some partitions of some tables from higher tier to lower tier over network link.


and to achieve this requirement and I am doing all this.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 18:35, 189 days ago
Message: I checked once but It had nothing. I can't check right now as directory is a controlled one and not accessible by me.

I still don't know how to access that via this API
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 14, 2020, 18:39, 189 days ago
Message: If I remove partition and colon then code works fine and pulls complete table but that is not the requirement
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Ales Kavsek, Slovenia
Date: Mar 14, 2020, 18:40, 189 days ago
Message: Hi,

if you don't have access to the directory where the datapump log file is, you can't debug/write this PL/SQL procedure. Period. Ask DBA to give you access to this directory or start nagging for assistance each time.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: DBPUMP API notg working properly
Author: Nishant Kariya, India
Date: Mar 15, 2020, 09:23, 188 days ago
Message: Yes I shall ask that from my DBA
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here