Hello,
I am trying to execute a database packaged procedure.
The database job is :
BEGIN PKG_REPORTS.HEAD_DEALER_COUNT; END;

It runs on a remote agent.
It fails with an error, when I look at the log viewer on the remote agent I get :
Remote job executeion error. Exit code -1. Dateabase error occurred. ORA-06550: line 1, column 42: PLS-00103: encountered the symbol "" The symbol "" was ignored.

I don't understand this. I tried originally to run the procedure just using the EXECUTE command but that doesn't work and the scheduler hangs (I have posted on this before), so I tried wrapping Begin and End around it.
If I run the procedure from SQLPLUS it works fine. So there is nothing wrong with the code in the procedure itself.

The problem seems to happen if you have anything after the first line.
eg
If i have :
Begin PKG_REPORTS.HEAD_DEALER_COUNT; End;

And nothing else in the script it executes OK.
But I really want this job to execute 2 database procedures, so if I try :

Begin PKG_REPORTS.HEAD_DEALER_COUNT; End;
Begin PKG_REPORTS.BRANCH_DEALER_COUNT; End;

It fails with the error. Why is this ? Is it a bug ? I want to be able to execute 2 database procedures.
Surely I don;t have to have one job for each one do I ?

The last ";" is not needed.

: Hello,
: I am trying to execute a database packaged procedure.
: The database job is : BEGIN PKG_REPORTS.HEAD_DEALER_COUNT; END;

: It runs on a remote agent.
: It fails with an error, when I look at the log viewer on the remote agent I
: get : Remote job executeion error. Exit code -1. Dateabase error occurred.
: ORA-06550: line 1, column 42: PLS-00103: encountered the symbol
: "" The symbol "" was ignored.

: I don't understand this. I tried originally to run the procedure just using
: the EXECUTE command but that doesn't work and the scheduler hangs (I have
: posted on this before), so I tried wrapping Begin and End around it.
: If I run the procedure from SQLPLUS it works fine. So there is nothing wrong
: with the code in the procedure itself.

BEGIN
PKG_REPORTS.HEAD_DEALER_COUNT;
PKG_REPORTS.BRANCH_DEALER_COUNT;
END

: The problem seems to happen if you have anything after the first line.
: eg
: If i have : Begin PKG_REPORTS.HEAD_DEALER_COUNT; End;

: And nothing else in the script it executes OK.
: But I really want this job to execute 2 database procedures, so if I try :
: Begin PKG_REPORTS.HEAD_DEALER_COUNT; End;
: Begin PKG_REPORTS.BRANCH_DEALER_COUNT; End;

: It fails with the error. Why is this ? Is it a bug ? I want to be able to
: execute 2 database procedures.
: Surely I don;t have to have one job for each one do I ?

: BEGIN
: PKG_REPORTS.HEAD_DEALER_COUNT;
: PKG_REPORTS.BRANCH_DEALER_COUNT;
: END
no that does not work, I get an Oracle error saying that it encountered the symbol "" when expecting a begin case declare exit etc
I presume it is because there is no semicolon on the END as Oracle expects this.

Does it work with a semicolon?

: no that does not work, I get an Oracle error saying that it encountered the
: symbol "" when expecting a begin case declare exit etc
: I presume it is because there is no semicolon on the END as Oracle expects
: this.

: Does it work with a semicolon?
no - I think it is a bug.
At the moment the only way around it that i can think of is to have 2 separate jobs which seems a waste really.
It seems that you cannot execute more than one plsql command

I have created a test sample which does not work either :

declare
l_dummy varchar2(1);
Begin
select 'X' into
l_dummy
from dual;
End;

Fails if I include or exclude a semicolon on the End statement

If I put all text on just line or paste it from an SQL editor that uses only "new-line" character instead of pairs of "carriage return" and "new line" to terminate text lines then the SQL runs fine.

And of course you can have as many PL/SQL statements in it as you want.
BTW, the ending semicolon is required.

declare l_dummy varchar2(1); Begin select 'X' into l_dummy from dual; End;

: I have created a test sample which does not work either : declare
: l_dummy varchar2(1);
: Begin
: select 'X' into
: l_dummy
: from dual;
: End;

: Fails if I include or exclude a semicolon on the End statement

so what are you saying - you must put everything on one line ?
That's not very user-friendly.
The only way i can see how to get 2 sql procedures executed is to have 2 separate jobs

I have got it to execute the 2 procedures but to get it to work I had to put everything on one line :
Begin PKG_REPORTS.HEAD_DEALER_COUNT;PKG_REPORTS.BRANCH_DEALER_COUNT; End;

Is this normal behaviour ?
That means if I wanted to execute a lot of code then it would all have to be on one line and would be difficult to debug and also to write.
It would be far easier if you could include carriage returns.
I am writing the code in the 24x7 editor.

I agree with you, in case of Oracle I think the editor should automatically do the required conversion.

: I have got it to execute the 2 procedures but to get it to work I had to put
: everything on one line : Begin
: PKG_REPORTS.HEAD_DEALER_COUNT;PKG_REPORTS.BRANCH_DEALER_COUNT; End;

: Is this normal behaviour ?
: That means if I wanted to execute a lot of code then it would all have to be
: on one line and would be difficult to debug and also to write.
: It would be far easier if you could include carriage returns.
: I am writing the code in the 24x7 editor.

: I agree with you, in case of Oracle I think the editor should automatically
: do the required conversion.

Can we add this to a wishlist for the next upgrade of 24x7 please as I use it a lot to interact with our Oracle databases