Parameters can't be passed in when using perl DBI to create view

Question

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.

Answer 1

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.