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?
 
slightwv (䄆 Netminder)Connect With a Mentor 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.