Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Unanswered: Dynamic SQL getting truncated

    I have a procedure using dynamic sql to compare the contents of two tables. The code is:
    l_sql := 'create table ' || result_table_name || ' as'||
                   ' SELECT ' || l_field_list ||', COUNT(src32) src32_count, COUNT(src64) src64_count'||
                   ' FROM ('||
                           'SELECT ' || l_field_list ||',1 AS src32, TO_NUMBER(NULL) AS src64'||
                          ' FROM   ' || l_32_tab ||
                          ' UNION ALL'||
                          ' SELECT ' || l_field_list ||',TO_NUMBER(NULL) AS src32, 1 AS src64'||
                          ' FROM   ' || l_64_tab || case when db_link is not null then '@' || db_link
                                                        else ' ' end ||
                           ') GROUP BY ' || l_field_list ||
                   ' HAVING COUNT(src32) <> COUNT(src64)';
    dbms_output.put_line('l_sql length='||length(l_sql));
    execute immediate l_sql;
    I am getting an "ORA-00933: SQL command not properly ended" because for some reason the SQL statement if being truncated at 1255 characters even though l_sql is defined as varchar2(32767). Anyone ever encounter a problem like this before?

    Edit to add. This is Oracle 10g ( Enterprise edition.

    Thanks in advance
    Ken Denny
    Last edited by kendenny; 05-11-09 at 10:41.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >I have a procedure using dynamic sql to compare the contents of two tables
    In my opinion, this is a bad solution to a typical NIH "solution".

    Since in theory, this should work, then logical conclusion is that the code is faulty.
    Keep in mind that the SQL parser throws an error when a blank line occurs within a single SQL statement.

    Good Luck in finding the Golden Fleece.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Thanks. I discovered the problem. First, the length that dbms_output was reporting was the correct length for the full string but when I output the whole string dbms_output was truncating it at the maximum line length. The execute immediate was getting the full string.

    The problem was l_field_list where some of the column names had aliases. This is not a problem in the "select" portion of the SQL statement but is a problem in the "group by" portion. I need to fix my config table to remove the aliases.

Posting Permissions

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