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

asked 23 Jun, 09:52

Sarkis's gravatar image

Sarkis
2002625
accept rate: 0%

edited 24 Jun, 06:15

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668


permanent link

answered 23 Jun, 10:39

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

edited 23 Jun, 10:41

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

(23 Jun, 11:43) Sarkis

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

(23 Jun, 19:12) Sarkis

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

(26 Jun, 03:48) Sarkis
Replies hidden
1

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.

(26 Jun, 04:24) Reimer Pods
1

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

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.

(26 Jun, 05:50) John Smirnios

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\x0a' -- 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\x0a--' STRIP OFF;

SELECT * 
  FROM raw_dbtran_text 
 ORDER BY operation_number;

-- Note: UNLOAD writes an extra '\x0d\x0a--' 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\x0a--';
permanent link

answered 26 Jun, 10:58

Breck%20Carter's gravatar image

Breck Carter
26.6k433604879
accept rate: 21%

edited 26 Jun, 11:00

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×39

question asked: 23 Jun, 09:52

question was seen: 125 times

last updated: 26 Jun, 11:00