Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: Oracle 10g : PL/SQL cursor question ...

    I have inherited a packaged procedure that looks like this:
    ...

    cursor c(s_month varchar2) is
    select some_key,
    some_data,
    s_month contract_month -- remember this guy was passed as a cursor(parm)
    from some_table
    where come_cond;
    ...

    Later on the cursor is used in the procedure thusly:

    ...
    for rv in c(some_month) loop
    /* do some processing */

    begin
    insert into some_table( col1, col2, contract_month)
    values (rv.some_key, rv.some_data, rv.contract_month);
    exception
    when others then
    blah, blah, blah
    end;
    end loop;
    ...

    The insert statement inside the for loop tries to reference contract month from the cursor. Remember this value is substituted into the select list from the cursors parm. When the insert is attempted I get an error ORA-1400: "cannot insert NULL into SOME_TABLE.CONTRACT_MONTH"

    I'm trying to see if there is a problem with the procedural logic that populates the parm before it is passed to the cursor. It does not look like that is the case initially.

    In the meantime can any of you guys/gals figure out if there is a problem trying to substitute a literal into the select list of a cursor as this proc is trying to do?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Such a principle works correctly; here's a short example:
    Code:
    SQL> -- note that deptno is a NOT NULL column; we'll insert a parameter in here
    SQL> CREATE TABLE t_cur (deptno NUMBER NOT NULL, ename VARCHAR2(20) NOT NULL);
    
    Table created.
    
    SQL> CREATE OR REPLACE PROCEDURE P_Cur (p_deptno IN NUMBER)
      2  IS
      3    CURSOR c (p_deptno NUMBER) IS
      4      SELECT p_deptno, ename FROM EMP
      5        WHERE deptno = p_deptno;
      6  BEGIN
      7    dbms_output.put_line('p_deptno = ' || p_deptno);  --> check parameter value
      8    FOR rv IN c(p_deptno) LOOP
      9      INSERT INTO t_cur (deptno, ename)
     10        VALUES (rv.p_deptno, rv.ename);               --> inserting a parameter
     11    END LOOP;
     12  END;
     13  /
    
    Procedure created.
    OK, let's execute it now:
    Code:
    SQL> EXEC P_Cur (10);
    p_deptno = 10                --> it seems to be OK - a parameter has its value
    
    PL/SQL procedure successfully completed.
    
    SQL> -- see? No error here!
    SQL>
    SQL> SELECT * FROM t_cur;
    
        DEPTNO ENAME
    ---------- --------------------
            10 CLARK
            10 KING
            10 MILLER
    
    SQL>
    It appears that 's_month' is null in your case. Is it? Check it by adding a DBMS_OUTPUT.PUT_LINE command (don't forget to enable the output by SET SETVEROUTPUT ON).

  3. #3
    Join Date
    Dec 2007
    Posts
    3
    Thanks,

    As it turns out there is an Oracle Advanced Queue process that is supposed to propogate the data. The procedure is called by a after insert trigger on a table. The client assumes that the data will be there by the time the proc gets executed! Really dumb!

    A little factoid that my boss neglected to tell me before he gave me the assignment!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •