Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

I have a VB front end and I want to send a CLOB datatype in an Oracle 9i database through PL/SQL. I know how to send data if the datatype is number or varchar2, but I don't know how if it is CLOB. Thank you.
I do not know what you mean by "send a CLOB datatype in an Oracle 9i database through PL/SQL," but in most cases you can treat a CLOB the same as a number or varchar2 datatype. I will demonstrate how to receive CLOB data by sending it from a function. (If this does not answer your question, please submit more details.)

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.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close