How to create a lst file in SQL?

Question

Hi,

I want to create a lst file from a sql script

Here is my code:

BEGIN
DECLARE
loc_bia_foracid GAM.foracid%TYPE;
loc_fba_foracid GAM.foracid%TYPE;
out_spool VARCHAR2(32000);
BEGIN
loc_bia_foracid:= '&loc_bia_foracid';
loc_fba_foracid:= '&loc_fba_foracid';

out_spool := out_spool || '|' || loc_bia_foracid;
out_spool := out_spool || '|' || loc_fba_foracid;


END;

If I don't give the no of characters for out_spool it gives me an error. And I want my lst file to be more than 32k characters. Also I need to know how do I define it in a stored procedure.

Answer 1

Here is how the above would look as a stored procedure:

CREATE OR REPLACE FUNCTION make_lst(
  pis_bia_foracid IN GAM.foracid%TYPE,
  pis_fba_foracid IN GAM.foracid%TYPE)
RETURN VARCHAR2 AS
BEGIN
  RETURN '|' || pis_bia_foracid || '|' || pis_fba_foracid;
END make_lst;

(I haven't compiled it yet so it may not work first time)

My suggestion for strings greater than 32k is to use a CLOB (character large object). A CLOB (depending on your Oracle version) holds up to 4GB of text. I use Oracle 9i and in 9i you use the DBMS_LOB package to initialise a CLOB, open it for writing, write to it, close it.

Some comments on style:

• Note the use of the method name on the END tag. It helps keep track of nested BEGIN-END blocks.
• Note the naming conventionon the variables: pis=parameter, in, string while ls=local, string
• I recommend adding logging and unit testing.
• I recommend adding the method to a package as I susepct you'll need a few related methods to achieve what you're ultimately looking for.

Answer 2

See the AskTom article here: http://asktom.oracle.com/tkyte/flat/index.html