in reply to SQLplus code

Try ...
my $cmd="sqlplus $conect-string"; open SQLPLUS, "|$cmd" or die "couldn't open $cmd:$!\n"; print SQLPLUS <<end; set serveroutput on size 100000; set pagesize 1000 set linesize 140 set heading off set feedback off set trims on DECLARE num number := 0; state0 varchar(8) := '^%^%^%'; state1 varchar(8) := '^%^%^%'; mod_meas_name varchar(12); CURSOR c1 IS SELECT distinct icd_meas.meas_name, icd_meas.meas_title FROM icd_meas, icd_cal_discrete WHERE icd_meas.meas_name = icd_cal_discrete.meas_name and data_type = 'DIS' and size_bits_no = 1; BEGIN dbms_output.put_line('BiLevel Discrete Commands'); FOR record in c1 LOOP num := num + 1; BEGIN SELECT state into state0 FROM icd_cal_discrete WHERE record.meas_name = meas_name and high_count_no = 0; SELECT state into state1 FROM icd_cal_discrete WHERE record.meas_name = meas_name and high_count_no = 1; EXCEPTION WHEN NO_DATA_FOUND THEN state0 := ''; state1 := ''; WHEN TOO_MANY_ROWS THEN state0 := '--'; state1 := '--'; END; dbms_output.put_line('!!DESCRIPTION: ' || record.meas_title); dbms_output.put_line('ID;B;' || rpad(replace(record.meas_name,'_'),1 +2) || ';' || rpad(state1,8) || ';' || rpad(state0,8)); END LOOP; -- dbms_output.put_line('num = ' || num); END; end close SQLPLUS;
The above is untested. But I hope it helps.

Replies are listed 'Best First'.
Re (2): SQLplus code
by VSarkiss (Monsignor) on Jun 17, 2003 at 01:33 UTC

    That hardly qualifies as a translation. You're just feeding the source verbatim into SQL*Plus. I think what the OP was after is a Perl program that would achieve the same result without using the sqlplus executable.

    The question has been asked and answered before, and you can probably find other examples with a little work.

    Basically, you can translate all of the query logic into Perl using DBI and DBD::Oracle in a straightforward way, but the formatting capabilities will require some thought. But just copying the whole script to a Perl scalar before feeding it to SQL*Plus is unlikely to benefit anyone. You may as well have suggested the OP use "cat" or "type".

      I think LameNerd has just invented Inline::PL/SQL!