Hi
Just wondering if I should be able to prepare a PL/SQL statement that both
creates a temporary table and accesses that temp table in the one prepare
execute and bind statements, or am I going to need to do more than one prepare.
Due to access I am unable to create functions or procedures on the database
but temp tables are ok. eg below I want to end up being able to bind to :test
to fetch my results, but I want to be able to prepare and execute the below in
one go. .. Thanks in advance for any help... This works in sqlplus in the
one statement but DBD Oracle is seeing :test as undef..
my $sql4 = qq(
CREATE GLOBAL TEMPORARY TABLE "ip_table"
(
nelocn VARCHAR2(10),
netype VARCHAR2(10),
neindex VARCHAR2(10)
)ON COMMIT PRESERVE ROWS;
DECLARE
ip VARCHAR2(50):= :ipaddress;
BEGIN
INSERT INTO ip_table (nelocn, netype, neindex)
select equp_locn_ttname ,equp_equt_abbreviation, equp_index
from equipment
where equp_ipaddress = ip;
OPEN :test FOR
SELECT nelocn
FROM ip_table;
END;
);
$sth = $dbh->prepare($sql4);
my $sth_cursor;
$sth->bind_param(":ipaddress",$ip);
$sth->bind_param_inout(':test', \$sth_cursor, 0, {ora_type => ORA_RSET });
$sth->execute();
The error is as follows
DBD::Oracle::st bind_param failed: ORA-01036: illegal variable name/number (DBD
ERROR: OCIBindByName) [for Statement "
CREATE GLOBAL TEMPORARY TABLE "ip_table"
(
nelocn VARCHAR2(10),
netype VARCHAR2(10),
neindex VARCHAR2(10)
)ON COMMIT PRESERVE ROWS;
DECLARE
ip VARCHAR2(50):= :ipaddress;
BEGIN
INSERT INTO ip_table (nelocn, netype, neindex)
select equp_locn_ttname ,equp_equt_abbreviation, equp_index
from equipment
where equp_ipaddress = ip;
OPEN :test FOR
SELECT nelocn
FROM ip_table;
END;
" with ParamValues: :ipaddress='10.255.4.220', :test=undef] at sqlplustest.pl
line 177.
