PLSQL with Perl

[Kalin Evtimov]

Hi!
I have a small problem, maybe syntax:

I create an sql-file using spool:


$file = "file.sql";
$HOT=qx{$ENV{'ORACLE_HOME'}/bin/sqlplus -SL /nolog < whenever sqlerror exit failure
set termout off heading off feedback off trimspool on
set echo off linesize 1000 pagesize 300
connect / as sysdba;
whenever sqlerror continue
spool $file
select 'alter tablespace '||a.tablespace_name||' end backup;'
----and so on---------

spool off
END};

Now I want to call the created sql-file and use:

start $file

Here my programm freezes and does nothing more.
If I test the sql-file separately, it works perfectly. But calling within the perl-script doesn't work properly.

Help appreciated. Many thanks.

[Peter Godron · Accepted solution]

Kalin,
as a test, can you write your file.sql as you do at the moment, but then start a new sqlplus session and do your start.
So:
1. Create your file.sql
2. exit sqlplus
3. Start new sqlplus, which starts file.sql

This used to be our workaround whwn packages were not being executed, which I assume also stands true for PLSQL.

[Kalin Evtimov]

Thanks.

The problem was in the wrong settings of the database..I hope it wil work.