Re: SQL - PL/SQL Question. Please Help!!

From: <alexjunq_at_terra.softdes.com.br>
Date: Wed, 16 Jun 1999 02:10:35 GMT
Message-ID: <7k712l$7te$1@nnrp1.deja.com>

Hi George,

  this procedure may help you.

  have fun

  Alexandre Junqueira

---

procedure FillupDefaultTabColumns ( pTableName in varchar2 ) is   --
  vSqlStatement varchar2(2000) ;
  vSqlID number ;
  vErrCode number ;
  --
begin
  for cColumns in ( select column_name,

                           data_type
                    from   user_tab_columns
                    where  table_name = pTableName ) loop

--

    vSqlStatement := '' ;
--

    if ( cColumn.data_type == 'VARCHAR2' ) then

      --
      vSqlStatement := vSqlStatement || cColumn.column_name || '='' '','
;
      --
    elsif ( cColumn.data_type == 'NUMERIC' ) then
      --
      vSqlStatement := vSqlStatement || cColumn.column_name || '=0,' ;
      --

    end if ;
--

  end loop ;
  --
  if ( vSqlStatement is null ) then
--

    vSqlStatement := 'update ' || pTableName || ' set ' ||

                     substr ( vSqlStatement, 1, length ( vSqlStatement )

- 1 ) ;

    vSqlID := dbms_sql.open_cursor ;
    dbms_sql.parse ( vSqlID, vSqlStatement, v7 ) ;     vErrCode := dbms_sql.execute ( vSqlID ) ;     dbms_sql.close_cursor ( vSqlID ) ;
--

  end if ;
  --
end ;
/

---

In article <LgB93.38$5E4.10347_at_news2.randori.com>,   "George Gifford" <georgegifford@~usa~.net <--- ANIT-SPAM (Remove ~)> wrote:
> Does anyone have any suggestions or pl/sql code that will accept a tablename

> and then update/replace ALL Null Numeric fields with a 0, and all Null
> VarChar2 Fields with a space (' ')??
>
> Or a PL/SQL Block that will run through each record in a table and

update

> specific fields with a default value???
>
> Any help would be greatly appreciated!!!
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 15 1999 - 21:10:35 CDT