title: Oracle equivalent of T-SQL SET @var = @var + ' ';
body: <p>Switching over to Oracle from SQL Server. How do I concatenate a string on to a variable inside a loop? In SQL Server I would've wrote something like </p>
  
  <pre><code>DECLARE @var varchar(max);
  WHILE ([I'm doing something or whatever on a table])
  BEGIN
      SET @var = @var + ' additional text';
  END
  SELECT @var AS 'result';
  </code></pre>
  
  <p>This is what I have in Oracle so far in my stored procedure. (Going to evolve it to an autogen for ClassMap files for NHibernate)</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE WWNA.UT_MAPPINGHELPER
  (
      TABLE_NAME IN ALL_TABLES.TABLE_NAME%TYPE
      , OWNER_NAME IN ALL_TABLES.OWNER%TYPE
      , CLASS_DEFINITION IN OUT VARCHAR2
  )
     IS
     CURSOR TABLE_INFO_CUR
     IS
      SELECT 
          T.TABLE_NAME
          , C.COLUMN_NAME
          ,C.DATA_TYPE
          ,C.DATA_LENGTH
          ,C.DATA_PRECISION
          ,C.NULLABLE
      FROM 
          ALL_TABLES T
          JOIN
          ALL_TAB_COLUMNS C
              ON T.TABLE_NAME = C.TABLE_NAME
                  AND T.OWNER = C.OWNER
      WHERE
          T.TABLE_NAME = :TABLE_NAME
          AND T.OWNER = :OWNER_NAME;
      TABLE_INFO_ROW TABLE_INFO_CUR%ROWTYPE;
      build_sql varchar2;
  BEGIN
      --VARIABLE build_sql varchar2(4000);
      :build_sql := '';
      OPEN TABLE_INFO_CUR;
  
      LOOP
          FETCH TABLE_INFO_CUR INTO TABLE_INFO_ROW;
          EXIT WHEN TABLE_INFO_CUR%NOTFOUND;
          SELECT (:build_sql || ' ' || TABLE_INFO_ROW.TABLE_NAME || ' ' || TABLE_INFO_ROW.COLUMN_NAME || ' ' || TABLE_INFO_ROW.DATA_TYPE) INTO :build_sql;
      END LOOP;
      :CLASS_DEFINITION := :build_sql;  
  
      --DBMS_OUTPUT.put_line (CLASS_DEFINITION);
  
  
  END;
  </code></pre>
  
  <p>Thanks</p>