Hot answers tagged sqlplus
13
Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon
Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.
12
Several ways.
You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):
export EDITOR=vi
Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.
If you don't wish to set an environment ...
10
but for example no "arrow-up" key for the previous history entry is available.
You're talking about sqlplusw the "GUI" tool - the oldschool sqlplus does support
arrow-up-bring-previous-statements feature.
What is a good replacement / extension for sqlplus
Oracle's SQL Developer, a free GUI-based tool, is pretty neat.
If you're a vim addict, ...
9
The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot. The following will work in SQL*Plus:
REM comment
-- comment
/* comment */
begin
DBMS_OUTPUT.PUT_LINE('Test'); --comment
DBMS_OUTPUT.PUT_LINE('Test'); /* comment */
end;
/
These will not:
begin
DBMS_OUTPUT.PUT_LINE('Test'); REM comment
end;
/
begin
...
8
AVG and other aggregate functions work on sets of data. The WHERE cause does not have access to the entire set, only to data for the row it is operating on. If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.
...
8
you need a / after a PL/SQL block in SQL*Plus:
SQL> begin
2 null;
3 end;
4 -- here you need a /
5 /
PL/SQL procedure successfully completed
This is so SQL*Plus knows you are done with your statement (which could include intermediate, non-terminating ;).
SQL types may include PL/SQL code, therefore the SQL*Plus devs decided that you ...
8
Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.
By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:
SQL - Structured ...
7
If it is wrapped in
BEGIN ... END
DECLARE ... END
CREATE OR REPLACE ... END
Is a one-liner prefixed EXECUTE
Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more ...
7
When your OS user is from a DBA group, you can connect AS SYSDBA with OS authentication:
Two special operating system groups control database administrator
connections when using operating system authentication. These groups
are generically referred to as OSDBA and OSOPER. The groups are
created and assigned specific names as part of the database
...
6
sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))
Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like
sqlplus ...
6
Metalink bug 9103343 states:
This is expected behaviour. SQL*Plus is written in oci and oci has a
default prefetch value of 1 row. However prefetch upon a fetch (as
opposed to upon an execute) only takes place when you are not
performing an array fetch, so when arraysize is 1. Regardless of
arraysize the first fetch in the trace is always ...
6
The second statement is creating an object table.
It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...
6
There is nothing like PostgreSQL's set search_path in Oracle.
The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ...
CREATE OR REPLACE TRIGGER LOGON_TRG
AFTER LOGON ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
EXCEPTION
when others
then ...
5
When you're entering an SQL statement into SQL*Plus, it needs to know when you're done with it, especially if the command spans multple rows. Therefore, it requires a terminal character character which can be set with the set sqlterminator. By default, this character is the semicolon:
SQL> select *
2 from
3 dual;
D
-
X
Now, changing this ...
4
For GUI, I use SQL Developer from Oracle. It has just about everything you might need, including prebuilt SQL for ASH/AWR reports, sessions, memory, and other management. It's helpful to me since my official role isn't DBA but I do a lot of that work.
SQL Plus has an equivalent to the up arrow: list or l. It's not as good, just showing the last command ...
4
An alternative technique may be to use an external table. There is no need to take your CSV (or whatever) file and load it in a separate step. Simply declare your external table in the correct format (this is almost identical to a SQL*Loader parfile, but wrapped in a CREATE TABLE statement) and you can issue a SELECT directly against it, once the file is in ...
4
Is there a demarcation between SQL and PL/SQL?
SQL is a standard*.
PL/SQL is a vendor extension to the SQL standard*.
*SQL is a language that has an explicit grammar and rules for how that grammar should be implemented, and is but is not a standard per-se. However, since there is a language specification, that everyone can agree on, then anything ...
4
SELECT utc.column_name "Name"
, utc.nullable "Null?"
, concat(concat(concat(utc.data_type,'('),utc.data_length),')') "Type"
, ui.uniqueness
FROM user_tab_columns utc
LEFT JOIN user_ind_columns uic ON uic.table_name = utc.table_name
AND utc.column_name = uic.column_name
LEFT JOIN user_indexes ui ON ui.table_name = utc.table_name
AND ...
4
Try it like this:
var retCode number
exec :retCode := 30;
exit :retCode
(See http://www.orafaq.com/forum/mv/msg/80574/233106/0/#msg_233106)
Bottom line is that retCode must be a variable defined in SQL*PLUS's scope. Your DECLARE is inside a code block, and SQL*PLUS can't see into it.
4
Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order.
Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index ...
3
Benoit, you actually had a partial answer, everything you said was correct, but after next reboot, the account locked. After several hours of more research, the following issue was discovered, with the appropriate resolution.
Enterprise Manager had the default SYSMAN password stored for its credentials, and was trying to lock in with that password in rapid ...
3
If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:
variable p_owner varchar2(30);
variable p_column varchar2(30);
set verify off
set feedback off
begin
:p_owner := '&1';
:P_column := '&2';
end;
/
set feedback on
...
3
Well, a parameterized query is going to do better, hands down. The way we used to do something similar to this was by using an anonymous PL/SQL block that would use UTLFILE to open and read the parameters, feeding them to query in the block, and writing the output back to the file system.
YMMV of course.
3
since you mentioned ssh; you might want to take a look at DbVisualizer It has integrated ssh tunneling, is running on multiple platforms and supports about every database that has a jdbc driver available. The developer is also very responsive and if you happen to be able to find a not supported database, they will add it with pleasure. (and quick).
One of ...
3
This seems to be a quirk of SQL*Plus and arraysize rather than pipelined functions - the following demonstrates the same effect:
create or replace function pause return integer as
begin
dbms_lock.sleep(2);
return 1;
end;
/
select pause from dual connect by level<10;
You can (sort of) workaround the issue by selecting the rows twice and discarding ...
3
Agree with first responder. The only way to do what you want, and not 100% sure it is possible, would be to have very high level permissions on all servers, or have special permission settings. Any system admin would be justified in asking for any order to set something like this up in writing. The same goes for the DBAs for the DBs you want to run "sql ...
3
Your trigger doesn't need the CALL keyword.
create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/
Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ...
3
So you don't need to specify the full path when you run sqlplus.exe:
Set your %TNS_ADMIN% variable to the full path of the relevant folder (C:\programs\Oracle\Ora11g\TNS_ADMIN ?). Instructions for doing so are a google away. At the same time, make sure %ORACLE_HOME% is set to C:\programs\Oracle\Ora11g and that your %PATH% has C:\programs\Oracle\Ora11g\bin ...
2
SQL*Plus has a role for Oracle Database Administration, very similar like vi has in the UNIX/Linux OS world. It is not everybodys darling, but it will always be there and it will always work. Better get familiar with it if you are a DBA.
Developers can afford to strive for something more comfortable like SQL Developer :-)
2
REM is supported due to being the way MS BATCH files are commented, and this tool being used with automation environments.
-- is supported due to being part of the SQL standard. ( http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt page 83 cf <comment introducer> ::= <minus sign><minus sign>[<minus sign>...] )
Only top voted, non community-wiki answers of a minimum length are eligible
