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.