2

I want to use Perl DBI to create views based on a database automatically. The related code is like the following, 

my $dbh       = DBI->connect( "dbi:Oracle:$database", $user_passwd );
my $Directive = q{ CREATE OR REPLACE VIEW SOME_VIEW AS SELECT * FROM ID_TABLE WHERE ID=?};
my $ID = 12345;

my $sth = $dbh->prepare($Directive);
my $rv  = $sth->execute($ID);

Then I found the $rv is always undef after I run the code. Anything wrong I've made in the code? When I put the parameter directly into $Directive, everything is good.

BTW, when I use some other $Directive, like "SELECT * FROM ID_TABLE WHERE ID=?", the parameter $ID could be passed in without any problem.

user1579740
  • 21
  • 2
  • 1
    Try connecting with `RaiseError => 1`. Some drivers do not support parameters in views (not sure about Oracle, though). – choroba Aug 06 '12 at 16:17

1 Answers1

2

SQL does not allow views to be created with a placeholder in the condition as in your example. All else apart, there isn't a notation that allows you to specify the value for the placeholder when referencing the view. DDL statements in general do not take input parameters.

You will have to do things differently, probably foregoing the view.

This is not a limitation of Perl or DBI per se, nor even really the specific DBMS you're using; it is an issue with the design of SQL as a whole.

Much the simplest way to deal with this specific case is:

my $dbh = DBI->connect( "dbi:Oracle:$database", $user_passwd );
my $ID = 12345;
my $sql = qq{ CREATE OR REPLACE VIEW SOME_VIEW AS SELECT * FROM ID_TABLE WHERE ID = $ID };

my $sth = $dbh->prepare($sql);
my $rv  = $sth->execute();

Simply embed the value of the parameter in the SQL statement that you prepare and execute.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Thanks Jonathan for your immediate response. Seems I have to find a way to pass without using automatically creating those views. – user1579740 Aug 06 '12 at 16:31
  • I've retried in a way like following .. my $dbh = DBI->connect( "dbi:Oracle:$database", $user_passwd ); my $Directive = q{ CREATE OR REPLACE VIEW SOME_VIEW AS SELECT * FROM ID_TABLE WHERE ID=?}; my $ID = '12345'; my $sth = $dbh->prepare($Directive.$ID); my $rv = $sth->execute(); ... This part of code works well. From here I think the function $stch->execute() could not put the parameters to where they are supposed to be. Wired. – user1579740 Aug 06 '12 at 16:42
  • Add RaiseError to your connect and you will get something like "ORA-01027: bind variables not allowed for data definition operations" when you call execute. Reread Jonathan's post - you cannot do what you are trying to do - Oracle does not allow you to. – bohica Aug 07 '12 at 08:14