How do I create an lst file in SQL?
I want to create lst file from 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 no characters for out_spool
it gives me an error. And I want my lst file to have over 32k characters. Also I need to know how to define it in a stored procedure.
It would be something like this I suppose
declare
loc_bia_foracid varchar2(32767);
loc_fba_foracid varchar2(32767);
out_spool clob;
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 you use it in an anonymous block, you can get an empty out_spool from behind and subscribe so you will need to concatenate them before loc_ variables like "&" || loc_bia ....
source to share
This is how it would look like 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 won't work the first time)
My suggestion for lines larger than 32k is to use CLOB (Generic Large Object). The CLOB (depending on your Oracle version) can hold up to 4GB of text. I am using Oracle 9i and in 9i you are using DBMS_LOB package to initialize CLOB, open it for writing, write to it, close it.
Some style comments:
- Note the use of the method name in the END tag. It helps keep track of nested BEGIN-END blocks.
- Pay attention to the rule of naming variables: pis = parameter, in, string while ls = local, string
- I recommend adding logging and unit testing.
- I recommend adding a method to the package as I suspect that you will need several related methods to achieve what you are ultimately looking for.
source to share