-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
/
Last edited by anyoneokay; 11-29-2007 at 09:05 AM.
-
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
Last edited by anyoneokay; 11-29-2007 at 09:08 AM.
-
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.
Last edited by anyoneokay; 11-29-2007 at 09:09 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|