Solved

Stored Proc to fetch Clob data

Posted on 2004-11-01
1,170 Views
Last Modified: 2007-12-19
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.


0
Question by:ch_kaushik
    12 Comments
     
    LVL 15

    Expert Comment

    by:andrewst
    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
     
    LVL 75

    Expert Comment

    by:slightwv (䄆 Netminder)
    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
     

    Author Comment

    by:ch_kaushik
    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
     

    Author Comment

    by:ch_kaushik
    Hi Slightwv,

    Application Using C with Pro*C for Oracle conectivity.
    0
     
    LVL 15

    Expert Comment

    by:andrewst
    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
     

    Author Comment

    by:ch_kaushik
    Sorry andrewst it's not working.
    0
     
    LVL 75

    Expert Comment

    by:slightwv (䄆 Netminder)
    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
     
    LVL 15

    Expert Comment

    by:andrewst
    Please post your non-working code...
    0
     

    Author Comment

    by:ch_kaushik
    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
     
    LVL 75

    Accepted Solution

    by:
    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
     

    Author Comment

    by:ch_kaushik
    Hi slightwv,

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

    0
     
    LVL 75

    Expert Comment

    by:slightwv (䄆 Netminder)
    posting due to: "interested in its final disposition"
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    860 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now