| how to parametrize values in pl/sql block [message #687936] |
Thu, 27 July 2023 22:06  |
 |
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
Hi, i have one procedure inside the procedure i have 2 lines which is i need to parametrize. here OUTLN is schema_name and DBA_USERS_HIST is the table_name.
dbms_datapump.metadata_filter(l_dp_handle,'SCHEMA_EXPR','= OUTLN');
dbms_datapump.metadata_filter(l_dp_handle,'NAME_EXPR','= DBA_USERS_HIST');
my question is if i put schema_name and table_name hard coded it works fine. but when i am trying to parametrize i am getting
ORA-39071: Value for SCHEMA_EXPR is badly formed.
could you please help me on this?
|
|
|
|
| Re: how to parametrize values in pl/sql block [message #687938 is a reply to message #687936] |
Fri, 28 July 2023 00:25  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Posting a code that works to debug a code that does not work but you don't post help in no way to debug it.
From your previous topic:
Michel Cadot wrote on Tue, 21 March 2023 07:37
From your previous topic:
Michel Cadot wrote on Tue, 28 February 2023 07:22
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
...
Michel Cadot wrote on Tue, 28 February 2023 17:52
Please read How to use [code] tags and make your code easier to read.
...
Michel Cadot wrote on Wed, 01 March 2023 07:09
1/ FORMAT your post
...
|
|
|
|
| Re: how to parametrize values in pl/sql block [message #687939 is a reply to message #687938] |
Fri, 28 July 2023 04:49 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example for you.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle number;
6 begin
7
8 l_dp_handle := dbms_datapump.open(
9 operation => 'EXPORT',
10 job_mode => 'TABLE',
11 remote_link => NULL,
12 job_name => 'TESTUSER1_EMP_EXPORT',
13 version => 'LATEST');
14
15 dbms_datapump.add_file(
16 handle => l_dp_handle,
17 filename => 'TESTUSER1_EMP.dmp',
18 directory => 'TEST_DIR');
19
20 dbms_datapump.add_file(
21 handle => l_dp_handle,
22 filename => 'expdpTESTUSER1_EMP.log',
23 directory => 'TEST_DIR',
24 filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
25
26 dbms_datapump.metadata_filter(
27 handle => l_dp_handle,
28 name => 'SCHEMA_EXPR',
29 value => '= ''' || p_schema || '''');
30
31 dbms_datapump.metadata_filter(
32 handle => l_dp_handle,
33 name => 'NAME_EXPR',
34 value => '= ''' || p_table || '''');
35
36 dbms_datapump.start_job(l_dp_handle);
37
38 dbms_datapump.detach(l_dp_handle);
39
40 end test_proc;
41 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> exec test_proc ('TESTUSER1', 'EMP')
PL/SQL procedure successfully completed.
|
|
|
|
|
|
|
|
|
|
| Re: how to parametrize values in pl/sql block [message #687945 is a reply to message #687944] |
Fri, 28 July 2023 11:34 |
|
avtaritet
Messages: 18
Registered: April 2020
|
Junior Member |
|
|
quick question if i want to put multiple tables here how should be below statement.
dbms_datapump.metadata_filter(
handle => l_dp_handle,
name => 'NAME_EXPR',
value => 'in || in_tables || ');
-- below one works fine
dbms_datapump.metadata_filter(handle => l_dp_handle, name => 'NAME_EXPR', value => 'IN (TEST1,TABLE2)', object_type => 'TABLE');
[Updated on: Fri, 28 July 2023 11:37] Report message to a moderator |
|
|
|
|
|
| Re: how to parametrize values in pl/sql block [message #687947 is a reply to message #687946] |
Fri, 28 July 2023 12:28 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the changes to line 34 below. It should work with multiple tables or just one. It will just use an IN clause instead of =.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle number;
6 begin
7
8 l_dp_handle := dbms_datapump.open(
9 operation => 'EXPORT',
10 job_mode => 'TABLE',
11 remote_link => NULL,
12 job_name => 'TESTUSER1_EXPORT',
13 version => 'LATEST');
14
15 dbms_datapump.add_file(
16 handle => l_dp_handle,
17 filename => 'TESTUSER1.dmp',
18 directory => 'TEST_DIR');
19
20 dbms_datapump.add_file(
21 handle => l_dp_handle,
22 filename => 'expdpTESTUSER1.log',
23 directory => 'TEST_DIR',
24 filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
25
26 dbms_datapump.metadata_filter(
27 handle => l_dp_handle,
28 name => 'SCHEMA_EXPR',
29 value => '= ''' || p_schema || '''');
30
31 dbms_datapump.metadata_filter(
32 handle => l_dp_handle,
33 name => 'NAME_EXPR',
34 value => 'in (''' || replace (p_table, ',', ''',''') || ''')');
35
36 dbms_datapump.start_job(l_dp_handle);
37
38 dbms_datapump.detach(l_dp_handle);
39
40 end test_proc;
41 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> exec test_proc ('TESTUSER1', 'EMP,DEPT')
PL/SQL procedure successfully completed.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
