Q
Problem solve Get help with specific problems with your technologies, process and projects.

PLS errors after adding columns to table

I altered a table and added two columns. I ran reference type as tablename%rowtype in stored procedures. I got two error messages: "PLS-00302: component 'NUMLABEL' must be declared" and "PLS-00417: unable to resolve 'NUMLABEL' as a column."

I altered a table and added two columns. I ran reference type as tablename%rowtype in stored procedures. I got two error messages when I compiled the procedure: "PLS-00302: component 'NUMLABEL' must be declared" (this is for parameter declare) and "PLS-00417: unable to resolve 'NUMLABEL' as a column." This is for reference_rec.table%rowtype. I could not reference new column as reference_rec.newcolumn.

I checked table by desc command. Two new columns exist in the table in the database.

If I create a table with these new columns, it works fine. But I have the problem after adding new columns into the existing table.

This issue is only in Oracle8i. It works in Oracle9i and 10g. How do I find a solution? Thanks for any help!

CREATE OR REPLACE Procedure KK2
   ( as_NumLabel         in tableA.NUMLABEL%type,
     as_TypeLabel     in tableA.TYPELABEL%type
    )
as
BEGIN
dbms_output.put_line('OK' );
dbms_output.put_line(as_NumLabel );
dbms_output.put_line(as_TypeLabel );

end kk2;
In Oracle 8i, you cannot use the %TYPE declaration in the procedure's parameter list. I could not find this fact documented anywhere in the Oracle 8i documentation. But the Oracle 9i documentation is the first version that I can find that does explicitly state that you can use %TYPE in your parameter declarations. I verified that I could not use %TYPE in my parameter declarations in Oracle 8i but could in 9i. So it looks like what you want to do is a new feature in Oracle 9i.

Dig Deeper on Oracle stored procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close