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;
/