How to declare SQLCA.SQLERRD?

Question

I am trying to insert records into a table using insert .... select ... stmt in the oracle stored procedure. How to find the number of records inserted using SQLERRD?

recIn number; Insert into t1 ....Select.....; recIn := SQLCA.SQLERRD(3); .... .... PLS-00201: identifier 'SQLCA.SQLERRD' must be declared 

is an error message. How do you declare SQLCA.SQLERRD?

 using Oracle 9.2 bcs 

Answer 1

Are you using PL / SQL? Or are you using Pro * C / C ++? SQLCA.SQLERRD will be defined in Pro * C / C ++, it will not be defined in PL / SQL. Since you did not mark the question for Pro * C, I assume that you are just using PL / SQL.

In PL / SQL, you simply reference SQL%ROWCOUNT after you run the SQL query to get the number of rows. Sort of

 DECLARE l_num_rows INTEGER; BEGIN INSERT INTO t1( <<list of columns>> ) SELECT <<list of columns>> FROM <<some tables>> WHERE <<some predicates>> l_num_rows := sql%rowcount; dbms_output.put_line( 'The statement inserted ' || l_num_rows || ' rows.'; END;