SP2-0734

We need more information.

It doesn't look like you are executing a stored procedure.  It looks like you are doing a copy/paste into sqlplus or executing a script in sqlplus?

Look around the lines that start with dbms_stats for a syntax error?

The code would help.  If it is huge, PLEASE use a code block.

COMMIT;
   
   --
   DBMS_OUTPUT.PUT_LINE ('v_load_record_count = ' || v_load_record_count);

   IF v_info_file_count <> v_load_stagerecord_count
   THEN
      --Turn off the structure load status - SH
      TURN_OFF_STRUCTURE_LOAD_STATUS (v_info_itc, 'test');
      RAISE e_invalid_record_count;
   END IF;
EXCEPTION
   WHEN e_invalid_record_count
   THEN
      RAISE_APPLICATION_ERROR (
         -20001,
         'ERROR: The INFO FILE and TEMP TABLE counts do not match!!');

      --
      CLOSE tc_STG_BENEFIT;

There is no DBMS_STATS call there so the error must be elsewhere.

You also didn't explain what it is you are doing.  The SP2 error is typically a sqlplus error.

There are many sqls i am loading stage table and then updating control table and then updating some date columns

Again, not enough information.

Is ALL the code a stored procedure, plsql/block, straight SQL, what???

The error is around at least two DBMS_STATS calls.

Without the code, we cannot tell you EXACTLY what the error is.

My "guess" is you are trying to execute dbms_stats OUTSIDE of a PL/SQ block.  inside sqlplus and outside of a PL/SQL block, you need exec.


This will work:
begin
...
dbms_stats.something;
end;
/


At the sqlplus prompt:
SQL> dbms_stats.something;

will generate an error.

You need to put the dbms_stats calls inside some pl/sql or add EXEC to it.

SQL>  exec dbms_stats.something;

WHERE something is the rest of the actual code you are running.

How huge is your procedure? What does the following produce?
Select line from user_source
where name = "YOURPROCEDURENAME"
and instr(upper(text),'dbms_stats') > 0

To help you start to debug, this will give you the lines that are causing the error. From there you can select text from user_source where lines are plus or minus some number (e.g.50) from the lines returned in the above query. Those 100 lines of text could be examined to insure the dbms_stats call is either in a pl/sql block, indicates an exec dbms_stats call, or, if neither, provides a better example of where the problem is for the experts to review.

>>What does the following produce?

I don't think it is stored code.  I'm not sure how you get an SP2- error inside a stored procedure/function.

If you test the above...with the blank line, it raises similar error.
Remove the blank line and it works.

alternatively, within the sql*plus session try:
SQL>  set sqlblanklines on

this should cause sqlplus to ignore the blank lines...and hopefully stop erroring in this fashion.

will test

So...what's the verdict?

Sorry but I don't believe it is a blank line in the select since it involves DMBS_STATS.

>>Was able to reproduce error condition

You didn't reproduce the original error:

SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored.

I was able to reproduce the sp error. Not specifically for DBMS stats.

@sam2929,
Can you please post a relevant segment of your code (in a code block) that contains "dbms_stats" so we can possibly find the cause of the error message?

>>I was able to reproduce the sp error. Not specifically for DBMS stats.

There are several ways to produce a SP2-0734.  Accepting the blank line as a solution doesn't match with the question asked.

For example (as I mentioned above but without the example):
SQL> dbms_stats.gather_schema_stats('SCOTT');
SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored.

No blanks lines involved.

i will get back on this