-- Need guide to create Stored Procedure for CLOB datatype
-- My table name XYZ having two columns
col1 as number
col2 as CLOB

select * from XYZ ;
col1 col2(clobtype)
-- ---
1 aasddsf
2 basdfsdf
3 cafdasd
4 dadfsd
5 eafasd

if i pass col1 as input,then
i must get display for col2

sql>exec col1(3) ;

col2
----
cafdasd
This is wht i requied !!

so whats the problem?

Hi Dave .,
i am getting error while i execute procedure for clob
I have attached Document of my code ;

SQL> CREATE TABLE XYZ(col1 number,col2 CLOB);
Table created.
SQL> desc xyz
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 CLOB

SQL> CREATE OR REPLACE PACKAGE clob_proc as
TYPE cursortype IS REF CURSOR;
PROCEDURE return_clob(p_cursor IN OUT cursortype, p_col1 number);
END;
/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY clob_proc as
PROCEDURE return_clob(p_cursor IN OUT cursortype, p_col1 number)
IS
BEGIN
OPEN p_cursor FOR
SELECT col2 FROM xyz
WHERE col1 = p_col1;
END;
END;
/

Warning: Package Body created with compilation errors.

Now Calling procedure :

SQL> var c refcursor;
SQL>
SQL> exec clob_proc.return_clob(:c,1);

############# i am getting error :

ERROR at line 1:
ORA-04063: package body "schemaname.CLOB_PROC" has errors
ORA-06508: PL/SQL: could not find program unit being called: "schemaname.CLOB_PROC"
ORA-06512: at line 1


-------------------------------------------
This is what i must get
PL/SQL procedure successfully completed.
SQL> print c

COL2
------------------------------
abc
SQL>
-----------------------------------------------

Hi,

Do you have a question?

If this is a request for free support, you are in the wrong forum. Try MetaLink:

http://metalink.oracle.com

As to your ORA-06508 error, see here:

http://www.dba-oracle.com/t_ora_06508_pl_s...eing_called.htm

I see that you are spamming this same question to other forums. That's not very nice.

Is this homework?

Sir.,
I am new to CLOB type pl/sql coding , my intention was not to spam. but to gain ideas in pl.sql and
i got clear ideas form the

link : http://asktom.oracle.com/tkyte/ResultSets/index.html

Mycode :
SQL> create or replace package clob_proc as
2 type cursorType is ref cursor;
3 procedure return_clob(p_cursor in out cursorType,p_col1 number)
4 ;
5 end;
6 /

Package created.

SQL> create or replace package body clob_proc as
2 procedure return_clob(p_cursor in out cursorType,p_col1 number)
3 is
4 begin
5 open p_cursor for
6 select tenderid,cellvalue from searchclob where tenderid = p_col1;
7 end;
8 end;
9 /

Package body created.

SQL> var c refcursor;
SQL> exec clob_proc.return_clob(:c,93);

PL/SQL procedure successfully completed.

SQL> print c

it works !!!
-----