Help with PL/SQL - CLOB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help with PL/SQL - CLOB

  1. #1
    Join Date
    Nov 2000
    Posts
    173

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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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 08:05 AM.

  4. #4
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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 08:08 AM.

  5. #5
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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 08: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
  •  



Click Here to Expand Forum to Full Width