Help with PL/SQL - CLOB

Hi All,

I am not very experienced in PL/SQL. I need help in creating a simple procedure to insert data into two temp tables. One of the tab.columns is a CLOB. I can't seem to execute the procedure correctly, no matter which way I try. I was calling the procedure with named parameters. The error message is very vague. Thanks in advance. Kathy


BEGIN upd_temp_node_proc (p_cid=>54906, p_pid=>1, p_detailid=>54906, p_type=>1, p_lbl=>'Kathy', p_ordr=>0, p_lvl=>5
4906, p_ptr=>54906, p_class=>0, p_dtype=>7, p_content=>'This is the clob data, it is very large. Next, I need to test while enter
ing more than 4000 characters', p_status_fk=>1); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'UPD_TEMP_NODE_PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Any Suggestions:

CREATE OR REPLACE PROCEDURE "UPD_TEMP_NODE_PROC" (
p_cid IN FAC_NODE.cid%TYPE,
p_pid IN FAC_NODE.pid%TYPE DEFAULT NULL,
p_detailid IN FAC_NODEDETAILS.detail_id%TYPE DEFAULT NULL,
p_type IN FAC_NODETYPE.nodetype_id%TYPE DEFAULT NULL,
p_lbl IN FAC_NODE.lbl%TYPE DEFAULT NULL,
p_ordr IN FAC_NODE.ordr%TYPE DEFAULT NULL,
p_lvl IN FAC_NODE.lvl%TYPE DEFAULT NULL,
p_ptr IN FAC_NODE.ptr%TYPE DEFAULT NULL,
p_class IN NUMBER DEFAULT NULL,
p_dtype IN FAC_NODEDTYPE.nodedtype_id%TYPE DEFAULT NULL,
p_content IN FAC_NODEDETAILS.content%TYPE,
p_effective_date IN FAC_NODEDETAILS.effective_date%TYPE DEFAULT NULL,
p_status_fk IN FAC_NODEDETAILS.status_fk%TYPE DEFAULT NULL,
p_complete OUT VARCHAR2
)
AS
RETURNING VARCHAR2(10) := 'FALSE';
sql_stmt1 VARCHAR2(500);
sql_stmt2 VARCHAR2(500);
error_code NUMBER;
error_message VARCHAR2(500);
BEGIN
BEGIN
INSERT INTO facnode_temp (
cid, pid, nodetype_fk, lbl, ordr, lvl,
cre8_date, cre8_by,
mod_date, mod_by,
ptr, node_class, nodedtype_fk)
VALUES (
p_cid, p_pid, p_type, p_lbl, p_ordr, p_lvl,
sysdate, user,
sysdate, user,
p_ptr, p_class, p_dtype);
EXCEPTION
WHEN OTHERS THEN
p_complete := 'FALSE';
error_code :=SQLCODE;
error_message :=SQLERRM || 'Inserting into FAC NODE TEMP Table ';
INSERT INTO ERRORS (e_user, e_date, error_code, error_message)
VALUES (USER, SYSDATE, error_code, error_message);
END;

BEGIN
INSERT INTO facnodedetails_temp (
detail_id,
cid_fk,
effective_date,
status_fk,
content,
cre8_date,
cre8_by,
mod_date,
mod_by)
VALUES (p_detailid, p_cid, p_effective_date, p_status_fk, p_content,
sysdate, user, sysdate, user);
EXCEPTION
WHEN OTHERS THEN
p_complete := 'FALSE';
error_code :=SQLCODE;
error_message :=SQLERRM || 'Inserting into FAC NODEDETAILS TEMP Table ';
INSERT INTO ERRORS (e_user, e_date, error_code, error_message)
VALUES (USER, SYSDATE, error_code, error_message);
END;
COMMIT;
p_complete := 'TRUE';
END Upd_Temp_Node_Proc;
/

You might want to check DBMS_LOB package.

The operation of clob is different from other data types.
If you want to insert or update a clob column, you must do as below:
(1) you must go to the clob row firstly;
(2) then you can update clob column used by dbms_lob package(such as dbms_lob.write, dbms_lob.write, dbms_lob.writeappend);

Here is an easy example:
--------------------------------------------------------

Code:

---

create table t_clob2
(
  no      number,
  content clob
);

create or replace procedure sp_clob(ip_no number,ip_content varchar2)
is
  lv_no      number;
  lv_clob    clob;
  lv_sqlstr  varchar2(4000);
begin
  insert into t_clob2 values(ip_no,empty_clob());
  commit;
  lv_sqlstr := 'select content from t_clob2 where no=' || ip_no || ' for update';
  execute immediate lv_sqlstr into lv_clob;
  dbms_lob.write(lv_clob,length(ip_content),1,ip_content);
  commit;
end;

SQL> exec sp_clob(1,'abcdefg');
 
PL/SQL procedure successfully completed
 
SQL> select * from t_clob2;
 
        NO CONTENT
--------------------------------------------------------------------------
        1 abcdefg
/

---

I have code another exmaple as below, for inserting or updating clob column.
I think it can help you to resolve your problem.

the data from t_src_clob
NO CONTENT(clob)
--------------------
1 xxx
2 yyy

and i want insert a row to t_dest_clob
NO CONTENT(clob)
--------------------
3
then set t_src_clob's content value(whose no is 'xxx' ) for t_dest_clob's content.

At last, the data from t_dest_clob will become
NO CONTENT(clob)
--------------------
3 xxx

The original data for testing：

Code:

---

--drop table t_src_clob;
--drop table t_dest_clob;
create table t_src_clob
(
  no      number,
  content clob
);
alter table t_src_clob
  add constraint T_SRC_CLOB_PK primary key(no);
 
create table t_dest_clob
(
  no      number,
  content clob
);
alter table t_dest_clob
  add constraint T_DEST_CLOB_PK primary key(no);

insert into t_src_clob  values(1,'xxx');
insert into t_src_clob  values(2,'yyy');

commit;

select * from t_src_clob;
select * from t_dest_clob;

---

Procedure and function for update clob:

Code:

---

create or replace function getclob(ip_table_name  in varchar2,  --the clob table
                                  ip_PK_column  in varchar2,  --the pk column name of clob table
                                  ip_PK_value    in number,    --the value of pk column
                                  ip_clob_column in varchar2  --the clob column'name
                                  ) return clob is
  lv_clob  clob;
  lv_amount number := 2000;
  lv_offset number := 1;
  lv_sqlstr varchar2(1000);
begin
  lv_sqlstr := 'select ' || ip_clob_column || ' from ' || ip_table_name ||
              ' where ' || ip_PK_column || '=' || ip_PK_value;
  EXECUTE IMMEDIATE lv_sqlstr
    INTO lv_clob;
  return lv_clob;
end;
/

create or replace procedure updateclob(ip_dest_table_name  in varchar2,
                                      ip_dest_PK_column  in varchar2,
                                      ip_dest_PK_value    in number, 
                                      ip_dest_clob_column in varchar2,
                                      ip_src_table_name  in varchar2,
                                      ip_src_PK_column    in varchar2,
                                      ip_src_PK_value    in number,
                                      ip_src_clob_column  in varchar2)
is 
  lv_src_clob  clob;
  lv_dest_clob clob; 
  lv_amt      number;   
  lv_sqlstr    varchar2(1000); 
begin
  lv_sqlstr := 'select ' || ip_dest_clob_column || ' from ' || ip_dest_table_name ||
        ' where ' || ip_dest_PK_column || '=' || ip_dest_PK_value || 'for update';
  EXECUTE IMMEDIATE lv_sqlstr
    INTO lv_dest_clob;
  lv_src_lob := getclob(ip_src_table_name,ip_src_PK_column,ip_src_PK_value,ip_src_clob_column);
  lv_amt := dbms_lob.getlength(lv_src_lob);
  dbms_lob.copy(lv_dest_clob,lv_src_lob,lv_amt);
  commit;
end;
/

---

Here is an example for inserting clob

Code:

---

SQL> select * from t_src_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
        1 xxx
        2 yyy
SQL> select * from t_dest_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
 
SQL> insert into t_dest_clob values(5,'ooo');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> select * from t_dest_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
        3

SQL> exec updateclob('t_dest_clob','no',3,'content','t_src_clob','no',1,'content');
 
PL/SQL procedure successfully completed
 
SQL> select * from t_src_clob;
 
        NO CONTENT
---------- --------------------------------------------------------------------------------
        3 xxx

---

So you must modify the "INSERT INTO facnodedetails_temp...." SQL.
Firstly, insert into facnodedetails_temp without content;
Then, select the clob row for update, and insert clob column used by dbms_lob package like i code as above.