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

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 - 08:32:29 CEST