Insert into clob using plsql bind variable

Insert into clob using plsql bind variable

create table tab1 ( id number primary key , big_text clob);


create or replace procedure insert_into_tab1(a number,b varchar2)
as
begin
insert into tab1 values(a,b);
commit;
end;
/

exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'))

SQL> exec insert_into_tab1(1,rpad('Start string from here ..','32000','.'));

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1;

COUNT(*)
----------
1

SQL> select big_text from tab1;

BIG_TEXT
--------------------------------------------------------------------------------
Start string from here .........................................................

SQL> select dbms_lob.getlength(big_text) from tab1;

DBMS_LOB.GETLENGTH(BIG_TEXT)
----------------------------
32000

SQL>
PLSQL limits my bind variable to 32000.
SQL, the limit is 4,000 characters.
Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters.



refence : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:388196216305

How to Use Flash Recovery Area as your archivelog location

First set :
db_recovery_file_dest='/oracle/flashRecoveryArea'
db_recovery_file_dset_size=<90% of the size '/oracle/flashRecoveryArea'>
Second :
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;

Adding instance on RAC database without dbca ( 10gR2)

The suggested method to add an instance is via the dbca utility ( below is the silent version)

dbca -silent -addInstance -nodeList rac3 -gdbName ocfs -instanceName ocf3 -sysDBAUserName sysdba -sysDBAPassword xxxxx

If a dbca failed , try to add your new instance manually as follows

Add a new Listener using netca
Add a new Listener information in tnsnames.ora ( Remote Listener section)

Edit the tnsnames.ora for all instances


Prepare the database

Create a new thread
====================

ALTER DATABASE
ADD LOGFILE THREAD 3
GROUP 5 ('/ocfs-disk/ocfs/redo05.log') size 50M,
GROUP 6 ('/ocfs-disk/ocfs/redo06.log') size 50M;

alter database enable thread 3;

Create a new Undo tablespace
=============================

create undo tablespace UNDOTBS3 datafile '/ocfs-disk/ocfs/undotbs03.dbf' size 100M autoextend off;

Create init<>.ora and password file from the new node and assign the new thread and undo tablespace

add the instance using srvctl
==============================

srvctl add instance -d ocfs -i ocfs3 -n rac3
srvctl config database -d ocfs
srvctl start instance -d ocfs -i ocfs3

Ensure that the services_names is not set
ALTER SYSTEM SET service_names='' SCOPE=SPFILE SID='*';

NetManager does not display Advance Security Option Under Profile.

Add

INSTALLEDCOMPONENTS=CLIENT,NET8,JAVAVM,SERVER,ANO

under

$ORACLE_HOME/network/tools/NetProperties

Restart netMgr