Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: dynamic sql question ( new line character )

    can I enter "new line character" in dynamic sql.
    Basically I want to create 5 statements in 1 query which should have similar output like below.

    create table TAB1 as select * from cust;
    update TAB1 set name = 'NEW';
    insert into cust select * from TAB1 ;
    drop table TAB1;
    commit;
    create table TAB1 as select * from emp;
    update TAB1 set name = 'NEW';
    insert into emp select * from TAB1 ;
    drop table TAB1;
    commit


    I am using following dynamic sql, that doesn't work because all the statements comes on one line. If the output comes on saperate line , it will work.

    select 'create table TAB1 as select * from ' || tname|| ';'
    || 'update TAB1 set name = ''NEW'' ;'
    || 'insert into ' || tname || ' select * from TAB1 ; '
    || 'drop table TAB1 ; commit ;'
    from tab;


    Is it possible or I should use procedure instead

    Thanks in advance
    ydj

  2. #2
    Join Date
    Oct 2003
    Posts
    43
    To split the code onto seperate lines try using ||chr(10)|| instead of || between lines of code. This specifies a carriage return, I have never tried it with dynamic sql, but it works it other cases.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •