I have inherited a packaged procedure that looks like this:
cursor c(s_month varchar2) is
s_month contract_month -- remember this guy was passed as a cursor(parm)
Later on the cursor is used in the procedure thusly:
for rv in c(some_month) loop
/* do some processing */
insert into some_table( col1, col2, contract_month)
values (rv.some_key, rv.some_data, rv.contract_month);
when others then
blah, blah, blah
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?
Such a principle works correctly; here's a short example:
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);
SQL> CREATE OR REPLACE PROCEDURE P_Cur (p_deptno IN NUMBER)
3 CURSOR c (p_deptno NUMBER) IS
4 SELECT p_deptno, ename FROM EMP
5 WHERE deptno = p_deptno;
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;
OK, let's execute it now:
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> SELECT * FROM t_cur;
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).
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!