CODEWITHSUNDEEP
oracle-databaseperlplsqldbi
TonyaLepski
TonyaLepski

Reputation: 199

DBD::Oracle - A PL/SQL block is not working

I am trying to execute a PL/SQL code using DBD::Oracle. It gets executed without any errors, but the tables don't get dropped.

Here is the code:

my @tabs = qw!actor address category city country customer film film_features
film_actor film_category film_text inventory language payment rental staff 
store!; 

for my $ts (@tabs){

    my $csr = $ora->prepare(q{
         DECLARE
              var_count INT;
         BEGIN
             SELECT COUNT(*) INTO var_count
             FROM sys.all_tables WHERE OWNER = 'SAKILA' AND table_name = :ts ;
                 if var_count > 0 then
                     EXECUTE IMMEDIATE 'DROP TABLE :ts CASCADE CONSTRAINTS' ;
                 end if;
          END;
    });
    $csr->bind_param(":ts", $ts);
    $csr->execute;

}

Upvotes: 2

Views: 326

Answers (2)

user3658506
user3658506

Reputation: 633

No.1 - Change the table list into upper case

No.2 - Change :

FROM 

EXECUTE IMMEDIATE 'DROP TABLE :ts CASCADE CONSTRAINTS' ;

TO:

EXECUTE IMMEDIATE 'DROP TABLE '||:ts||' CASCADE CONSTRAINTS' ;

Here is the full code :

my @tabs = qw!ACTOR ADDRESS CATEGORY CITY COUNTRY CUSTOMER FILM FILM_FEATURES FILM_ACTOR FILM_CATEGORY FILM_TEXT INVENTORY LANGUAGE PAYMENT RENTAL STAFF STORE!;
  for my $ts (@tabs){
     chomp $ts;
     my $csr = $ora->prepare(q{ 
        DECLARE
          var_count INT;
        BEGIN
          SELECT COUNT(*) INTO var_count
          FROM sys.all_tables WHERE OWNER = 'SAKILA' AND table_name = :ts ;
            if var_count > 0 then
              EXECUTE IMMEDIATE 'DROP TABLE '||:ts||' CASCADE CONSTRAINTS' ;
            end if;
        END;
     });
$csr->bind_param(":ts", $ts);
$csr->execute;
}

Upvotes: 3

yamny
yamny

Reputation: 660

I would replace

EXECUTE IMMEDIATE 'DROP TABLE :ts CASCADE CONSTRAINTS';

with 

EXECUTE IMMEDIATE 'DROP TABLE '||:ts||' CASCADE CONSTRAINTS';

because dynamic sql will not recognize :ts as variable when you put it directly to dynamic query.

Upvotes: 2

Related Questions