Oracle - Trying to execute a procedure using perl

Question

I am trying to execute a procedure using perl as under:

my @tabs = qw!ACTOR ADDRESS CATEGORY CITY COUNTRY CUSTOMER FILM INVENTORY LANGUAGE STAFF STORE!;
  for my $ts (@tabs){
     chomp $ts;
        my $csr = $ora->prepare(q{
           BEGIN
             update_cascade.on_table(:ts)
           END;
        });
$csr->bind_param(":ts", $ts);
$csr->execute;
}

Which results in following errors:

DBD::Oracle::st execute failed: ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol "END" when expecting one of the following:


   := . ( % ;
The symbol ";" was substituted for "END" to continue. (DBD ERROR: error possibly near <*> indicator at char 71 in '
           BEGIN
             update_cascade.on_table(:ts)
           <*>END;
        ') [for Statement "
           BEGIN
             update_cascade.on_table(:ts)
           END;
        " with ParamValues: :ts='ACTOR'] at sakila_update_cascade.pl line 18.
DBD::Oracle::st execute failed: ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol "END" when expecting one of the following:


   := . ( % ;
The symbol ";" was substituted for "END" to continue. (DBD ERROR: error possibly near <*> indicator at char 71 in '
           BEGIN
             update_cascade.on_table(:ts)
           <*>END;
        ') [for Statement "
           BEGIN
             update_cascade.on_table(:ts)
           END;
        " with ParamValues: :ts='ACTOR'] at sakila_update_cascade.pl line 18.

I can execute it within SQLPlus as under

exec update_cascade.on_table(:'ACTOR');

I tried with:

update_cascade.on_table('||:ts||')

It does not get executed and get this perl error:

Can't bind unknown placeholder ':ts' (':ts') at sakila_mig.pl line 659.

The DBD::Oracle package sites an example as under:

my $test_num = 5;
  my $is_odd;

  $csr = $db->prepare(q{
  BEGIN
  PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
  END;
  });
  $csr->bind_param(":test_num", $test_num);

  $csr->bind_param_inout(":is_odd", \$is_odd, 1);
  $csr->execute;

user3658506 · Accepted Answer · 2014-08-28 11:55:57Z

2

You're simply missing a semicolon (;)

Try :

      BEGIN
        update_cascade.on_table(:ts);
      END;

The code looks ok !

answered Aug 28, 2014 at 11:55

user3658506

6334 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle - Trying to execute a procedure using perl

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related