Hi Monks, I have problem in calling Oracle stored procedure here. the script is

 
#!/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::errst
+r";
eval {
    my $func = $dbh->prepare(q{
        BEGIN
   
    drop_user_table(parameter1_in => :parameter1);

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

    $dbh->commit;
};

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

$dbh->disconnect;
[download]

and the procedure is

create or replace procedure drop_user_table (in_table IN varchar2) aut
+hid current_user
IS
-- Declare the variables for counting tables and setting the execute s
+tatement.
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;
[download]

and while running the script I am getting this error

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

                                <*>drop_user_table(parameter1_in => :p
+arameter1);

        END;
    ') [for Statement "
        BEGIN

                                drop_user_table(parameter1_in => :para
+meter1);

        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_TA
+BLE'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicato
+r at char 23 in '
        BEGIN

                                <*>drop_user_table(parameter1_in => :p
+arameter1);

        END;
    ')
[download]

Can anybody help me here?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.