Home » RDBMS Server » Server Utilities » how to parametrize values in pl/sql block (Oracle 19c redhat 7)
how to parametrize values in pl/sql block [message #687936] Thu, 27 July 2023 22:06 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #687942 is a reply to message #687939] Fri, 28 July 2023 07:29 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
you are awesome Man!!! thank you so much. my problem has been solved
Re: how to parametrize values in pl/sql block [message #687943 is a reply to message #687942] Fri, 28 July 2023 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... but prone to sql injection.

Re: how to parametrize values in pl/sql block [message #687944 is a reply to message #687943] Fri, 28 July 2023 11:14 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member
Michel Cadot wrote on Fri, 28 July 2023 07:32

... but prone to sql injection.

why?
Re: how to parametrize values in pl/sql block [message #687945 is a reply to message #687944] Fri, 28 July 2023 11:34 Go to previous messageGo to next message
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 #687946 is a reply to message #687939] Fri, 28 July 2023 11:40 Go to previous messageGo to next message
avtaritet
Messages: 18
Registered: April 2020
Junior Member

Hi Barbara any idea how to do multiple tables here?

Thanks

[Updated on: Sat, 29 July 2023 10:18] by Moderator

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

Re: how to parametrize values in pl/sql block [message #687948 is a reply to message #687947] Fri, 28 July 2023 13:13 Go to previous message
avtaritet
Messages: 18
Registered: April 2020
Junior Member

Thank you so much Barbara I really appreciate you!

[Updated on: Sat, 29 July 2023 10:19] by Moderator

Report message to a moderator

Previous Topic: How to exclude Multiple tables by using EXPDP
Next Topic: Services restart during datapump expdp and impdp
Goto Forum:
  


Current Time: Tue Feb 27 16:36:42 CST 2024