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

From: Erika Grondzakova <Erika.Grondzakova_at_cern.ch>
Date: Wed, 16 Jun 1999 14:57:15 +0200
Message-ID: <37679F2B.63063CEA_at_cern.ch>

Hi,

With few changes could be like this :

create or replace procedure FillupDefaultTabColumns ( pTableName in varchar2 ) is
--

  vSqlStatement varchar2(2000) ;
  vWhereStatement varchar2(2000) ;
  vSqlID number ;
  vErrCode number ;
--

begin
  vSqlStatement := '' ;
  vWhereStatement := '';
  for cColumn in ( select column_name,

                           data_type
                    from   user_tab_columns
                    where  table_name = pTableName ) loop

  vSqlStatement := '' ;
  vWhereStatement := '';

    if ( cColumn.data_type = 'VARCHAR2' ) then       vSqlStatement := vSqlStatement || cColumn.column_name || '='' ''';

      vWhereStatement := vWhereStatement|| cColumn.column_name || ' IS NULL';

     elsif ( cColumn.data_type = 'NUMBER' ) then
       vSqlStatement :=  vSqlStatement  || cColumn.column_name || '=0' ;
      vWhereStatement := vWhereStatement|| cColumn.column_name || ' IS

NULL';
    end if ;
--

  if ( vSqlStatement is not null ) then

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

                     substr ( vSqlStatement, 1, length ( vSqlStatement
)  ) ||' WHERE '||       
                     substr ( vWhereStatement, 1, length (

vWhereStatement ) );

• dbms_output.put_line(vSqlStatement); /* ^^^^^^^^ this I have used just for testing purpose, how will be the update statement */ /* you can uncomment it if you want to see, how it will be. */ /* DON'T FORGET TO EXECUTE 'SET SERVEROUTPUT ON' statement before running the */ /* procedure to see it on the screen */ vSqlID := dbms_sql.open_cursor ; dbms_sql.parse ( vSqlID, vSqlStatement, DBMS_SQL.V7) ; vErrCode := dbms_sql.execute ( vSqlID ) ; dbms_sql.close_cursor ( vSqlID ) ; -- end if ; end loop ;

--

end ;
/

Hope it will work as you want...

Erika

George Gifford

> 
> Alexandre,
> 
> Thank you for your generous and swift reply!!  I was able to create and
> compile your procedure; however, I had to make the following modifications
> to get it to compile without errors:
> ===============================
> 
> (1) I had to modify the following 2 lines by changing the == to = :
> 
>     if ( cColumn.data_type == 'VARCHAR2' ) then
>     elsif ( cColumn.data_type == 'NUMERIC' ) then
> 
> (2) Also had to change cColumns in the following line to cColumn:
> 
>   for cColumns in ( select column_name,
> 
> (3) And had to change the v7 in the following line to DBMS_SQL.V7:
> 
>     dbms_sql.parse ( vSqlID, vSqlStatement, v7 ) ;
> ===============================
> 
> After it compiled I attempted to run it passing the procedure a table name
> as follows:
> 
>   EXECUTE FillupDefaultTabColumns( 'TEST_TABLE');
> 
> I get the "PL/SQL procedure successfully completed." message; however, it
> didn't take more than 1 second to get this message and looking at the data
> shows me that nothing has happened.
> 
> I am pretty much a beginner when it comes to using PL/SQL and Dynamic
> PL/SQL, but I can follow your code somewhat and understand what is trying to
> be done.
> 
> I do appreciate your help on this!!  Could you continue to offer your help
> until I get this resolved??
> 
> Thanks Again,
> 
> George - SSI
> California, USA
> 
> alexjunq_at_terra.softdes.com.br wrote in message
> <7k712l$7te$1_at_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_at_~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 Wed Jun 16 1999 - 14:57:15 CEST