#!/usr/bin/perl
use strict;
use DBI;


my $dbh = DBI->connect(
    "dbi:Oracle:host= ;sid= ;port= ","user","password",{RaiseError => 1,AutoCommit => 0}) || die "Database connection not made: $DBI::errstr";
eval {
    my $func = $dbh->prepare(q{
        BEGIN
   
    drop_user_table(parameter1_in => :parameter1);

        END;
    });
  
 $func->bind_param(":parameter1", 'mumtest') ; #positional placeholders are handy!
    $func->execute;
 $func->finish;

    $dbh->commit;
};

if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    $dbh->rollback;
}

$dbh->disconnect;


##</code><code>##

create or replace procedure drop_user_table (in_table IN varchar2) authid current_user
IS
-- Declare the variables for counting tables and setting the execute statement.
tbl_exists integer;
my_stmt varchar2(100);
BEGIN
-- Make a count to see if table exists
select count(*)
into tbl_exists
from all_tables
where table_name = in_table
and owner = user;
-- If table exeists, then set the statement to drop it and execute the statement.
if tbl_exists = 1 then
my_stmt := 'drop table ' || in_table;
EXECUTE IMMEDIATE my_stmt;
end if;
END;


##</code><code>##

DBD::Oracle::st execute failed: ORA-06550: line 4, column 5:
PLS-00306: wrong number or types of arguments in call to 'DROP_USER_TABLE'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 23 in '
        BEGIN

                                <*>drop_user_table(parameter1_in => :parameter1);

        END;
    ') [for Statement "
        BEGIN

                                drop_user_table(parameter1_in => :parameter1);

        END;
    " with ParamValues: :parameter1='mumtest'] at ora.pl line 20.
Execution of stored procedure failed: ORA-06550: line 4, column 5:
PLS-00306: wrong number or types of arguments in call to 'DROP_USER_TABLE'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 23 in '
        BEGIN

                                <*>drop_user_table(parameter1_in => :parameter1);

        END;
    ')