Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Proc to fetch Clob data

Posted on 2004-11-01
13
Medium Priority
?
1,196 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
Comment
Question by:ch_kaushik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
13 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 12472380
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12472921
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
ID: 12480152
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ch_kaushik
ID: 12480159
Hi Slightwv,

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

Expert Comment

by:andrewst
ID: 12481428
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
ID: 12483323
Sorry andrewst it's not working.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12483622
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
ID: 12483739
Please post your non-working code...
0
 

Author Comment

by:ch_kaushik
ID: 12490001
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 12493252
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
ID: 12493303
Hi slightwv,

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

0
 
LVL 77

Expert Comment

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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