<The 9i client doesn't know 10g SQL. If you call your command from the
<9i client but wrapped in pl/sql the command will be executed in the
<10g server successfully.
SQL is not excuted by 10g engine ?
some control is done by sql*plus ?
Thanks
> <The 9i client doesn't know 10g SQL. If you call your command from the
> <9i client but wrapped in pl/sql the command will be executed in the
> <10g server successfully.
>
> SQL is not excuted by 10g engine ?
> some control is done by sql*plus ?
SQL*Plus does a very basic parse of the command to decide what it's
dealing with, e.g. a SQL*Plus command like 'show linesize' that it can
execute locally, or some SQL or PL/SQL that needs to go to the server.
The type of command affects the continuation prompt, for example. If a
command starts with BEGIN then it's PL/SQL and needs to continue until
a line consisting of a "/" or a "." regardless of semicolons, if it
starts with "SELECT" then it's SQL and ends with the first occurrence
of the currently defined sqlterminator character, and so on. If you
enter "BANANA" it gives an SP2-0042 "unknown command" error - notice
that's raised by the SQL*Plus client and has not even gone to the
server. As far as SQL*Plus 9i is concerned, 'PURGE' is in the same
category (just like 'WITH' and 'MERGE' in SQL*Plus 8i).
astalavista - 06 Oct 2008 20:39 GMT
OK thanks William
On Oct 4, 7:34 am, "astalavista" <nob...@nowhere.com> wrote:
> <The 9i client doesn't know 10g SQL. If you call your command from the
> <9i client but wrapped in pl/sql the command will be executed in the
> <10g server successfully.
>
> SQL is not excuted by 10g engine ?
> some control is done by sql*plus ?
SQL*Plus does a very basic parse of the command to decide what it's
dealing with, e.g. a SQL*Plus command like 'show linesize' that it can
execute locally, or some SQL or PL/SQL that needs to go to the server.
The type of command affects the continuation prompt, for example. If a
command starts with BEGIN then it's PL/SQL and needs to continue until
a line consisting of a "/" or a "." regardless of semicolons, if it
starts with "SELECT" then it's SQL and ends with the first occurrence
of the currently defined sqlterminator character, and so on. If you
enter "BANANA" it gives an SP2-0042 "unknown command" error - notice
that's raised by the SQL*Plus client and has not even gone to the
server. As far as SQL*Plus 9i is concerned, 'PURGE' is in the same
category (just like 'WITH' and 'MERGE' in SQL*Plus 8i).
> <The 9i client doesn't know 10g SQL. If you call your command from the
> <9i client but wrapped in pl/sql the command will be executed in the
[quoted text clipped - 3 lines]
> some control is done by sql*plus ?
> Thanks
The client knows the SQL keywords and syntax - to enable SQL
validation checks to take place without the expense of a trip to the
server. In your case, it only knows about 9i SQL. To execute the 10G
purge command you will need to send your statement as dynamic SQL
( purge is DDL) wrapped in PL/SQL. The PL/SQL block will be validated
in the same way - keyword and syntax will be checked. However, the
dynamic component will not be checked until execution time (after the
PL/SQL has been shipped to the server and executed - on the server 10G
SQL will be understood).
Try an anonymous PL/SQL block, with an "execute immediate" to make the
call at runtime.
Mathew Butler
astalavista - 07 Oct 2008 07:37 GMT
OK thanks
On Oct 4, 7:34 am, "astalavista" <nob...@nowhere.com> wrote:
> <The 9i client doesn't know 10g SQL. If you call your command from the
> <9i client but wrapped in pl/sql the command will be executed in the
[quoted text clipped - 3 lines]
> some control is done by sql*plus ?
> Thanks
The client knows the SQL keywords and syntax - to enable SQL
validation checks to take place without the expense of a trip to the
server. In your case, it only knows about 9i SQL. To execute the 10G
purge command you will need to send your statement as dynamic SQL
( purge is DDL) wrapped in PL/SQL. The PL/SQL block will be validated
in the same way - keyword and syntax will be checked. However, the
dynamic component will not be checked until execution time (after the
PL/SQL has been shipped to the server and executed - on the server 10G
SQL will be understood).
Try an anonymous PL/SQL block, with an "execute immediate" to make the
call at runtime.
Mathew Butler
> <The 9i client doesn't know 10g SQL. If you call your command from the
> <9i client but wrapped in pl/sql the command will be executed in the
[quoted text clipped - 3 lines]
> some control is done by sql*plus ?
> Thanks
You may want to try
BEGIN
EXECUTE IMMEDIATE 'purge dba_recyclebin';
END;
/
So that the actual syntax will pass SQL*Plus and turn it over to the
database (10g)