unload without quotes

[Baron]

I am trying to generate sql script to be read later on another db.

begin
declare v_dncode varchar(20);

declare v_select_stmt varchar(200);

declare v_unload_stmt varchar(200);

select distinct docnumcode into v_dncode from document ;

set v_select_stmt = 'select * from document where docnumcode = ''' + v_dncode + ''' and docnr > 100;';

set v_unload_stmt = 'unload select ''' + v_select_stmt + ''' from dummy to ''c:\\temp\\sql_statement.sql''';

execute immediate string (v_unload_stmt);

end

The result which I get in sql_statement.sql is:

'select * from document where docnumcode = ''111'' and docnr > 100;'

Question: how can I get the result as:

select * from document where docnumcode = '111' and docnr > 100;

i.e. removing the first and last quotes and also remove one of the double quotes around 111. whereas 111 is the result of: select distinct docnumcode into v_dncode from document ; and it is of type varchar(10);

Thanks in advance

[johnsmirnios]

Add the QUOTES OFF clause: http://dcx.sap.com/index.html#sqla170/en/html/817fd0fd6ce21014a58ff727fbb7b4e2.html*loio817fd0fd6ce2...

Also, for what you are doing, you could try xp_write_file. http://dcx.sap.com/index.html#sqla170/en/html/8180f5b16ce21014934ea5a5d5c8efba.html*loio8180f5b16ce2...

[Baron]

Thanks for the reply, the second comments looks very useful, and hope that it will work for version 10. Will test it and write back

[Baron]

thank you very much. xp_write_file is exactly what I needed.

[Baron]

could you please tell me if there is possibility to append text using xp_write_file?

[reimer_pods]

xp_write_file isn't designed for that. But you could use xp_read_file to read the contents of that file into a variable, add the contents you want to append to it and write it back with xp_write_file.

[johnsmirnios]

As Reimer says, xp_write_file doesn't support appending, see http://dcx.sap.com/index.html#sqla170/en/html/8180f5b16ce21014934ea5a5d5c8efba.html*loio8180f5b16ce2...

UNLOAD might still work for you. You could use QUOTES OFF ESCAPES OFF APPEND ON. You might also need ROW DELIMITED BY '' since I don't recall if we add a trailing delimiter. If the data is binary, you should use HEXADECIMAL OFF too.

[Breck_Carter]

In addition to John's reply and sundry comments, here is one of my favorite templates, named "load_unload_raw_text.sql"...

---------------------------------------------------------------------
-- LOAD UNLOAD cr-lf delimited text.

BEGIN
   DROP TABLE raw_text;
   EXCEPTION WHEN OTHERS THEN
END;

/*
CREATE LOCAL TEMPORARY TABLE raw_text (
   line_number          BIGINT NOT NULL 
                           DEFAULT AUTOINCREMENT
                           PRIMARY KEY CLUSTERED,
   line_text            LONG VARCHAR NOT NULL DEFAULT '' )
   NOT TRANSACTIONAL;
*/

CREATE TABLE raw_text (
   line_number BIGINT NOT NULL 
                  DEFAULT AUTOINCREMENT
                  PRIMARY KEY CLUSTERED,
   line_text   LONG VARCHAR NOT NULL DEFAULT '' );

LOAD TABLE raw_text ( line_text )
   FROM 'c:/temp/xxx.txt'
   DEFAULTS ON 
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF 
   ROW DELIMITED BY '\\x0d\
' -- preserve empty lines
   STRIP OFF;

SELECT *
  FROM raw_text
 ORDER BY raw_text.line_number;

UNLOAD SELECT line_text
         FROM raw_text
        ORDER BY line_number
   TO 'c:/temp/yyy.txt'
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF;

-- similar use of OUTPUT...
OUTPUT TO 'temp_generated_alter_autoincrement.txt'
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTE '';



---------------------------------------------------------------------
-- LOAD UNLOAD operations captured by DBTRAN

BEGIN
   DROP TABLE raw_dbtran_text;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE raw_dbtran_text (
   operation_number          BIGINT NOT NULL DEFAULT AUTOINCREMENT,
   operation_text            LONG VARCHAR NOT NULL DEFAULT '',
   PRIMARY KEY ( operation_number ) );

LOAD TABLE raw_dbtran_text ( operation_text )
   FROM 'C:/projects/ASA9_templates/run/dbtran/dbtran_ddd.sql'
   DEFAULTS ON DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF ROW DELIMITED BY '\\x0d\
--' STRIP OFF;

SELECT * 
  FROM raw_dbtran_text 
 ORDER BY operation_number;

-- Note: UNLOAD writes an extra '\\x0d\
--' to the end of the output file.
--       Otherwise the output file is identical to the input file. 

UNLOAD SELECT operation_text
         FROM raw_dbtran_text
        ORDER BY operation_number
   TO 'C:/projects/ASA9_templates/run/dbtran/dbtran_ddd2.sql'
   DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF ROW DELIMITED BY '\\x0d\
--';