PL/SQL on Oracle 11g code is when executing stored procedure

Question

What I am trying to do here is not run an insert code over and over, which is why I decided to create a stored procedure. Below is the script for the stored procedure and it created successfully, but when I execute the stored procedure "BEGIN SP_INSERT_PMC_UPDATE_DP; END;" I receive an error message "*wrong number or types of arguments in call to SP_INSERT_PMC_UPDATE_DP*"

My frame of thinking and sp code on this is:

1- I check to see if there are records in the PMC_UPDATE_DP and place that value into RECORD_COUNT.

2- Now if RECORD_COUNT is greater than zero I want to clear out all the records from the PMC_UPDATE_DP table.

3- If the RECORD_COUNT is equal to zero, then I want to insert data from the EXTERNAL_PMC_UPDATE_FD table to PMC_UPDATE_DP.

    CREATE OR REPLACE PROCEDURE PMC.SP_INSERT_PMC_UPDATE_DP 
    (RECORD_COUNT OUT NUMBER) --Is my issue here???
    IS

    BEGIN   
--Returns the total records and adds the value to RECORD_COUNT variable.
        SELECT COUNT(*) 
            INTO RECORD_COUNT
            FROM PMC.PMC_UPDATE_DP; 

--Condition to see if RECORD_COUNT is greater than zero and dumps data to clear out PMC.PMC_UPDATE_DP table.
        IF RECORD_COUNT > 0 THEN 
            EXECUTE IMMEDIATE 'TRUNCATE TABLE PMC.PMC_UPDATE_DP';
        END IF; 

--Condition to see if RECORD_COUNT equals zero. If true insert data into PMC_UPDATE_DP table from the PMC.EXTERNAL_PMC_UPDATE_FD table.
        IF RECORD_COUNT = 0 THEN 
            INSERT INTO PMC.PMC_UPDATE_DP
            (       JOB_ID,               
            CONTROL_ID,           
            ACCT_NO,              
            CALC_DIVIDEND,        
            CERT_NEW_SHARES,      
            CALC_CASH_DISBURSMENT,
            DECEASED,             
            STATUS,               
            ALPHA_SP1,            
            ALPHA_SP2,            
            ALPHA_SP3,            
            ALPHA_SP4,            
            NUM_SP1,              
            NUM_SP2,              
            NUM_SP3,              
            NUM_SP4,              
            DONT_CALL,            
            DONT_MAIL            
            )
            SELECT JOB_ID,               
            CONTROL_ID,           
            ACCT_NO,              
            CALC_DIVIDEND,        
            CERT_NEW_SHARES,      
            CALC_CASH_DISBURSMENT,
            DECEASED,             
            STATUS,               
            ALPHA_SP1,            
            ALPHA_SP2,            
            ALPHA_SP3,            
            ALPHA_SP4,            
            NUM_SP1,              
            NUM_SP2,              
            NUM_SP3,              
            NUM_SP4,              
            DONT_CALL,            
            DONT_MAIL
            FROM PMC.EXTERNAL_PMC_UPDATE_FD
            COMMIT;
        END IF;
    END;

Justin Cave Justin Cave · Accepted Answer · 2013-10-02T00:37:32

If you want to call a procedure with an OUT parameter, you'd need to pass in a local variable so Oracle has something to hold the output.

DECLARE 
  l_record_count pls_integer;
BEGIN
  PMC.SP_INSERT_PMC_UPDATE_DP ( l_record_count );

  -- Do something with l_record_count, probably not just calling dbms_output
  -- dbms_output.put_line( 'Record count = ' || l_record_count );
END;

If you are not planning on doing anything with the ouput, perhaps you really don't want the procedure to have an OUT parameter. Perhaps you just want to declare record_count as a local variable.

PL/SQL on Oracle 11g code is when executing stored procedure

1 Answers