Sending CLOB through PL/SQL
I have a VB front end and I want to send a CLOB datatype in an Oracle 9i database through PL/SQL.
First, I create a table with a CLOB datatype:
create table text_resume (resume_version number, description clob)where 'description' is a CLOB datatype.
Next I insert data into this table:
insert into text_resume values (1, ' Experience Summary Over 20 years of experience in IT, including 6+ years of experience in database and application administration, installation, creating, upgrading, configuration, tuning, implementation, backup and recovering, monitoring, deploying and user group support as well as UNIX shell scripts. Oracle Certified Professional in Oracle 9i with experiences in developing, migration, maintaining and supporting large multiple Oracle production Databases 10g/9i/8i/8. Strong background in logical and physical RDBMS design, and functional support of Oracle Applications, Forms, Reports, PL/SQL scripts for packages, stored procedures and database triggers. Proven ability to successfully interact on an interpersonal level and function in team environments. '); commit;
The next step is to create a function called get_clob. This function accepts a number and returns the CLOB field from table text_resume:
create or replace function get_clob(p_version number) return clob as v_return clob; begin select description into v_return from text_resume where resume_version = p_version; return v_return; end;where the return value is a CLOB datatype.
Next execute the function to get the data:
select get_clob(1) from dual
Results:
Experience Summary Over 20 years of experience in IT, including 6+ years of experience in database and application administration, installation, creating, upgrading, configuration, tuning, implementation, backup and recovering, monitoring, deploying and user group support as well as UNIX shell scripts. Oracle Certified Professional in Oracle 9i with experiences in developing, migration, maintaining and supporting large multiple Oracle production Databases 10g/9i/8i/8. Strong background in logical and physical RDBMS design, and functional support of Oracle Applications, Forms, Reports, PL/SQL scripts for packages, stored procedures and database triggers. Proven ability to successfully interact on an interpersonal level and function in team environments.