Stored Proc to fetch Clob data

Hi,

I have a clob data type field in one of my oracle8i database tables. My application uses this clob field data to store/fetch info consisting of 24000 characters max.

I need a stored proc which when called from my application will fetch that clob data. Problem is I can't use select statement as it would have it's varchar (4000 characters) limit.

Can any one please help me out with the stored proc which when called would fetch those infomations from the clob data field, by passing empty buffers as arguments.

I need the stored proc.


ch_kaushikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewstCommented:
create or replace procedure p (p_val out varchar2) is
  v_clob clob;
begin
  select clobval into v_clob from mytable where ...;
  p_val := dbms_lob.substr(v_clob,24000,1);
end;
/
0
slightwv (䄆 Netminder) Commented:
What language and DB connectivity are you using for your application?
i.e./ VB and ODBC

Many languages can now hanble CLOBs in their native form espically if you are using an Oracle component for connectivity (the exception is ODBC).
0
ch_kaushikAuthor Commented:
Hi Andrewst,

Getting this error after running the stored proc

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TABLE.GETDATA", line 5
ORA-06512: at line 1

using this srored proc,

create or replace procedure getdata (asName IN VARCHAR2, p_val out varchar2) is
v_clob clob;
begin
select details into v_clob from mytable where name=Name;
p_val := dbms_lob.substr(v_clob,24000,1);
end;
/


Any clue??

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ch_kaushikAuthor Commented:
Hi Slightwv,

Application Using C with Pro*C for Oracle conectivity.
0
andrewstCommented:
The problem is in your calling program, which needs to allow for 24000 chars like this:

declare
  v_text varchar2(24000);
begin
  getdata('name',v_text);
end;
/

If the variable is too small, you will get that error.
0
ch_kaushikAuthor Commented:
Sorry andrewst it's not working.
0
slightwv (䄆 Netminder) Commented:
I'm still fuzzy.  

If you are using ProC for the application layer, what is the need for a stored procedure to retrieve the data?  

You should be able to retrieve a CLOB directly from the C code.  At least you should be able to use CLOBs in the stored prodecure and forget all about varchar2.
0
andrewstCommented:
Please post your non-working code...
0
ch_kaushikAuthor Commented:
Ok here is the problem little bit in detail. But first to tell you I am not an Oracle Guy, and need to handle this scenerio.

We are indeed using ProC directly to retrieve the clob data.

Our application uses two seperate oracle tables which uses fields of clob data types.

We are using Pro* C to fetch data from one of the tables all this time, without any problem. But the moment we try to fetch clob data from the second newly introduced table. It doesnot work. And please don't ask me why.

So I thought why not use a stored proc which I will call from my C application through my Pro* C code for the second newly introduced table.

As far as the code goes, the stored proc is already written in my previous comment.  

And my Pro* C code from where I am calling the stored proc is,

void kgetdata(char* name, char* Buffer)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char szName[NAME_SIZE];
    char szBuffer[24000];
    EXEC SQL END DECLARE SECTION;

    memset(szName, 0, NAME_SIZE);
    memset(szBuffer, 0, 24000);
    strcpy(szName, name);

    exec sql execute
    begin
        getdata(:szName, :szBuffer);
    end;
    end-exec;
    strcpy(Buffer, szBuffer);
}

Any Clue ...
0
slightwv (䄆 Netminder) Commented:
I would suggest you do away with the stored proc.  Adding another layer of complexity to an application as a kludge is never a good idea.  I would get the C interface working.  Look for cdemolb.c in $ORACLE_HOME/rdbms/demo.  It is a C program that deals with LOBS.

I also suggest the docs for more information on getting this to work.  I located the above C demo in the Oracle Call Interface Programmer's Guide:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/ociabdem.htm#430289
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ch_kaushikAuthor Commented:
Hi slightwv,

Thanks for the info.
Will look into it sometime later as I am urgently into some other issue.

0
slightwv (䄆 Netminder) Commented:
posting due to: "interested in its final disposition"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.