Home » Other » Client Tools » PLSQL command line help (10g 10.2.0.1)
PLSQL command line help [message #537586] Thu, 29 December 2011 10:31 Go to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
I've just installed 10g and created a database(s) with dbca (and as part of the install).

When connecting as system to one of the DBs I created, I can only see plsql help topics (i.e. connect, startup, etc), and not the plsql (select, create, alter....)

Am I missing something? I've run the helpins script. which did not add anything, however it does report an error, evidently when dropping a temporary view:

End of the 'helpins' command output.

Quote:

<snip>

Commit complete.


Table altered.

DROP VIEW HELP_TEMP_VIEW
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


58 rows created.


Commit complete.


View dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
bash-3.2$ exit

Re: PLSQL command line help [message #537588 is a reply to message #537586] Thu, 29 December 2011 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
there is no built in help for SQL statements

you need to Read The Fine Manual in URL below

http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm
Re: PLSQL command line help [message #537597 is a reply to message #537586] Thu, 29 December 2011 12:31 Go to previous messageGo to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
I have used other systems (and am doing it right now), where I can type "help select" and see a description of the command.

Those systems just have the client installed, where the one I'm using has the entire oracle installation. I believe that is the only difference.




Re: PLSQL command line help [message #537604 is a reply to message #537597] Thu, 29 December 2011 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only thing you have to do to get this help is to run "$ORACLE_HOME/hlpbld helpus.sql" from SYSTEM account.

Regards
Michel
Re: PLSQL command line help [message #537723 is a reply to message #537586] Fri, 30 December 2011 09:05 Go to previous messageGo to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
Running that script is the same as running the "helpins" shell script. I have done so and I get the same error shown in the original post.

And the sql help is still not available?

I am assuming (but not sure) that the error during the script is the problem??

Re: PLSQL command line help [message #537724 is a reply to message #537723] Fri, 30 December 2011 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I've run the helpins script
For what it is worth, helpins file does not exits for V11.2 Oracle RDBMS
Re: PLSQL command line help [message #537725 is a reply to message #537723] Fri, 30 December 2011 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Running that script is the same as running the "helpins" shell script


If you know everything then you don't need our help.

Do it again and copy and paste what you EXACLTY do and get.

Regards
Michel
Re: PLSQL command line help [message #537727 is a reply to message #537725] Fri, 30 December 2011 10:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
When Oracle installation scripts involve creating a table or a view, they routinely first drop that table or view, in case it is a re-installation. If it is not a re-installation and you are running it for the first time, then it shows an error because it has attempted to drop a table or view that does not exist. So, you can ignore that error produced by running the script. If you were to re-run the script, then the table or view would be dropped before being re-created and would not display an error.

The script should be run from the system schema.

This help feature is a SQL*PLus command line help feature, not a SQL command line help feature. So, you get the SQL*Plus commands:

SYSTEM@orcl_11gR2> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW


SYSTEM@orcl_11gR2>


SELECT is a SQL command, not a SQL*Plus command, so it is not included in the SQL*Plus command line help utility.

If you are really seeing help for "HELP SELECT" on another system, please post a copy and paste with accompanying material that proves that. Is this an Oracle system or some other system? Many other systems have such help, but, as far as I know Oracle does not. If you are seeing such things on an Oracle system, then it may be some utility that has been installed, but does not come with Oracle.
Re: PLSQL command line help [message #538054 is a reply to message #537727] Tue, 03 January 2012 13:20 Go to previous messageGo to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
The help index on the system I installed comes up as described by Barbara.

Logging into another system I get the following, (see "help index" and "help select").
I don't have direct access to the dba's who configured the system and assumed that getting this behavior was common and not getting it was a problem.

That is evidently not the case.

This seems very useful to me (I prefer the command line whenever possible), and when I find out how this was done, will post the answer here.


===============================================================

Script started on Tue Jan 03 13:04:10 2012
myaccount@somebox[1]: sqlplus xxxxxxxx/xxxxxxxxx

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 3 13:04:33 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

sql> help index


Index


Enter help, a space and part or all of the help topic you want:



%FOUND Attribute %ISOPEN Attribute %NOTFOUND Attribute
%ROWCOUNT Attribute %ROWTYPE Attribute %TYPE Attribute
/ @ @@
ABS ACCEPT ADD_MONTHS
ALTER CLUSTER ALTER DATABASE ALTER FUNCTION
ALTER INDEX ALTER PACKAGE ALTER PROCEDURE
ALTER PROFILE ALTER RESOURCE COST ALTER ROLE
ALTER ROLLBACK SEGMENT ALTER SEQUENCE ALTER SESSION
ALTER SNAPSHOT ALTER SNAPSHOT LOG ALTER SYSTEM
ALTER TABLE ALTER TABLESPACE ALTER TRIGGER
ALTER USER ALTER VIEW ANALYZE
AND OPERATOR ANSI, DB2, AND SQL/DS APPEND
DATATYPES
ARCHIVE LOG ARITHMETIC OPERATORS ASCII
AUDIT (SQL Statements) AUDIT (Schema Objects) AVG
Actual vs Formal Aliasing Assignments
Parameters
BNF Syntax BREAK BTITLE
Block Label Boolean Expressions Buffer
Built-in Functions CEIL CHANGE
CHAR DATATYPE CHARACTER DATATYPES CHARACTER OPERATORS
.
.
. <snipped>
.
Reraising an Exception Reserved Words Reserved Words (PL/SQL)
SAVE SAVEPOINT SELECT
SET SET OPERATORS SET ROLE
SET TRANSACTION SHOW SIGN
SIN SINGLE ROW FUNCTIONS SINH
SOUNDEX SPOOL SQL FUNCTIONS
SQL Support SQLPLUS SQRT
START STDDEV STORAGE
SUBSTR SUBSTRB SUM
SYSDATE Scope and Visibility Stored Subprograms
Structure Theorem Subprograms Substitution
Subtypes Supplying the Correct Syntax Notation
Format
TAN TANH TEXT
TIMING TO_CHAR (date TO_CHAR (label
conversion) conversion)
TO_CHAR (number TO_DATE TO_LABEL
conversion)
TO_MULTI_BYTE TO_NUMBER TO_SINGLE_BYTE
TRANSLATE TRUNC (DATE) TRUNC (NUMBER)
TRUNCATE TTITLE Transaction Processing
UID UNDEFINE UNION ALL OPERATOR
UNION OPERATOR UPDATE UPPER
USER USERENV Unhandled Exceptions
Using COMMIT Using DDL Using LOCK TABLE
Using ROLLBACK Using SAVEPOINT Using SET TRANSACTION
Using SQLCODE and VARCHAR DATATYPE VARCHAR2 DATATYPE
SQLERRM
VARIABLE VARIANCE VSIZE
Variables Variables and Constants WHENEVER OSERROR
WHENEVER SQLERROR


sql> help select
SELECT command

PURPOSE:
To retrieve data from one or more tables, views, or snapshots.

SYNTAX:

SELECT [DISTINCT | ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ]
[, { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ] ] ... }
FROM [schema.]{table | view | snapshot}[@dblink] [t_alias]
[, [schema.]{table | view | snapshot}[@dblink] [t_alias] ] ...
[WHERE condition ]
[ [START WITH condition] CONNECT BY condition]
[GROUP BY expr [, expr] ... [HAVING condition] ]
[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]
[ORDER BY {expr|position} [ASC | DESC]
[, {expr|position} [ASC | DESC]] ...]
[FOR UPDATE [OF [[schema.]{table | view}.]column
[, [[schema.]{table | view}.]column] ...] [NOWAIT] ]

where:

DISTINCT
returns only one copy of each set of duplicate rows selected.
Duplicate rows are those with matching values for each expression in
the select list.

ALL
returns all rows selected, including all copies of duplicates.

The default is ALL.

*
selects all columns from all tables, views, or snapshots listed in
the FROM clause.

table.*
view.*
snapshot.*
selects all columns from the specified table, view, or snapshot.
You can use the schema qualifier to select from a table, view, or
snapshot in a schema other than your own.

If you are using Trusted Oracle, the * does not select the ROWLABEL
column. To select this column, you must explicitly specify it in
the select list.

expr
selects an expression, usually based on columns values, from one of
the tables, views, or snapshots in the FROM clause. A column name
in this list can only contain be qualified with schema if the table,
view, or snapshot containing the column is qualified with schema in
the FROM clause.

c_alias
provides a different name for the column expression and causes the
alias to be used in the column heading. A column alias does not
affect the actual name of the column. Column aliases can be
referenced in the ORDER BY clause but in no other clauses in a
statement.

schema
is the schema containing the selected table, view, or snapshot. If
you omit schema, Oracle assumes the table, view, or snapshot is in
your own schema.

table
view
snapshot
is the name of a table, view, or snapshot from which data is
selected.

dblink
is complete or partial name for a database link to a remote database
where the table, view, or snapshot is located. Note that this
database need not be an Oracle7 database.

If you omit dblink, Oracle assumes that the table, view, or snapshot
is on the local database.

t_alias
provides a different name for the table, view, or snapshot for the
purpose of evaluating the query and is most often used in a
correlated query. Other references to the table, view, or snapshot
throughout the query must refer to the alias.

WHERE
restricts the rows selected to those for which the condition is
TRUE. If you omit this clause, Oracle returns all rows from the
tables, views, or snapshots in the FROM clause.

START WITH
CONNECT BY
returns rows in a hierarchical order.

GROUP BY
groups the selected rows based on the value of expr for each row and
returns a single row of summary information for each group.

HAVING
restricts the groups of rows returned to those groups for which the
specified condition is TRUE. If you omit this clause, Oracle
returns summary rows for all groups.

UNION
UNION ALL
INTERSECT
MINUS
combines the rows returned by two SELECT statement using a set
operation.

AS
can optionally precede a column alias. To comply with the ANSI SQL92
standard, column aliases must be preceded by the AS keyword.

ORDER BY
orders rows returned by the statement.
expr
orders rows based on their value for expr. The
expression is based on columns in the select list or
columns in the tables, views, or snapshots in the
FROM clause.
position
orders rows based on their value for the expression
in this position of the select list.
ASC
DESC
specifies either ascending or descending order. ASC
is the default.
The ORDER BY clause can reference column aliases defined in the
SELECT list.

FOR UPDATE
locks the selected rows.

NOWAIT
returns control to you if the SELECT statement attempts to lock a
row that is locked by another user. If you omit this clause, Oracle
waits until the row is available and then returns the results of the
SELECT statement.

PREREQUISITES:
For you to select data from a table or snapshot, the table or
snapshot must be in your own schema or you must have SELECT
privilege on the table or snapshot.

For you to select rows from the base tables of a view, the owner of
the schema containing the view must have SELECT privilege on the
base tables. Also, if the view is in a schema other than your own,
you must have SELECT privilege on the view.

The SELECT ANY TABLE system privilege also allows you to select data
from any table or any snapshot or any view's base table.

If you are using Trusted Oracle in DBMS MAC mode, your DBMS label
must dominate the creation label of each queried table, view, or
snapshot or you must have READUP system privileges.

SEE:
DELETE, UPDATE

sql> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
myaccount@somebox[1]: exit
myaccount@somebox[1]:
script done on Tue Jan 03 13:04:46 2012
Re: PLSQL command line help [message #538061 is a reply to message #538054] Tue, 03 January 2012 13:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What operating systems do the two system use? I found a few sites on the web that claim that you get the help on the SQL commands with Unix operating system. I am using Windows.
Re: PLSQL command line help [message #538075 is a reply to message #538061] Tue, 03 January 2012 14:41 Go to previous messageGo to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
Solaris on Sparc.
Re: PLSQL command line help [message #538205 is a reply to message #538075] Wed, 04 January 2012 11:27 Go to previous messageGo to next message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
Can you post the URL of those sites?

Thanks.
Re: PLSQL command line help [message #538213 is a reply to message #538205] Wed, 04 January 2012 12:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you click on the link below and scroll down to "3.3.2 Getting Help Under UNIX", you will see an example of "help select".

http://holowczak.com/oracle/sqlplus/#SECTION00053000000000000000

The above is for an outdated version. I now seem to recall back in an 8i class it being mentioned that the help for the SQL commands was removed from the SQL*Plus command line help. Are you using an old version of SQL*Plus that still has this on one system? Or has somebody found a copy of that old help file somewhere and copied it onto that system?







Re: PLSQL command line help [message #538484 is a reply to message #538213] Fri, 06 January 2012 09:15 Go to previous message
hawkrocket
Messages: 7
Registered: December 2011
Location: Kansas
Junior Member
Thanks,

"sqlplus -V" gives version 10.2.0.1.

Will post the answer when I find it.
Previous Topic: IntelliSense and Function Usage/Preview in Toad
Next Topic: unable to drop roles
Goto Forum:
  


Current Time: Thu Mar 28 17:24:29 CDT 2024