Problems using %TYPE, %ROWTYPE in pl/sql when column NOT NULL?

[SJSFoxPro]

We have just converted an Oracle database from 9i to 10g. We have a number of procedures that declare parameters using %TYPE and make variable declarations using %TYPE or %ROWTYPE. For example:

PROCEDURE create_cl_search_query_proc (ps_lst_nme IN resource.lst_nme%TYPE) IS
S_search_data search_results_gtmp%ROWTYPE
-- code
END create_cl_search_query_proc;

In these two cases, both contain columns that have NOT NULL check constraints.

I have been searching endlessly for Oracle 10g New Features and/or 10g bugs and/or 10g pl/sql best practices that explain why using %TYPE or %ROWTYPE is a problem when there are NOT NULL check constraints on the columns.

Has anyone found any documentation or had similar issues with this?

I know this question is vague, I’m looking more for pointers to documentation on this than answers. Although, any direction is welcome!

Thanks!

 

[Dagon]

I'm not clear why you think it should be a problem. Are you getting some sort of error ?

 

[SJSFoxPro]

Sorry... I guess I didn't make that totally clear.

I couldn't even run the procedure without an error until I changed the parameter to ps_lst_nme IN varchar. The error messages were nearly useless. I realized the point of failure after setting some breakpoints. Once I could get the procedure to run, I was getting a fetch error when trying to FETCH a cursor INTO s_search_data (the cursor happened to have 0 rows). The interim solution around that was to check for IF cursor%ROWCOUNT > 0 THEN do the FETCH INTO s_search_data.

I have found workarounds, however, this kind of code is throughout our procedures. I want to get a handle on known 10g issues and best practice workarounds for these issues. None of the 10g "new feature" white papers I have found touch on these issues.

That is really the point of my post. I can't find any documentation to justify my findings.

 

[carp]

While the error codes may have been useless to you, it is quite possible that someone else has seen them before and knows what they mean. A NOT NULL constraint on a column does not (to my knowledge) affect the use of %TYPE or %ROWTYPE. We are using these methods throughout our code, and it has worked fine in 7.3, 8i, 9i, and 10g. So I suspect there is something else at play here.

As to your cursor fetching issues - that is typically handled with the following construct:

LOOP
FETCH my_cursor INTO my_variable;
EXIT WHEN my_cursor%NOTFOUND;
END LOOP;

 

[SJSFoxPro]

I have found the cause of our problem is what appears to be a tightened restriction enforced in 10g. PL/SQL documentation for 9i and 10g is clear that when using %ROWTYPE, “the column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement” [and include all columns]. This was an unenforced restriction in Oracle 9i and I have found in the 9i release notes a notation that “this behavior will be disallowed in an upcoming release”. Our application has many procedures upgraded from 8i to 9i, and now to 10g. The code that was working in both 8i and 9i is now raising the error "variable not in select list" in 10g.

I have been able to duplicate the issue, comparing 9i to 10g, and we now understand the issue and the areas that need review and correction.

Thanks for the responses.