Hello
I am updating All Long Columns in database, Replacing $# with CHR(10).
For Ex. emails sent to you whenever someone replies. $# Only registered users are eligible.
After updating this will look like
emails sent to you whenever someone replies.
Only registered users are eligible.
For That I written following Procedure Which is giving error, Please help me to correct this procedure.
Thanks & Regards
Shailesh
Create or Replace Procedure LongUPD as
V_TabName Varchar2(30);
V_ColName Varchar2(30);
V_Id Varchar2(20);
V_LongText Varchar2(32767);
TYPE Cur1 IS REF CURSOR;
Cur_Col Cur1;
Begin
Open Cur_Col For
Select Table_Name,Column_Name
From User_Tab_Columns
Where Data_Type = 'LONG';
Loop
Fetch Cur_Col into V_TabName, V_ColName;
EXIT WHEN Cur_Col%NOTFOUND;
Select Rowid,Nvl(Replace(V_ColName,'$#',CHR(10)),'')
into V_Id,V_LongText from V_tabName where rowid=v_id;
EXECUTE IMMEDIATE 'Update '||V_tabName||' Set '||V_ColName=V_LongText||' where rowid=rowid';
End Loop;
Close Cur_Col;
Exception
When NO_DATA_FOUND
Then Null;
End;
/
