Updating clob

24-Mar-2015 13:54

'ln_step ' ' where rowid = :x', 14 dbms_sql.native ); 15 dbms_sql.bind_variable( l_cursor, ':x', p_rowid ); 16 17 dbms_sql.define_column_long(l_cursor, 1); 18 l_n := dbms_sql.execute(l_cursor); 19 20 if (dbms_sql.fetch_rows(l_cursor)0) 21 then 22 dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , 23 l_long_val, l_long_len ); 24 end if; 25 dbms_sql.close_cursor(l_cursor); 26 return l_long_val; 27 end getlong; 28 / Function created. WORLD select getlong('T', 'X', rowid) from t; GETLONG('T','X', ROWID) ----------------------------------------------------------------------------------------------------------------------------------- **************************************************..... I just wanted to know why this failed in PURE SQL while it worked in PL/SQL loop.... Type ----------------------------------------------------- ----- T_NUMBER NOT NULL CHAR(13) USER_COMMENT LONG In a stored Proc I implemented like.

i just wrote a function like // create or replace function getlong1( p_rowid in rowid) return varchar2 as l_data long; begin select remarks into l_data from cr_claimheader where rowid = p_rowid; return substr( l_data, 1, 4000 ); end; // to get the first 4000 chars of remarks i am calling this function like // select getlong1(rowid) from cr_claimheader where headersrno = 4005 // Fine, this is working fine when i have the data with less than 4000 chars. suppose if the length of data increase means, it returns just null... select * from t2; X ---------- Y -------------------------------------------------------------------------------- 1 abcde When I am doing a Select-Insert, the datatype conversion gives an error but when i am using values clause, it works fine... September 20, 2002 - am UTC because the documentation says it will? Put more then 32k in the long and it'll fail miserably in plsql as well. Tom, I have a table which was created with a long column and has around 1 M records in it... In this process, I am saving the data into temp table with varchar2 column and modifying the main table. procedure sp_xx(in_c TNo IN NUMBER, l_var OUT VARCHAR2) IS select sf_pc_cmt_FNC(in_c TNo) into l_var from t_comment; end; the function "sf_pc_cmt_FNC" is exactly look like the code snippet you provided.

CREATE TABLE AD_MESSAGES (MSG_NUM NUMBER(5) DEFAULT 0 NOT NULL , MSG_CONTENT LONG NOT NULL , MSG_TS DATE NOT NULL , AD_DOC_DOC_NUM NUMBER(5) NOT NULL , AD_DOC_ADC_CONTAINER_NUM NUMBER(12) NOT NULL , AML_MSG_LEVEL VARCHAR2(10) NOT NULL ) TABLESPACE AD_MESSAGES / CREATE TABLE TEMP_AD_MESSAGES (MSG_NUM NUMBER(5) DEFAULT 0 NOT NULL , MSG_CONTENT VARCHAR2(4000) NOT NULL , MSG_TS DATE NOT NULL , AD_DOC_DOC_NUM NUMBER(5) NOT NULL , AD_DOC_ADC_CONTAINER_NUM NUMBER(12) NOT NULL , AML_MSG_LEVEL VARCHAR2(10) NOT NULL ) TABLESPACE AD_MESSAGE / ALTER TABLE TEMP_AD_MESSAGES NOLOGGING / DECLARE CURSOR cur_msg IS SELECT msg_num ,msg_content ,msg_ts ,ad_doc_doc_num ,ad_doc_adc_container_num ,aml_msg_level FROM AD_MESSAGES; -- ln_msg_num TEMP_AD_MESSAGES. create table as select -- Hi Tom, Do you see any problem, if we create a function which takes a rowid parameter and then selects the long column into a varchar2 variable from the rowid and returns this varchar2 variable.

After this we just use this function in the insert statement. insert /*+ APPEND */ into TEMP_AD_MESSAGES select MSG_NUM ,my_to_varchar_func(rowid), MSG_TS , AD_DOC_DOC_NUM, AD_DOC_ADC_CONTAINER_NUM, AML_MSG_LEVEL from ad_messages; We know that our long field has less than 2000 bytes of length.

February 11, 2003 - am UTC I don't understand this at all -- sorry? My routine returns just the first 4000 characters of a long --regardless of how long the long is.IF your longs are all 32k or less your code should be: is l_tmp long; begin select long_col into l_tmp from t_comment where ...; return substr( l_tmp, 1, 4000 ); end; if they EXCEED 32k for just one row -- you have to use dbms_sql in plsql Hi Tom, What method would you suggest for converting a long column to varchar2. We want to generate least number of archive logs for this.

Aside from radiocarbon dating, ISO/IEC 17005-accredited Beta Analytic also provides biobased content/renewable carbon content testing to manufacturers, product distributors, and researchers worldwide.… continue reading »

Read more

This guitar is absolutely without a doubt the best guitar for the money. The action of the guitar was absolutely perfect as soon as I picked it up.… continue reading »

Read more

Now, I can vend just on the weekends because I have loyal customers who keep coming back for my authentic soft tamales.… continue reading »

Read more

Un projet de sécurité comporte plusieurs phases : conception du système, installation des produits, maintenance et assistance.… continue reading »

Read more