That means your query is still incomplete and need more input from your side.
In your case, its probably because you didn't terminate the query with a semicolon.
When you get "2", type the semicolon ";" and enter.
Please also make sure, you terminate all your future queries with ";".
Hi,
I want to write a script, in which I will connect to sqlplus and do a quary and then exit
sqlplus user/pwd@database
select count(*) from table
exit.
and write the result in a log file.
How to write it ?
Many thanks before. (1 Reply)
I'm writting a shell script and at the begining I login to sqlplus by
sqlplus -l user_name/password@instance
what I would like is to check if the database is down or not , and if the database has started moved to the next step else sleep for a certain time and then check again .
I know how to... (4 Replies)
hi,
I have this basic query.
I have created a new user on unix.
I have given home directory and permission through chmod to create directory stucture.
Now need to connect sqlplus. What permissions should we give, so that this works?
Any help is appreciated.
Thanks,
Neha (1 Reply)
I want to use Perl to connect to a remote Oracle DB
I have no oracle installation on my server (and dont plan on installing one) I am using solaris 9 on x86 server.
Is this possible?
I basically want to run some basic sql queries on the remote oracle db which I have access to using perl on my... (0 Replies)
a job extracts orcle data into unix as flat file.
a single record breaks into two record in unix flat file. This is the case only for 6 records out of 60 lack records.
(its not single record in two line. but its single record into record. ie., \n come into picture)
can you tell me what... (6 Replies)
Hi,
I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following:
1. Connect to oracle database hosted on a different server
2. fire a query on the oracle db
3. store the output in a variable
4. use this variable for further logic... (1 Reply)
Hi,
I am very new to shell scripting and trying to write a simple shell script in which i am trying to achieve the following:
1. Connect to oracle database hosted on a different server
2. fire a query on the oracle db
3. store the output in a variable
4. use this variable for further logic... (26 Replies)
I need to connect my Oracle 11g DB from shell script with 'sysdba' permissions. To do this I have to switch user from 'root' to 'oracle'.
I've tried the following with no success.
su - oracle -c "<< EOF1
sqlplus -s "/ as sysdba" << EOF2
whenever sqlerror exit sql.sqlcode;... (2 Replies)
Hi,
I have been trying to connect to sqlplus the same way I used to do in my earlier company but I get these error messages , please suggest way out -
user name - xyzuser
schema name - xyzschema
$ sqlplus xyzuser@xyzschema
ksh: sqlplus: not found.
$ sqlplus -s xyzuser@xyzschema... (5 Replies)
Good evening
I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus.
I tried to find out the user/passwd and service name by env variable and all Ive got is this:
ORACLE_SID_REPCOL=SCL_REPCOL
ORACLE_SID=xmeta
ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
Discussion started by: alexcol
2 Replies
LEARN ABOUT REDHAT
prepare
PREPARE(7) SQL Commands PREPARE(7)NAME
PREPARE - create a prepared query
SYNOPSIS
PREPARE plan_name [ (datatype [, ...] ) ] AS query
INPUTS
plan_name
An arbitrary name given to this particular prepared query. It must be unique within a single session, and is used to execute or
remove a previously prepared query.
datatype
The data-type of a parameter to the prepared query. To refer to the parameters in the prepared query itself, use $1, $2, etc.
OUTPUTS
PREPARE
The query has been prepared successfully.
DESCRIPTION
PREPARE creates a prepared query. A prepared query is a server-side object that can be used to optimize performance. When the PREPARE
statement is executed, the specified query is parsed, rewritten, and planned. When a subsequent EXECUTE statement is issued, the prepared
query need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the query is
executed.
Prepared queries can take parameters: values that are substituted into the query when it is executed. To specify the parameters to a pre-
pared query, include a list of data-types with the PREPARE statement. In the query itself, you can refer to the parameters by position
using $1, $2, etc. When executing the query, specify the actual values for these parameters in the EXECUTE statement -- refer to EXECUTE
[execute(7)] for more information.
Prepared queries are stored locally (in the current backend), and only exist for the duration of the current database session. When the
client exits, the prepared query is forgotten, and so it must be re-created before being used again. This also means that a single prepared
query cannot be used by multiple simultaneous database clients; however, each client can create their own prepared query to use.
Prepared queries have the largest performance advantage when a single backend is being used to execute a large number of similar queries.
The performance difference will be particularly significant if the queries are complex to plan or rewrite. For example, if the query
involves a join of many tables or requires the application of several rules. If the query is relatively simple to plan and rewrite but rel-
atively expensive to execute, the performance advantage of prepared queries will be less noticeable.
NOTES
In some situations, the query plan produced by PostgreSQL for a prepared query may be inferior to the plan produced if the query were sub-
mitted and executed normally. This is because when the query is planned (and the optimizer attempts to determine the optimal query plan),
the actual values of any parameters specified in the query are unavailable. PostgreSQL collects statistics on the distribution of data in
the table, and can use constant values in a query to make guesses about the likely result of executing the query. Since this data is
unavailable when planning prepared queries with parameters, the chosen plan may be sub-optimal.
For more information on query planning and the statistics collected by PostgreSQL for query optimization purposes, see the ANALYZE [ana-
lyze(7)] documentation.
COMPATIBILITY
SQL92
SQL92 includes a PREPARE statement, but it is only for use in embedded SQL clients. The PREPARE statement implemented by PostgreSQL also
uses a somewhat different syntax.
SQL - Language Statements 2002-11-22 PREPARE(7)