Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Oracle / Oracle Server / October 2008

Tip: Looking for answers? Try searching our database.

Purge command

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
astalavista - 01 Oct 2008 20:29 GMT
Hi,

when I am connected to a 10.20.4 database
with a 9i client, the purge command doesn't work:

> purge dba_recyclebin;
SP2-0734: unknown command beginning "purge dba_..." - rest of line ignored.

Why ?
The documentation says it is a SQL command

Thanks in advance
Michel Cadot - 01 Oct 2008 21:06 GMT
| Hi,
|
[quoted text clipped - 8 lines]
|
| Thanks in advance

It is not a 9i SQL command and so is not known for 9i SQL*Plus.

Regards
Michel
astalavista - 04 Oct 2008 07:35 GMT
> It is not a 9i SQL command and so is not known for 9i SQL*Plus.
>
> Regards
> Michel

OK thanks Michel
mathewbutler - 02 Oct 2008 23:10 GMT
> Hi,
>
[quoted text clipped - 9 lines]
>
> Thanks in advance

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.

Mathew butler
astalavista - 04 Oct 2008 07:34 GMT
<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
William Robertson - 06 Oct 2008 17:40 GMT
> <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).
mathewbutler - 06 Oct 2008 20:47 GMT
> <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
Vince - 07 Oct 2008 15:28 GMT
> <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)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.