Home » SQL & PL/SQL » SQL & PL/SQL » how to assign a value for CLOB in Oracle 8i (how to assign a value for CLOB in Oracle 8i)
how to assign a value for CLOB in Oracle 8i [message #413081] Tue, 14 July 2009 05:41 Go to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
Hi,
I have a function like this:

create or replace FUNCTION "ALLRECORDFN" (NUMBERVAL IN NUMBER, simplereci in REFTEST_TYPES%ROWTYPE, simplerecio in out REFTEST_TYPES%ROWTYPE)
RETURN REFTEST_TYPES%ROWTYPE
as simplereco REFTEST_TYPES%ROWTYPE;
begin
simplereco := simplereci;
simplerecio.COL_NUMBER := 234;
simplerecio.COL_VARCHAR2 :='AFDSFVARCHAR2TESTDATASCDASSAA';
simplerecio.COL_CLOB :='DSGFVSDJ237RYHVB2893475349';
simplerecio.COL_DATE :='04-JUN-07';
return simplereco;
END;
/

It is throwing a Warning: Function created with compilation errors., when I comment the CLOB part,I am able to create the function. Could you please let me know how to assign a value in Oracle 8i DB for CLOB.
Re: how to assign a value for CLOB in Oracle 8i [message #413083 is a reply to message #413081] Tue, 14 July 2009 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Mon, 15 December 2008 08:57
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
...

Use SQL*Plus and copy and paste your session.
Use "show error".

Regards
Michel

Re: how to assign a value for CLOB in Oracle 8i [message #413087 is a reply to message #413083] Tue, 14 July 2009 05:58 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
Hey Michel
If you know the answer respond otherwise dont respond.. dont give useless responses.


Re: how to assign a value for CLOB in Oracle 8i [message #413091 is a reply to message #413081] Tue, 14 July 2009 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Telling you to use show error to see what the actual error messages were would be the correct response here.
And you'll find people will be far more willing to help you if you follow the forum guide.
Re: how to assign a value for CLOB in Oracle 8i [message #413092 is a reply to message #413081] Tue, 14 July 2009 06:11 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

arunsuresh100,

Prove everybody that he has given a USELESS response, watch out man he is a senior Pro out here, Just Imagine how awkard it looks when we do not Format our question.

You may know the DDL/DML and all business logic required for it. For make it simple to the other fellow members you should post them, only then you would have a good chance of getting the answer.

I don't think that Michel has provided USELESS response at all
Re: how to assign a value for CLOB in Oracle 8i [message #413097 is a reply to message #413092] Tue, 14 July 2009 06:34 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
Hey Sorry to all the guys for my mistake and usage of words. Specially to Michel.
Your right, formatting is necessary, but situation is like that don't have much time to do all this.

I am getting a Warning when compiling. But when I comment the CLOB part it's creating successfully.

Here is the query, please help me out, its bit urgent.

CREATE OR REPLACE FUNCTION "swq"
(numberval IN NUMBER,
simplereci IN reftest_types%ROWTYPE,
simplerecio IN OUT reftest_types%ROWTYPE)
RETURN reftest_types%ROWTYPE
AS
simplereco reftest_types%ROWTYPE;
BEGIN
simplereco := simplereci;

simplerecio.col_number := 234;

simplerecio.col_varchar2 := 'AFDSFVARCHAR2TESTDATASCDASSAA';

simplerecio.col_clob := 'DSGFVSDJ237RYHVB2893475349';

simplerecio.col_date := '04-JUN-07';

RETURN simplereco;
END;
/

Please send me the response.

Thanks in Advance
Arun
Re: how to assign a value for CLOB in Oracle 8i [message #413101 is a reply to message #413097] Tue, 14 July 2009 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is still not formatted and you didn't read the posts.
We told you to execute "show error" when getting your error message.

If you want to get urgent answer you need to urgently popst the requested information.
Once again read forum guide, it is clearly explained what YOU MUST do to get a quick answer.

Regards
Michel
Re: how to assign a value for CLOB in Oracle 8i [message #413102 is a reply to message #413081] Tue, 14 July 2009 06:46 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

Your right, formatting is necessary, but situation is like that don't have much time to do all this.



But you wasted your time in posting it twice,explaining and using some unnecessary conversations.

I don't think I need too much time if I have SQL Formatter http://www.orafaq.com/utilities/sqlformatter.htm and

CREATE OR REPLACE FUNCTION "swq" 
     (numberval    IN NUMBER, 
      simplereci   IN reftest_types%ROWTYPE, 
      simplerecio  IN OUT reftest_types%ROWTYPE) 
RETURN reftest_types%ROWTYPE 
AS 
  simplereco  reftest_types%ROWTYPE; 
BEGIN 
  simplereco := simplereci; 
   
  simplerecio.col_number := 234; 
   
  simplerecio.col_varchar2 := 'AFDSFVARCHAR2TESTDATASCDASSAA'; 
   
  simplerecio.col_clob := 'DSGFVSDJ237RYHVB2893475349'; 
   
  simplerecio.col_date := '04-JUN-07'; 
   
  RETURN simplereco; 
END; 
/ 

[Updated on: Tue, 14 July 2009 06:47]

Report message to a moderator

Re: how to assign a value for CLOB in Oracle 8i [message #413292 is a reply to message #413102] Wed, 15 July 2009 02:50 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
Guys could you please update the answers if any body is aware of it. Its bit urgent.
Re: how to assign a value for CLOB in Oracle 8i [message #413293 is a reply to message #413092] Wed, 15 July 2009 02:51 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
If any one is aware of the answer pls update it.
Re: how to assign a value for CLOB in Oracle 8i [message #413294 is a reply to message #413292] Wed, 15 July 2009 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You still haven't told us what the error you're getting is!!!

I admire your faith that we can accurately emulate obsolete database versions in our heads without any table definitions, and predict what the error you'll get will be, but it would make it easier if you'd actually tell us what the exact problem is.
Re: how to assign a value for CLOB in Oracle 8i [message #413331 is a reply to message #413294] Wed, 15 July 2009 04:35 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
While creating the function it is showing Warning:Function created with compilation errors.

SQL> show errors
No errors.
SQL>

It is showing the status as above for errors.


Thanks
Arun
Re: how to assign a value for CLOB in Oracle 8i [message #413334 is a reply to message #413331] Wed, 15 July 2009 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still refuse to follow the guidelines, how can you expect we help you?

Regards
Michel
Re: how to assign a value for CLOB in Oracle 8i [message #413341 is a reply to message #413081] Wed, 15 July 2009 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try this:
show errors function <function name>


And don't wrap your function names in double quotes - it makes it case sensitive.
Re: how to assign a value for CLOB in Oracle 8i [message #413344 is a reply to message #413341] Wed, 15 July 2009 06:05 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
Thanks for the update.
I tried using the syntax

SQL> show errors function swq
Errors for FUNCTION SWQ:

LINE/COL ERROR
-------- --------------------------------------------------------
18/3 PL/SQL: Statement ignored
18/27 PLS-00382: expression is of wrong type


and the 18th line corresponding to below mentioned in the query:

18 simplerecio.col_clob := 'DSGFVSDJ237RYHVB2893475349';
Re: how to assign a value for CLOB in Oracle 8i [message #413347 is a reply to message #413344] Wed, 15 July 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 15 July 2009 11:43
You still refuse to follow the guidelines, how can you expect we help you?

Regards
Michel


Re: how to assign a value for CLOB in Oracle 8i [message #413349 is a reply to message #413344] Wed, 15 July 2009 06:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I know that in some Oracle version it was impossible to assign a CLOB a VARCHAR2 value with a simple :=

I'm not quite shure if it was 7.4.something or 8.1.something.

My memory regarding the last millennium is a bit hazy.
Re: how to assign a value for CLOB in Oracle 8i [message #413355 is a reply to message #413349] Wed, 15 July 2009 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You may well need to use DBMS_LOB
Re: how to assign a value for CLOB in Oracle 8i [message #413361 is a reply to message #413349] Wed, 15 July 2009 06:59 Go to previous messageGo to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
I found out from the metalink that:
A direct insert into the CLOB field does not work.
Use the DBMS_LOB pacakage to access and manipulate the (C)LOB data.

For example:
SET serveroutput on;

DROP TABLE TEMP;

CREATE TABLE TEMP (
KEY NUMBER,
clob_col CLOB);

INSERT INTO TEMP
VALUES (10,
Empty_clob());

DECLARE
lob_loc CLOB;
amt BINARY_INTEGER;
pos INTEGER := 1;
v_clob VARCHAR2(32000);
BEGIN
FOR i IN 1.. 12000 LOOP
v_clob := v_clob
||'a';
END LOOP;

INSERT INTO TEMP
VALUES (20,
Empty_clob());

amt := Length(v_clob);

SELECT clob_col
INTO lob_loc
FROM TEMP
WHERE KEY = 20;

dbms_lob.Write(lob_loc,amt,pos,v_clob);

dbms_output.Put_line('Inserted');
END;
/

I am finding it bit difficult in using the same scenario for a function/procedure.
Could anyone help me out.

Thanks
Arun
Re: how to assign a value for CLOB in Oracle 8i [message #413368 is a reply to message #413361] Wed, 15 July 2009 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could anyone help me out.

Not me until you follow the forum guide.

Regards
Michel
Re: how to assign a value for CLOB in Oracle 8i [message #413385 is a reply to message #413361] Wed, 15 July 2009 08:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem I'm having helping is that you're using 8i, and that's obsolete. I have no access to an 8i Dbto test things on.

LOB handling changed a lot between 8i and 9i
Previous Topic: Check Constraint
Next Topic: Connect By NoCycle not returning all rows
Goto Forum:
  


Current Time: Sat Dec 10 22:17:20 CST 2016

Total time taken to generate the page: 0.15191 seconds