password
A reader, March 25, 2003 - 4:19 pm UTC
tom, I want to put a password on the package
so when I publish the package for some procedures I want to put the password as parameter
how can we do it ?
I have to grant execute to public for this package but one of the procedure is voletile
I want something like this
package db_util
proceudre diable_all_triggers
-------------------------------
schema_name varchar2
password varchar
when they execute from sqlplus
user should not see the password
is it possible ?
oracle version 8.1.7.0
os:linux 7.3 ( redhat)
March 25, 2003 - 8:37 pm UTC
huh? password to "what".
I would not make it password driven, if you need to protect this one procedure, maintain a table of users that is allowed to use it instead.
you cannot hide "binds"
Thanks!!
A reader, March 26, 2003 - 9:12 am UTC
I just wanted to restrict some users to execute this proc.
without any other work. because it is just a samall utility
I can't create user table in prod. system for this purpose..
I know in database I am suppose to process the data and not expect too many interface/security things.
Thanks
March 26, 2003 - 9:15 am UTC
then I would suggest you remove the procedure from this package and put it into its own package and grant execute on that to the proper people.
Thanks..
A reader, March 26, 2003 - 9:16 am UTC
I will do that !!
thanks !!
SQL*Plus Deprecated in 10i
Tim Hall, March 26, 2003 - 9:54 am UTC
Hi.
What are your thoughts about SQL*Plus being deprecated in 10i in favour of iSQL*Plus?
What's going to be the preferred way to run batch scripts that require an element of UNIX/Windows scripting in conjunction with SQL*Plus?
Cheers
Tim...
March 26, 2003 - 2:56 pm UTC
where did you read that?
SQL*Plus
Mark A. Williams, March 26, 2003 - 3:14 pm UTC
This is only for sqlplusw.exe NOT sqlplus.exe (the command-line version)... Metalink has a desupport note I think, but I can't recall for certain at the moment. There was a recent discussion on c.d.o.s as well.
- Mark
March 26, 2003 - 4:33 pm UTC
excellent -- i always hated sqlplusw.exe ;)
I've not heard anything about plus going away.
SQL*Plus
Mark A. Williams, March 26, 2003 - 3:20 pm UTC
Found it...
Here's the doc id: 171870.1
<snip>
The SQL*Plus for Windows graphical user interface (GUI) will be desupported in future releases of SQL*Plus. The browser-based iSQL*Plus interface will replace the SQL*Plus for Windows GUI. The SQL*Plus for Windows command line (DOS) interface will continue to be supported.
</snip>
So, SQL*Plus itself will not go away, just the Windows GUI version.
- Mark
RE: i always hated sqlplusw.exe...
Mark A. Williams, March 26, 2003 - 4:54 pm UTC
RE: i always hated sqlplusw.exe
yeah, but that was before you upgraded your o/s! :)
- Mark
Farewell SQL*Plus GUI
A reader, March 26, 2003 - 8:18 pm UTC
We will miss you SQL*Plus GUI,
Thanks for the "cut and pastes" which
the DOS (can I use that word on this forum ;) )
cannot...
From those afflicted with Windoze
March 27, 2003 - 7:30 am UTC
DOS did the cut and paste in a superior fashion I thought even.
I could easily copy a SQUARE of data from sqlplus in dos. I could cut the empno, ename column out -- leaving the other junk there.
A reader, May 01, 2003 - 3:01 am UTC
hello tom,
how can one get a history of executed SQL commands within SQLPLUS (windows) to reexecute them once more?
May 01, 2003 - 11:58 am UTC
sqlplus keeps only the last one. It is what it is -- a very simple, functional and invaluable command line tool.
It is all about running scripts.
A reader, May 01, 2003 - 3:23 pm UTC
hey - for some reason I can use cursor keys to get previous executed statements (in a "doskey fashion") ...
May 01, 2003 - 3:25 pm UTC
works on dos (try f7 ;)
sql history
Ashokkumar, May 01, 2003 - 5:18 pm UTC
We can use sqlplus worksheet for previous command history.
Run sqlplus in Emacs
A reader, May 01, 2003 - 5:33 pm UTC
... and you will have everything that you desire for a full featured interactive command line tool. SQL*Plus is a bit rudimentary by itself, I have to say. Lack of statement history beyond the last one is just one of its shortcomings (even mysql's doing better in this regard), and the difficulty in editing current statement is another - all solved by running it in Emacs.
And yes, sure we can all use sqlplus in a DOS window (just to get command line history with an up arrow key) from MS Windoze, and yeah, I am sure that's what Larry really wants!
A reader, May 01, 2003 - 6:04 pm UTC
Well there you go... you can cut and paste
in a DOS window...
EMACS can provide a handy interface to
SQL*Plus as mentioned above.
May 02, 2003 - 7:51 am UTC
define _editor=vi
that is my solution. whenever I find myself accidently in emacs -- I always have to open another window so I can ps and find the pid of emacs and do a kill -9 on it.
A addition
Kamal Kishore, May 01, 2003 - 9:28 pm UTC
It seems that with the next Oracle release, only the support for SQL*Plus GUI on windows will be removed. The application will still be shipped. Only that support is being removed and it is being put in the list of deprecated features (meaning do not count on it being there forever...)
how do you get sqlplus command line history
A reader, May 27, 2003 - 7:05 pm UTC
Tom, vi is for editing current statement only, can you get previous command lines with it? At the minimum, sqlplus should have a bash-like capability.
May 27, 2003 - 7:59 pm UTC
it does not.
buffer edit in sqlplus for Linux?
Sikandar Hayat, September 10, 2003 - 12:56 pm UTC
Hi TOM,
I have installed Oracle 9i R2 on RH 7.2 box and after connecting with sqlplus unable to get the buffer in the editor. In windows I was doing with "ed". With "ed" it gets the last sql into the editor but in sqlplus vi is not doing the same. Which parameter I should use to do it?
September 10, 2003 - 8:08 pm UTC
SQL> define _editor=vi
i have that in my login.sql
I already did this
Sikandar Hayat, September 11, 2003 - 9:21 am UTC
I already did this but it seem to be not working with me. I did this like,
SQL>define _editor=vi
SQL>vi
but it is doing nothing but if I use the following,
SQL>!vi it opens the vi but without buffer sql statement.
I want to know that after setting this param I have to use just vi at command line?
Sorry for so basic question as I am new on this platform.
September 11, 2003 - 9:54 am UTC
just type "edit"
SQL> define _editor=vi
SQL> edit
Very nice
Sikandar Hayat, September 12, 2003 - 12:30 am UTC
Very nice TOM it worked for me. Thanks
execute the script without supplying parameters on prompt
A reader, October 04, 2003 - 6:25 pm UTC
Tom
I want to execute a script like the following ...
@getprocedurecode pr_test
i.e. it should accept the parameter on the same line as the script name, I dont want to get prompted and then give the parameter. How to achieve this,
Right now when I execute a script it prompts me for the input parameter, and when I give it it executes.
Thanks in advance.
October 04, 2003 - 7:23 pm UTC
just use &1 in the script -- if you passed in something, it'll not prompt you
&1 Vs &tablename
A reader, October 04, 2003 - 7:45 pm UTC
&1 does not prompt, but &tablename prompts even when parameter is passed. Is &1 predefined for the purpose of not prompting i.e. is this inherently defined in sqlplus ?
October 04, 2003 - 7:59 pm UTC
&1 &2 &3 .....
are the positional parameters
you could:
define &tablename=&1
if you like.
How To change parameter value For next run...
Vikas Sharma, October 05, 2003 - 6:22 am UTC
Hi Tom,
I have sql file say abc.sql. Which has a insert statment like
insert INTO mytable values (&name,&title);
now i want to run this sql file sveral times on one sql session prompt but i want that i should be prompt for name and title every time i run.
For example on sql prompt if i do @abc.sql first time i will be prompted for name and title but if i run second time i will not.
Thanks & Regards
Vikas Sharma
October 05, 2003 - 9:27 am UTC
hmmm, you say
"...but i want that I should be prompt(ed) for the name and title every time.."
and
"i will be prompted for name and title but if I run second time I will not"
not sure WHICH behaviour you are looking for -- but we can do either or... consider:
ops$tkyte@ORA920> @test
ops$tkyte@ORA920>
ops$tkyte@ORA920> select '&name' from dual;
Enter value for name: foo
old 1: select '&name' from dual
new 1: select 'foo' from dual
'FO
---
foo
<b>Note that I used &name in the query -- single ampersand. rerunning and i get:</b>
ops$tkyte@ORA920> @test
ops$tkyte@ORA920> set echo on
ops$tkyte@ORA920>
ops$tkyte@ORA920> select '&name' from dual;
<b>Enter value for name: bar</b>
old 1: select '&name' from dual
new 1: select 'bar' from dual
'BA
---
bar
<b>Note that I got prompted again for name -- it did not retain the value. if I use &&name however:</b>
ops$tkyte@ORA920> edit test
ops$tkyte@ORA920> @test
ops$tkyte@ORA920> set echo on
ops$tkyte@ORA920>
ops$tkyte@ORA920> select '&&name' from dual;
<b>Enter value for name: foo</b>
old 1: select '&&name' from dual
new 1: select 'foo' from dual
'FO
---
foo
<b>I got prompted the first time....</b>
ops$tkyte@ORA920> @test
ops$tkyte@ORA920> set echo on
ops$tkyte@ORA920>
ops$tkyte@ORA920> select '&&name' from dual;
old 1: select '&&name' from dual
new 1: select 'foo' from dual
'FO
---
foo
ops$tkyte@ORA920>
<b>but not on subsequent executions</b>
so, use &name to get asked each time, &&name to get asked once.
How to clear the value set for &
A reader, October 05, 2003 - 10:13 pm UTC
How to clear this value after we are done with executing our select.It is stored for the entrie session of sqlplus
October 06, 2003 - 7:54 am UTC
use UNDEFINE command...
Kamal Kishore, October 05, 2003 - 11:20 pm UTC
SQL> prompt &table
Enter value for table: tab#1
tab#1
SQL> prompt &table
Enter value for table: tab#2
tab#2
SQL>
SQL>
SQL> prompt &table
Enter value for table: tab#1
tab#1
SQL>
SQL> prompt &table
Enter value for table: tab#2
tab#2
SQL>
SQL> prompt &&table
Enter value for table: tab#3
tab#3
SQL>
SQL> prompt &&table
tab#3
SQL>
SQL> undefine table
SQL>
SQL> prompt &&table
Enter value for table: tab#4
tab#4
SQL>
SQL>
& and 'Accept'
A reader, October 06, 2003 - 10:24 pm UTC
What is the difference between & and Accept in sqlplus?
Is accept also used for entering a parameter.
October 07, 2003 - 7:51 am UTC
accept is used to set an &define_name in a user friendly fashion. it lets you prompt "nicely" for a value
&define_name will just ask the user for a value if not is set and then substitute the value into the statement.
"sqlplus command line usage" page doesn't tell how to insert an ampersand
Randy Strauss, November 05, 2003 - 6:08 pm UTC
I want, in a batch file to
insert into country_info ('Antigua & Barbuda');
(this is what the customer needs.)
But & always prompts for input.
I tried && and &&& and \& and /&
and looked in AskTom and 4 books and NOTHING!
Is there a way to input special chars in general, too?
But this isn't for questions. I'd try the usual route,
but that has been saying "the backlog is too large"
for weeks... Is there a good forum where I can ask?
-Randy
Thanks, Randy
November 06, 2003 - 7:36 am UTC
set define off
i searched for
how to insert ampersand
and found it straight away....
well, about the backlog issue -- it is just a matter of timing, there is only one of me (really, i answer them all -- even these).... i've taken lots of new questions - but anyway -- other great forums:
groups.google.com -- comp.databases.oracle.* groups. internet usenet news groups.
otn.oracle.com -- discussion forums
A search of the net revealed the answer
Randy, November 05, 2003 - 6:38 pm UTC
set escape "\";
and then \& works. Thanks, -r
OK
Carl, February 19, 2004 - 12:32 am UTC
Dear Tom,
when we set formatting commands like
sql> set pagesize 0
sql> set linesize 80
sql> set null missing
in sql*plus, How to cancel them ?Is there any unset or
clear commands for them?please do reply.
February 19, 2004 - 10:45 am UTC
you just set them "back". column formatting has a "clear", but things like this -- you just set back to whatever you want them to be.
if you want to provide a script that changes these but puts them back to "whatever they were before the script was run", you can use store, eg:
ops$tkyte@ORA920PC> show pagesize
pagesize 9999 <<<<=== my default from my login.sql
ops$tkyte@ORA920PC> store set x rep <<<<=== you would do this
Wrote file x
ops$tkyte@ORA920PC> set pagesize 0 <<<<=== before this
ops$tkyte@ORA920PC> show pagesize
pagesize 0
ops$tkyte@ORA920PC> @x <<<<=== so that this
ops$tkyte@ORA920PC> show pagesize
pagesize 9999 <<<<=== will put it back.
evaluate
Phil, March 12, 2004 - 7:31 am UTC
I am sure this is a no brainer...
what I would like to do is take a .sql file with a paramater ...
@test.sql <<optional param1>>
in test.sql I have
define param2 = 'TEST'
what I want to do is set param2 = param1 if param1 is passed otherwise use the default text.
can I do this in sqlplus ?
March 12, 2004 - 9:52 am UTC
You can use ACCEPT with the DEFAULT clause, but the user will have to hit the enter key.
evaluate ....
phil, March 12, 2004 - 8:04 am UTC
addition from above ..
ultimatly what I need to do is in my script ( test.sql ) , i will have something on the lines of the following ...
SQL> create index test_idx on test(status) tablespace &¶m2 ;
param2 may be the passed in value if there was one ... otherwise it will be passed in value.
thanks
he's the king!
A reader, March 25, 2004 - 10:44 pm UTC
HE'S THE KING!!
SQL*Plus Worksheet
Pat, November 29, 2004 - 3:15 pm UTC
Hi Tom,
I use SQL*Plus Worksheet to create .sql file, and when I created sequence/trigger, something strange happen. Here's the code:
-- 1 FirstSeq;
DROP TABLE TblFirst;
CREATE TABLE TblFirst( FirstId number );
DROP SEQUENCE FirstSeq;
CREATE SEQUENCE FirstSeq;
DROP TRIGGER FirstTrig;
CREATE TRIGGER FirstTrig
BEFORE INSERT ON TblFirst
FOR EACH ROW
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
-- 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
I get an error in the first trigger body:
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
-- 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
In the first trigger body, I should not see the code for the secondSeq and what follows.
I have another file in which I drop and create all the table and everything is fine. Why this problem?
Thanks.
November 29, 2004 - 3:52 pm UTC
sorry -- not following you at all here.
More explicit
Pat, November 29, 2004 - 4:22 pm UTC
In my .sql file, I create a lot of sequences and triggers.
But when the first trigger is created, I get an error of compilation and everything stop there. (Which is correct)
When I look at the trigger body, the code inside the trigger, I see the code of the trigger (BEGIN ... END;) and I also see the command that create the second sequence, the second trigger, the third sequence, the... and so on.
Just like that:
TRIGGER BODY OF FIRST TRIGGER
-------STARTS HERE--------
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
-- These three previous commands are correct (BEGIN,
-- SELECT, END), but what follows should not be in the
-- trigger body.
-- 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
---------STOPS HERE----------------
I added some table creation to complete the example. So, the code between --STARTS HERE-- and --STOPS HERE-- is in the trigger body.
Here's the code that I execute in SQL*Plus Worksheet.
-- 1 FirstSeq;
DROP TABLE TblFirst;
CREATE TABLE TblFirst( FirstId number );
DROP SEQUENCE FirstSeq;
CREATE SEQUENCE FirstSeq;
DROP TRIGGER FirstTrig;
CREATE TRIGGER FirstTrig
BEFORE INSERT ON TblFirst
FOR EACH ROW
BEGIN
SELECT FirstSeq.nextval INTO :new.FirstId FROM dual;
END;
-- 2 SecondSeq;
DROP TABLE TblSecond;
CREATE TABLE TblSecond( SecondId number );
DROP SEQUENCE SecondSeq;
CREATE SEQUENCE SecondSeq;
DROP TRIGGER SecondTrig;
CREATE TRIGGER SecondTrig
BEFORE INSERT ON TblSecond
FOR EACH ROW
BEGIN
SELECT SecondSeq.nextval INTO :new.SecondId FROM dual;
END;
I hope that you follow me.
(I'm sorry, the previous message was not clear enough to understand what was inside the trigger body)
November 29, 2004 - 6:35 pm UTC
you need to have a "/" in column 1 to submit a plsql block to the server
begin
....
end;
/
So simple
Pat, November 30, 2004 - 10:20 am UTC
Why big problem are always solved with so simple solutions?
Why this kind of trick are never documented anywhere?
Thanks Tom, you are the BEST.
November 30, 2004 - 11:18 am UTC
but "/" is very much documented?
Pat, November 30, 2004 - 1:33 pm UTC
To be honest, I never read any documents or examples with "/" in them. Maybe I read to fast...
But anyways, thanks.
November 30, 2004 - 2:04 pm UTC
this site is chock full of them ;)
Help!!
Siva, March 11, 2005 - 11:52 am UTC
Hi Tom,
I use Oracle 9iR2 under Windows NT Environment.
I am using a script file to do a task from SQL*PLUS.My requirement is
While running that script file,I would like to write
the error messages to a separate file which may be
raised.How to achieve this??
For example
SQL @a
2 /
select * from test_emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
I would like to write the error message to a separate file.
Could you please provide a solution for this?
March 12, 2005 - 8:11 am UTC
you would have to use a slightly more "sophisticated environment", like a client program you write.
Not a simple interactive command line tool.
On unix, we would normally
$ egrep '(^ERROR|^ORA-)' x.lst > error.log
perhaps you can do the same with find under windows.
Help!!
SH, March 14, 2005 - 4:49 pm UTC
You can execute sqlplus from BAT file using the following command.
sqlplus user/password@server @a.sql > a.txt
should route all of the outputs produced by a.sql into a.txt.
Hope this helps.
escape characters
Alistair Wall, April 02, 2005 - 4:27 am UTC
You have to escape some characters with ^:
echo exec dbms_stats.gather_schema_stats(ownname=^>'SCOTT', estimate_percent=^>10); | sqlplus scott/tiger@db1
A reader, April 06, 2005 - 6:07 am UTC
Alex, May 17, 2005 - 11:13 am UTC
Tom,
I'm just starting to get in the habit of using sqlplus more, is there a better option to return rows than the default? When you return large result sets the screen flies by for about 10 minutes after only select about 30k rows. I don't see how anyone could work this way. I know all you use is sqlplus, and I'm guessing there are times when you are selecting 100k + rows, how do you handle this? Thanks.
May 17, 2005 - 2:11 pm UTC
I don't select 100k+ rows, i cannot read that much information in a day.
what are you doing that you would actually retrieve that much data (when the screen starts flying -- I hit CTL-C because I must have made a MISTAKE)
but you can
set pagesize NNN
set pause on
it'll pause evern NNNN rows and wait for you to hit the entier key (don't forget to hit enter TWICE after issuing a select, it wants an enter to show you the first page!)
Alex, May 17, 2005 - 2:33 pm UTC
Tom,
I meant for tuning purposes of large queries, I should have said that. Making a change, then waiting for the plan and stats at the end...repeat.....I just figured there was an easier way.
May 17, 2005 - 3:02 pm UTC
set autotrace traceonly
run big query
set autotrace off
you'll get
a) plan
b) statistics
c) nothing printed on your screen but a and b
Using enviornment variable in sqlplus
Vikas Sharma, June 21, 2005 - 8:31 am UTC
Hi Tom,
I have a batch file of windows which opens and closes a sqlplus session. I have declared few env variable in it. I want to use those variables in the sqlplus env which it opens. for eg. the batch file look like
<<abc.bat>>
set orauser=vikas/vikas@local
set pacakgeName=abc
sqlplus %orauser% @mysql.sql
exit
-------------------------------------
<<mysql.sql>>
create or replace package <<here i want to use an env variavle pacakgeName>> is
procedure test1;
procedure test2;
end;
/
exit
--------------------------------
I want when the batch file is run it should open sqlplus and create a package by name abc.
Is this possible ?
Please let me know how to do it for windows and unix/linux OS.
Thanks
June 21, 2005 - 4:56 pm UTC
with linux/unix trivial in shell
sqlplus u/p <<EOF
create or replace package $env_variable
as
....
end;
/
EOF
Just make sure to escape $ that are not part of environment variables...
with windows, you don't have such nice scripting capabilities in the shell, you could use a technique like this:
C:\Documents and Settings\tkyte>type test.sql
host echo define x=%NAME% > temp.sql
@temp
select '&X' from dual;
exit
C:\Documents and Settings\tkyte>type test.cmd
@echo off
set NAME=hello_there
sqlplus tkyte/tkyte @test.sql
C:\Documents and Settings\tkyte>test.cmd
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 21 16:55:56 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
old 1: select '&X' from dual
new 1: select 'hello_there' from dual
'HELLO_THER
-----------
hello_there
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
using env Variables
Vikas Sharma, June 22, 2005 - 12:51 am UTC
Thanks
Using env variable when calling procedure
Vikas Sharma, June 30, 2005 - 8:04 am UTC
Hi Tom,
I would like to use a unix env variable when executing a procedure from sql prompt. What i am trying to do is following.
telnet 192.168.92.33
SunOS 5.9
login: oracle
Password:
Last login: Thu Jun 30 11:27:59 from 192.168.92.32
Sun Microsystems Inc. SunOS 5.9 Generic May 2002
bash-2.05$ export CONNECT_STRING=username/pwd@con_str
bash-2.05$ echo $CONNECT_STRING
username/pwd@con_str
bash-2.05$ sqlplus $CONNECT_STRING
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jun 30 17:03:37 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>SQL> create or replace procedure p1(parm varchar2) as
2 begin
3 dbms_output.put_line(parm || ' Value');
4 end;
5 /
Procedure created.
SQL> exec p1($CONNECT_STRING);
But this does not gives error.
I want the result like:
username/pwd@con_str Value
Is there any way to get the desired results.
Thanks and Regards,
Vikas Sharma
June 30, 2005 - 9:47 am UTC
not without shelling out to where the environment variables are.
You can certainly concoct ways to pass the environment variable INTO sqlplus when you start up -- but interactively, you would have to host out, stuff the environment variable into "something" and then read it back in in sqlplus.
Problem scheduling the script under Windows 2003
Patrick, August 11, 2005 - 4:11 am UTC
Hi Tom,
I am trying to schedule a CMD file that executes sqlplus script.
my script:
test.cmd
@echo off
sqlplus -s test/test@EBS @c:\home\test\test.sql
It executes fine manually. When I use the task scheduler under Windows and schedule it for 20 minutes later it also works (with my account logged on or off). However, it fails if the task is scheduled for more than 3 hours or at midnight. I am not running anything else during that time. It's a clean Windows installation with Oracle 10g client.
I get an ora-12560 TNS protocol adapter error. I tried setting the oracle environment (like oracle_home) in the script and in the Windows registry, but get the same result. I also checked the sqlnet.ora parameter and set the sqlnet.authentication_service to none.
Any help would be great.
Regards,
Patrick
August 11, 2005 - 9:56 am UTC
sorry, i know nothing about 2003 or the task scheduler or many things windows specific.
could it be that the database was shutdown at midnight? that's what you get when the service is shutdown.
Dynamically Control execution path in SQL PLus scripts
Thiyag, October 07, 2005 - 10:54 am UTC
Hi Tom:
Consider a sqlplus script something like:
@@upgrade_v_1
@@upgrade_v_2
@@upgrade_v_3
@@upgrade_v_4
If need to upgrade my database schemas from 1 to 4 I would execute this script. Is there away to have the script to shift dymically the control and start executing from say "@@upgrade_v_2" and instead of "@@upgrade_v_1" based on certain conditions?
Thanks
October 07, 2005 - 9:49 pm UTC
yes....
ah, but you probably didn't want just yes or no :)
create a script called "null.sql" that - does nothing.
ops$tkyte@ORA10GR2> !cat null.sql
prompt I do nothing
ops$tkyte@ORA10GR2> !cat something.sql
prompt I do something
ops$tkyte@ORA10GR2> column SCRIPT new_val S
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select case when count(*) = 1 then 'something'
2 when count(*) = 0 then 'null'
3 end SCRIPT
4 from dual
5 where 1 = 1
6 /
SCRIPT
---------
something
ops$tkyte@ORA10GR2> @&S
ops$tkyte@ORA10GR2> prompt I do something
I do something
ops$tkyte@ORA10GR2> select case when count(*) = 1 then 'something'
2 when count(*) = 0 then 'null'
3 end SCRIPT
4 from dual
5 where 1 = 0
6 /
SCRIPT
---------
null
ops$tkyte@ORA10GR2> @&S
ops$tkyte@ORA10GR2> prompt I do nothing
I do nothing
ops$tkyte@ORA10GR2>
Thanks Tom!
Thiyagu, October 13, 2005 - 2:14 pm UTC
Thanks a lot Tom.
Scripting Challenge
A Reader, November 19, 2005 - 6:18 pm UTC
Hi
I have the following files/folders structures on my Windows2000 computer:
C:\My scripts\main.sql
C:\My scripts\DDL\create_tables.sql
C:\My scripts\DDL\create_views.sql
C:\My scripts\Proc\create_procedures.sql
C:\My scripts\Func\create_funcions.sql
I need to call remaining scripts from within main.sql. So I hard-coded something like following in main.sql
connect scott/tiger;
@"C:\My scripts\DDL\create_tables.sql"
@"C:\My scripts\DDL\create_views.sql"
@"C:\My scripts\Proc\create_procedures.sql"
@"C:\My scripts\Func\create_funcions.sql"
and it worked fine. But
The actual requirement is the following:
========================================
A user can place the "My scripts" folder anywhere on his PC so Oracle9i SQLPlus should be able to append the home folder (something like <current_folder_path>) in the start of other scripts e.g.
@<current_fodler_path>\My scripts\DDL\create_tables.sql
--OR
@@\My scripts\DDL\create_views.sql
--OR
@@+"\My scripts\DDL\create_views.sql"
But none the above 3 worked. Can anybody please explain.. how to accomplish the above requirement? A working solution will be highly appreciated. Thank you!
November 19, 2005 - 8:21 pm UTC
you could have them pass in the "root" directory if you like and run that
Like this test.sql does, it'll create a script in my Desktop subdirectory and then "run it indirectly"
ops$tkyte@ORA9IR2> !cat test.sql
set echo on
host echo prompt It is me > Desktop/test.sql
@&1/Desktop/test.sql
ops$tkyte@ORA9IR2> @test /home/tkyte
ops$tkyte@ORA9IR2> set echo on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> host echo prompt It is me > Desktop/test.sql
ops$tkyte@ORA9IR2> @&1/Desktop/test.sql
ops$tkyte@ORA9IR2> prompt It is me
It is me
ops$tkyte@ORA9IR2> @test .
ops$tkyte@ORA9IR2> set echo on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> host echo prompt It is me > Desktop/test.sql
ops$tkyte@ORA9IR2> @&1/Desktop/test.sql
ops$tkyte@ORA9IR2> prompt It is me
It is me
ops$tkyte@ORA9IR2> @test /tmp
ops$tkyte@ORA9IR2> set echo on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> host echo prompt It is me > Desktop/test.sql
ops$tkyte@ORA9IR2> @&1/Desktop/test.sql
SP2-0310: unable to open fil
SQLPLUS feature
Yoni, November 24, 2005 - 7:58 am UTC
I remember there was a way to see the old and new values of an update statement after running it in SQLPLUS.
Could you refresh my memory ?
November 24, 2005 - 9:01 am UTC
that isn't a sqlplus feature, it is a flashback query feature.
select a.ename, a.sal new_sal, b.sal old_sal
from emp a, emp as of scn &SCN b
where a.empno = b.empno
/
as of scn or as of a time - you choose, join the table with itself as of some prior point in time.
Oh sorry
Yoni, November 24, 2005 - 10:38 am UTC
I actually ment to a before_update_trigger :old and :new values. I saw someone running an update statement and then he saw these values in sqlplus.
slow sqlplus output
reslan, December 09, 2005 - 3:59 am UTC
hi Tom i have many PCs connected to database and work normaly through PLUS80w but only one PC of them show a very slow splplus output knowing that the capabilities of such PC (RAm,processor) are very good.
December 09, 2005 - 6:50 am UTC
probably a slow video card :)
do this,
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
set termout off
run query
set termout on
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
put that in a script (must be in a script), run it - and see how long it takes without painting the screen... If it run OK, it'll be the video on that pc.
Mention RLWRAP please
Ashutosh, December 09, 2005 - 12:41 pm UTC
Hi Tom,
There have been questions in this thread regarding RECALLING previously executed sql statements in SQL*Plus. It is strange that you never made a mention of rlwrap. I read about it in one of your blogs. I downloaded the source, compiled and installed it, and now I know what others who do not use it are missing! Why don't you share the link to rlwrap, and your experience using it, in this forum?
December 09, 2005 - 1:15 pm UTC
see links i like right above.... - i have mentioned it more than once??
search for rlwrap on this site, you'll see more than one reference to it.
SQLPLUSQUESTION
dileswar sahoo, January 04, 2006 - 1:31 pm UTC
ABOUT SQLPLUSQUESTION
A reader, January 05, 2006 - 2:01 pm UTC
Hi Tom,
I have the following in a .sql file
Set Serveroutput On Feed Off Echo Off Term Off Scan On Head off Ver Off Lines 132
define tflag='R'
spool c:\temp\test.txt
select 'select sysdate from dual' from dual;
$if &&tflag .EQ. R then -
select 'select ''R'' from dual ' from dual;
spool off;
When I execute the .sql file from sqlplus prompt, and I open the result spool file I only see the statement select sysdate from dual. The next statement is not getting written to the output file.
Please let me know what I am doing wrong.
Thanks.
January 05, 2006 - 2:27 pm UTC
well, that is not a sqlplus script.
$if?? whats up with that?
A reader, January 05, 2006 - 2:33 pm UTC
Is there any way that the above can be achieved with SQL* Plus?
January 05, 2006 - 2:43 pm UTC
I don't know, why don't you describe in text what you are trying to accomplish.
What I see:
.............
Set Serveroutput On Feed Off Echo Off Term Off Scan On Head off Ver Off Lines
132
define tflag='R'
spool c:\temp\test.txt
select 'select sysdate from dual' from dual;
$if &&tflag .EQ. R then -
select 'select ''R'' from dual ' from dual;
spool off;
.......................
1) you set tflag to 'R'
2) you run a query to get the text "select sysdate from dual" printed on screen (prompt would have done that)
3) you then seem to say "if the thing we set to 'R' is still 'R' then print out - well, "select 'R' from dual" (again, pronmpt would have been my choice and the if statement would not be needed).
So, you give me a script in a language that doesn't exist - I'd say:
prompt select sysdate from dual
prompt select 'R' from dual
that does what I thing you are trying to do.
trying to do conditional execution in sqlplus?
William Moffatt, January 06, 2006 - 5:22 am UTC
It's possible the reader is trying to find a way of executing particular sql commands conditionally in sqlplus.
If that is the case, I've never managed to do it in a single file (if it's possible in one file I'd love to know how), but you could do the following:
you need 3 files:
file1 : nothing.sql
-- contains nothing (or the else case)
file2 : select_r.sql
-- contains the code to run if the test succeeds
select 'select ''R'' from dual' from dual;
file3 : test.sql
-- is the driving script
Set Serveroutput On Feed Off Echo Off Term Off Scan On Head off Ver Off Lines 132
define tflag='R'
spool c:\temp\test.txt
select 'select sysdate from dual' from dual;
column what_to_do new_val do_it
select
case
when (select '&tflag' from dual)='R'
then
'select_r'
else
'nothing'
end what_to_do
from
dual;
@@&do_it
-- end of file
running sqlplus -s /@db @test
will then generate the test.txt file with the two lines in it; setting tflag to something else will generate a test.txt file with only the first line in it.
note that you need to turn spooling off before the "if" statement, and then turn it back on afterward, if you don't want the result of the query to be in the file; appending to a spool file doesn't seem to work in the version of sqlplus I have, so I haven't put that in. I haven't tested this on windows since I work on unix.
You can use this this technique for any predicate you can express as a select statement; you can also use it for doing small loops (subject to the limit sqlplus applies to the depth of @ called scripts - 20 deep on the version I have).
No doubt this is gong to get howls of anguish for what it's doing, but I had a similar problem myself and this was the only way I could find to solve it.
If anyone knows of a better way, I'd appreciate knowing it.
Spaces in parameters to sqlplus
Basil, January 20, 2006 - 12:15 am UTC
The response to "A Reader" on November 19 seems to work as long as the directory path doesn't have spaces. However, the substitution mechanism behaves strangely (it seems to me), under 9iR2, if spaces are in what is passed.
As an example, under Windows (where I'm trying to put an installer together):
D:\>mkdir \temp
D:\>mkdir "\temp\with spaces"
D:\>copy con "\temp\with spaces\target.sql"
select 'target.sql' from dual
exit
^Z
1 file(s) copied.
D:\>type \temp\input.sql
DEFINE ROOTPATH=&1
VARIABLE RP VARCHAR2(500);
exec :rp := '&1';
select :rp from dual;
PROMPT ROOTPATH:
PROMPT '&ROOTPATH.'
@@'&ROOTPATH.target.sql'
EXIT
Now for the test:
D:\>cd temp
D:\temp>sqlplus system @input.sql "d:\temp\with spaces"
SQL*Plus: Release 9.2.0.5.0 - Production on Thu Jan 19 22:49:33 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
PL/SQL procedure successfully completed.
:RP
------------------------------------------------------------------------------
d:\temp\with spaces
ROOTPATH:
'd:\temp\with'
SP2-0310: unable to open file "d:\temp\withtarget.sql"
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
D:\temp>
It's clear that the input parameter ("d:\temp\with spaces") is passed in its entirety, on the basis of the bind variable rp. But why can't PROMPT echo it completely? And why can't I use it with @@ ? No fair picking on me for doing this with Windows -- it's just one platform we have to support.
January 20, 2006 - 10:12 am UTC
with spaces - extra quoting all over the place would be necessary. spaces in directory names, what a bad idea.
Quotes and spaces
Basil, January 20, 2006 - 11:11 am UTC
As you can see, I have quotes around the command invocation in the script (@'&ROOTPATH.target.sql'). Still doesn't work.
Spaces in filenames and paths are simply unavoidable now, as they are entirely regular on both Windows and Mac OS X. In my case, I'm trying to write an installer (using InstallAnywhere, at the present time) that will use many of our existing installation scripts. I don't have much control over where the stuff is run from. If there are spaces in the directory path, I can't help that. SQL*Plus needs to get with the program.
January 20, 2006 - 12:50 pm UTC
nothing is unavoidable. My point was - it would require an inordinate amount of quoting hither and thither. sorry it doesn't work, but not too much more I can say.
use relative pathing. would be one option (you own presumably your code tree and the naming the convention)
another would be to avoid the @&rootpath - that isn't working in the quotes, you can create a spool file however on the fly that has @"\temp\with spaces\target.sql" in it using &rootpath and then run this temporary spool file
Pathing
Basil, January 20, 2006 - 12:57 pm UTC
The problem, again, is that this is intended to make things easier for the user (they don't like the current way of running SQL*Plus scripts).
Relative pathing would work, except that the current directory MUST be the same as the one containing the master script (that invokes the others, using relative pathing). I've not been able to convince the install product to do that yet.
Honestly, SQL*Plus's behavior with @'&ROOTPATH.target.sql' strikes me as a bug. Why else would it process an embedded blank as a delimeter rather than actually just pasting the whole thing in?
January 20, 2006 - 1:05 pm UTC
please file it then - I can only show how it works, but not rewrite it personally.
I would hope an installation tool would permit the changing of the current working directory when you spawn a subprogram?
'sqlplus -S'
Anoop Gupta, March 30, 2006 - 6:48 am UTC
Hi Tom,
I have lots of shell scripts those are using some processing related to database, but each shell script is connecting to the database using following command
'sqlplus -s /'
could you please tell me which username and password it will use to connect to the oracle database.
And where exactly we need to do the configration to allow it to connec to the oracle.
Waiting for your response........
March 31, 2006 - 11:25 am UTC
it uses the current OS user as the account. Consider:
[tkyte@dellpe ~]$ id
uid=500(tkyte) gid=500(tkyte) groups=500(tkyte),501(ora9ir2),502(ora10gr1),503(ora10gr2),504(ora9ir1)
[tkyte@dellpe ~]$ sqlplus /
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 31 10:59:57 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10GR2> show user
USER is "OPS$TKYTE"
ops$tkyte@ORA10GR2> show parameter authent_pref
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string OPS$
ops$tkyte@ORA10GR2>
Logged into OS as tkyte.
My os_authent_prefix is OPS$ (OPerating System)
therefore, "/" will be "OPS$TKYTE" if that account exists and is granted access to the database
create user ops$tkyte identified EXTERNALLY;
grant create session to ops$tkyte;
that would like the OS user on my system use "connect /" to log in.
sqlplus -s /
Anoop Gupta, April 05, 2006 - 3:56 am UTC
Hi tom,
This is really very useful for me. And your prompt response is really appriciable.
I have a simple query,
while firing show parameter authent_pref command
SQL> show parameter authent_pref
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
os_authent_prefix string
you can see the VALUE is NULL, so I think if we are loging in by unix user XYZ then we need to fire following command to create os authenticate user
create user XYZ identified by EXTERNALLY;
grant create session to XYZ;
But the problem is that we already have XYZ user and it is not a os authenticated user.
So is there any way by which the value of os_authent_prefix can be set to OPS$
Thanks a lot.....
April 05, 2006 - 5:50 pm UTC
yes, just set the init.ora parameter? like you would set "db_cache_size"
how to write a ececutable file to delete the records of many tables ?
jxau, April 14, 2006 - 4:27 am UTC
tom :
i want to write a file to delete the rows of 8 tables ,this tables have the same column sampledate(date type) ,when user execute this file,he must input the startdate and enddate as a delete condition
like this :
declare
startdate date :=&startdate;
enddate date :=&enddate;
begin
delete from performancedata_h t where t.sampledate between startdate and enddate ;
delete from customerdata_h t where t.sampledate between startdate and enddate;
end;
/
but i can only compile it can't execute it ,can you tell me
what shoud i do ?
thank you
April 14, 2006 - 12:34 pm UTC
er?
sounds like you want to write a stored procedure
create or replace procedure remove_some_data( p_start in date, p_end in date )
as
begin
delete from t1 where sampledate between p_start and p_end;
....
end;
/
and then the user would execute:
exec remove_some_data( to_date( '01-jan-2006', 'dd-mon-yyyy' ), to_date( '20-jan-2006', 'dd-mon-yyyy' ) )
Alex, May 24, 2006 - 2:25 pm UTC
Is there a way to turn off the
"PL/SQL procedure successfully completed."
feedback in sqlplus?
May 25, 2006 - 1:14 pm UTC
ops$tkyte@ORA10GR2> exec null;
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2> set feedback off
ops$tkyte@ORA10GR2> exec null;
ops$tkyte@ORA10GR2>
Alex, May 25, 2006 - 2:35 pm UTC
Sorry that's my fault I knew that toad just tricked me again. It recognizes sqlplus commands but this particular one just does nothing for some reason despite even working in previous toad releases....
SQLPLUS Question
Vikram Romeo, June 23, 2006 - 5:14 pm UTC
Hi Tom,
In windows , if we start a procedure in sqlplus using the exec command and then forcefully close the window, what is the expected behaviour?
It should be one of these 2 right?
1. The session gets killed and the procedure stops abruptly
2. Since the procedure is running in the server, it will finish to completion and when the server wants to send the feedback to the session saying that the procedure successfully completed, it will find out the session was killed abruptly and does nothing(could do nothing infact).
Just curious to know. Because many times - mostly in the evenings, I wanted to go home after kicking off the procedure in my laptop (after shutting the laptop down!!!) ... :-)
Regards,
Vikram Romeo
June 24, 2006 - 11:36 am UTC
You should assume 1 or 2 could happen (because 1 or 2 could happen)
If you want to run something in the background - please just use dbms_job.
variable n number;
exec dbms_job.submit( :n, 'my_proc;' );
commit;
exit
now you can go home.
SQLPLUS Question
Vikram Romeo, June 24, 2006 - 12:28 pm UTC
Thanks Tom,
Yes ... I knew about dbms_job after reading your book ... :-)
However, I just wanted to understand how a procedure executes . is there any flow of data from the server to the client when we are executing a procedure? or does it run entirely in the server and gets back to the client with the feedback only when it completes .. yes .. may be I should have put my question in a different way Tom ... you know ;-) the weekend syndrome :-) please excuse me ...
Thanks a ton Tom ... cannot thank you more ...
Regards,
Vikram Romeo
June 24, 2006 - 1:44 pm UTC
the stored procedure is executed as a single "call over" to the server, and the client "waits for the response"
So, in some cases - the procedure would execute and then the connection would "fail" on the return (no client to write to)
The procedure would likely execute - (but would it rollback or commit...), but the operative word here is "likely"
It is nothing you could depend on.
# of rows selected
Raza, June 28, 2006 - 4:37 pm UTC
Hi Tom,
Is there any pre-defined variable in sqlplus that holds the number of rows selected for eg.
SELECT * from EMP;
data....
14 rows selected.
Is this 14 stored in any variables? that I can use.
June 28, 2006 - 5:11 pm UTC
one approach, add rownum to the outer most layer of the query - wrap your query in () - and use the column command to hide rownum and save the last fetched value:
ops$tkyte@ORA9IR2> column rownum new_val r noprint
ops$tkyte@ORA9IR2> select a.*, rownum
2 from (select username, user_id
3 from all_users
4 order by username ) a
5 /
USERNAME USER_ID
------------------------------ ----------
A 80
...
WMSYS 21
XDB 35
38 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> prompt &r rows...
38 rows...
Set default directory in SQL*Plus
Tom Persaud, July 06, 2006 - 6:08 pm UTC
With SQL*Plus (9i Client for Windows), the default directory is set based on the last file opened by clicking on "File" and "Open". I thought that there would be a "Set" command for this, but cannot find it. How can I set the default directory in SQLPlusW without clicking on anything?
July 08, 2006 - 10:32 am UTC
create a shortcut with a "start in" directory set for it.
# of rows selected
Raza, July 12, 2006 - 3:12 pm UTC
Hi Tom,
<<PREVIOUS_QUESTION>>
Hi Tom,
Is there any pre-defined variable in sqlplus that holds the number of rows
selected for eg.
SELECT * from EMP;
data....
14 rows selected.
Is this 14 stored in any variables? that I can use.
Followup:
one approach, add rownum to the outer most layer of the query - wrap your query
in () - and use the column command to hide rownum and save the last fetched
value:
ops$tkyte@ORA9IR2> column rownum new_val r noprint
ops$tkyte@ORA9IR2> select a.*, rownum
2 from (select username, user_id
3 from all_users
4 order by username ) a
5 /
USERNAME USER_ID
------------------------------ ----------
A 80
...
WMSYS 21
XDB 35
38 rows selected.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> prompt &r rows...
38 rows...
<</PREVIOUS_QUESTION>>
What if there is no rows selected. Then &r would not be defined. I need to have 0 in case if there is no rows selected.
Thanks for your help!
July 12, 2006 - 4:03 pm UTC
ops$tkyte@ORA10GR2> column rownum new_val r noprint<b>
ops$tkyte@ORA10GR2> define r=0</b>
ops$tkyte@ORA10GR2> select a.*, rownum
2 from (select username, user_id
3 from all_users
4 order by username ) a
5 /
USERNAME USER_ID
------------------------------ ----------
A 170
...
WMSYS 25
XDB 38
35 rows selected.
ops$tkyte@ORA10GR2> prompt &r rows....
35 rows....
ops$tkyte@ORA10GR2><b>
ops$tkyte@ORA10GR2> define r=0</b>
ops$tkyte@ORA10GR2> select a.*, rownum
2 from (select username, user_id
3 from all_users<b>
4 where 1=0</b>
5 order by username ) a
6 /
no rows selected
ops$tkyte@ORA10GR2> prompt &r rows....
0 rows....
WHENEVER SQLERRM EXIT
chris, July 24, 2006 - 11:05 am UTC
Tom,
I have a sql script where i am facing the following problem
select x achid from t;
no rows selected
exec data_Archive.web_data(&&achid);
when i try to run this script it gives error
Wrong number or types of arguments in call to data_archive.web_data
as &&achid is null (select statment returns no value)
i want to exit from the script when ever no rows are returned from the select statment WHENEVER SQLERROR is futile as no rows being returned is not a error
please suggest me an alternative or way to stop executing the SQL SCRIPT as soon as no rows are returned from the Select statment
thanks a ton,
chris
July 24, 2006 - 12:06 pm UTC
need entire script to help you out here - must be a column achild new_val somewhere.
but why don't you just:
declare
l_achid ....;
begin
select x into l_achid from t;
data_archive.web_data( l_achid );
exception
when no_data_found then
whatever....
end;
/
uses binds, has error handling capabilities. Don't script in sqlplus unless you have to - plsql is a tad more "robust"
oracle
vinod, July 25, 2006 - 5:27 am UTC
not loging on oracle server 2000.
and "database not started"
so what are doing now
July 25, 2006 - 10:58 am UTC
crystal ball in the shop. I cannot tell what you are doing now.
perhaps you want to start your database? The DBA should be able to help you out with that one.
Alexander the ok, July 25, 2006 - 11:24 am UTC
Holy laughing out loud. Is this guy serious? That's one for the archives.
"Database broken fix pls"
NO Crystal Ball for that !!!
Robert, July 28, 2006 - 12:07 pm UTC
>> not loging on oracle server 2000.
>> and "database not started"
>> so what are doing now
> Followup:
> crystal ball in the shop.
Hahaha !
There is NO "crystal ball" or anything for that nonsense
shell script didnt wait for the sqlplus to finish the query
pal, September 12, 2006 - 11:32 pm UTC
Hi Tom,
I wrote a shell script to run a query. The query itself takes about 1 min to run. But my script doesnt seems to wait for the query to finish and exit? Can you help?
sqlplus -s $CONNECTSTRING <<$$$EOF$$$
set serveroutput on
@$bigquery.sql
$$$EOF$$$
#check the afile.log
bigquery.sql:
declare
begin
f:= UTL_FILE.FOPEN('somedir','afile.log','W');
open cur for v_sql
loop
fetch cur into msg;
UTL_FILE.PUT_LINE(f, msg);
END LOOP;
close cur;
UTL_FILE.FCLOSE(f);
end;
thanks
pal
September 13, 2006 - 7:19 am UTC
sure it does, are you yourself "backgrounding" the script when you run it. you give insufficient details as to how you run this, and how you know it "did not wait".
are you sure you want to use something like $$$EOF$$$ given that $ is sort of a special shell meta character - confusing at best.
if that is what bigquery.sql has in it, that script doesn't run at all - perhaps that is the problem - that the script isn't even running, that plsql block is not valid.
reader
A reader, September 13, 2006 - 2:28 pm UTC
Hi Tom
Could you please tell me the sequence in which the sql statement runs e.g when we submit the query in which order oracle evaluates that..like first it looks the table name in the from clause and so on.
Thanks in advance
Sachin
September 13, 2006 - 3:19 pm UTC
no, not at that level of detail - it isn't even worth our time to know that level of detail (nothing we could do with that information)
If you would like to know how oracle processes statements however - the steps from "parse" to "return data", I have written about that extensively in effective Oracle by Design
In fact, an entire chapter (chapter 5) on it.
Alexander the ok, October 03, 2006 - 1:31 pm UTC
Tom,
This is a foolish question, I apologize, but it's driving me insane.
I don't know why all of a sudden this started happening, but now when I cut and paste into sqlplus, it puts the line number after the statement, like so:
SQL*Plus: Release 9.2.0.7.0 - Production on Tue Oct 3 13:15:20 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> select 'hello world'
from dual; 2
'HELLOWORLD
-----------
hello world
If I type it out, it works normally:
SQL> select 'hello world'
2 from dual;
'HELLOWORLD
-----------
hello world
I think it has to do with changing the options on my putty telnet window to use block select mode. All I know is when I was on windows both worked fine and it didn't do any crazy weird stuff. I do all cutting and pasting so this is very annoying.
AIX 5.2 for this machine, thanks.
October 03, 2006 - 4:52 pm UTC
happens on unix all of the time, no big deal - not a problem, it works when you hit enter.
Alexander the ok, October 03, 2006 - 11:09 pm UTC
Then why do I never see this in your examples on this site? Do you don't really type everything out do you?
October 04, 2006 - 7:03 am UTC
I run scripts only - @test.sql
SP2-0268
Sokrates, October 12, 2006 - 9:10 am UTC
SQL> set feedback offf
SP2-0268: feedback option not a valid number
SQL> set feedb off
SQL>
SQL> REMARK: this works, but off is not a valid number either, isn't it ?
SQL>
Su Baba, October 19, 2006 - 3:05 pm UTC
I have a Windows Shell script that calls SQL*Plus and invokes a SQL script from SQL*plus. This is an interactive script. The user will enter the login info in the command line. If the user enters the wrong login info (username/password/SID), the script will hang because it will enter SQL*Plus shell and prompt the user for login information.
Is there any elegant way to handle this situation?
October 20, 2006 - 3:45 am UTC
what do you mean by "hang", if it is an interactive script, it'll prompt them for a username/password again????
Su Baba, October 20, 2006 - 4:53 pm UTC
Sorry for not being clear.
We have an installer that installs our software. In this installer, we invoke SQL*Plus and run a DDL script to create database objects. The installer itself is interactive. It prompts user for Oracle username/password/SID. However, if the user enters the wrong login info (e.g. wrong SID), when SQL*PLus is invoked within the installer, it will enter the SQL*Plus shell and the control will never be returned to the installer. See an example below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
H:\>sqlplus abc/abc@abc
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 20 13:40:39 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Is there anyway to get around this kind of exception?
October 20, 2006 - 5:46 pm UTC
so, have your installer connect to oracle and verify the information would be one obvious way....
or, you can use nolog, pass in the user/pass instead of using it directly on the command line to log in:
[tkyte@localhost ~]$ cat install.sql
whenever sqlerror exit
connect &1
select * from dual;
exit;
[tkyte@localhost ~]$ sqlplus /nolog @install x/y@z
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 20 17:37:31 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
[tkyte@localhost ~]$ sqlplus /nolog @install /
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 20 17:37:36 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected.
D
-
X
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@localhost ~]$
Back to SQL*Plus variables
A reader, October 24, 2006 - 5:53 pm UTC
Here's something I don't quite understand. Can you explain what is happening based on the following session log?
SQL> variable v_tmp number
SQL> define v_tmp = 1
SQL> print v_tmp
V_TMP
----------
SQL> select &v_tmp from dual;
old 1: select &v_tmp from dual
new 1: select 1 from dual
1
----------
1
Elapsed: 00:00:00.01
SQL> begin
2 select 2 into :v_tmp from dual;
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> print v_tmp
V_TMP
----------
2
In regards to you running everything in scripts, what's the setting you use to display the SQL statements along with the script output?
-- this is from unix
> cat mytest.sql
select * from dual;
-- this is from sql*plus
SQL> @mytest
D
-
X
October 25, 2006 - 9:28 am UTC
variable x number
sets up a BIND VARIABLE that can be referenced in SQL statements using :x
it is a BIND
define x = 1
sets up a "character string substitution character", so that when you reference &x in anything - sqlplus physically removes the &x and replaces it - it is a replacement character, not a bind.
A reader, October 25, 2006 - 2:53 pm UTC
How can I use v_tmp in my scripts? For example, I want to do:
variable v_tmp varchar2(30)
begin
select username
into v_tmp
from v$session having most # of sessions;
end;
/
select <stuff>
from another v$
where username = :v_tmp -- not sure how this can be done;
October 25, 2006 - 4:26 pm UTC
begin
select username into :v_temp from v$session....
end;
/
select .... where username = :v_tmp;
or just
select .... where username = (select username ....... )
A reader, October 26, 2006 - 7:08 pm UTC
You got me. I should have checked my example before posting.
Here's the silly little idea that got me started on this path:
I was trying to write a sql script to do explain plan then display all in one. I wanted to make it use the user as the statement_id, so I did:
SQL> var v_tmp varchar2(30)
SQL> begin :v_tmp := user; end;
2 /
SQL> explain plan set statement_id = :v_tmp for
2 select *
3 from mytab;
explain plan set statement_id = :v_tmp for
*
ERROR at line 1:
ORA-01780: string literal required
Your next thought is probably: why don't you use autotrace? It seems autotrace is trying to execute the entire query before coming back:
SQL> set autotrace traceonly
SQL> select *
2 from cust
3 where id not in (select cust_id from exclude_tab);
ERROR:
ORA-01013: user requested cancel of current operation
671490 rows selected.
Elapsed: 00:00:25.43
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49670 consistent gets
5280 physical reads
0 redo size
29975193 bytes sent via SQL*Net to client
313633 bytes received via SQL*Net from client
44769 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
671490 rows processed
I changed the names of the tables.
October 27, 2006 - 7:41 am UTC
set autotrace traceonly EXPLAIN
and it wont, so just use autotrace.
A reader, October 27, 2006 - 5:17 pm UTC
Thanks! That worked.
James Galloway, October 31, 2006 - 12:38 pm UTC
I am writing a sqlplus script to perform automated regression testing. Part of the testing is looking at the differences between tables in two schema.
I use a sed script to translate the input file into SQL statements for the comparison, as below (the result SQL is your comparison select statement from "On Injecting and Comparing"):
C:\usr\home\devel>cat temp.input
c1, c2, c3 FROM cTable
C:\usr\home\devel>sed -f inputparse.sed < temp.input
SELECT c1, c2, c3, Count(cnt1) C1, Count(cnt2) C2 FROM (
SELECT Schema1.cTable.*, 1 cnt1, To_Number(NULL) cnt2 FROM Schema1.cTable
UNION ALL
SELECT Schema2.cTable.*, To_Number(NULL) cnt1, 2 cnt2 FROM Schema2.cTable
) GROUP BY c1, c2, c3 HAVING Count(cnt1) <> Count(cnt2);
C:\usr\home\devel>
Is it possible for me to execute the sed statement as a sqlplus shell command, catch the output, and execute its output in sqlplus? (A bit like using :r! in vi)
Or do I need to execute the sed statement as a sqlplus shell command, redirect the output into a .sql file, and execute that file from sqlplus?
October 31, 2006 - 12:51 pm UTC
host sed -f inputparse.se < temp.input > temp.sql
@temp.sql
will be the way to do that.
Trapping sqlplus login error
Robert, February 05, 2007 - 1:39 pm UTC
Tom,
In a korn shell script when logging into sqlplus...if the username/password is incorrect, the script just hangs forever.
For the sake of bullet-proofing my code... is there a way to cause the sqlplus command to return (i.e. in error) from within a shell script if an incorrect username/password is used?
Thanks,
Robert.
February 05, 2007 - 6:23 pm UTC
no it doesn't in general, you need to sort of be more precise in what it is exactly you are doing:
Enter user-name: [tkyte@localhost ~]$ cat t.ksh
#!/bin/ksh
$ORACLE_HOME/bin/sqlplus u/p <<EOF
exit
EOF
[tkyte@localhost ~]$ ksh t.ksh
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 5 18:25:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: Enter password:
ERROR:
ORA-01005: null password given; logon denied
Enter user-name: [tkyte@localhost ~]$
Weakness in Oracle or Unix?
Robert, February 05, 2007 - 7:04 pm UTC
Tom,
So there is no way to trap a user/password login error in a ksh script which is run from cron? (for example the a user's database password changes causing the password in the script to be wrong).
Do I understand correctly?
If so, this is a weakness in either UNIX or Oracle... agree?
Thanks,
Robert
Please ignore my previous reply
Robert, February 05, 2007 - 7:18 pm UTC
Tom,
Forgive me for posting too quickly above.
I had previously gotten some strange results (which caused my script to hang), but I was able to duplicate your results which I have looked at more closely.
Thank you for your help and time.
Robert.
For Robert - ksh password check
Greg, February 06, 2007 - 10:47 am UTC
You could use something simple like this ksh function in your script to check for a valid password:
#!/bin/ksh
check_login() {
if sqlplus -s -L $1/$2 <<! >/dev/null 2>&1
!
then
return 0
else
return 1
fi
}
check_login userid password
echo return=$?
trap update with no where clause
Mahomed, March 04, 2007 - 4:37 am UTC
Hi Tom,
Is is possible to trap an "update" or "delete" statement from sqlplus which does not have a "where clause"? I would like to abort straight away if an "update" or "delete"` does not have a where clause.
March 04, 2007 - 7:01 pm UTC
no, they look no different to us than anything else. it is all just SQL.
Trap SQL without Where ...
Richard Smith, March 05, 2007 - 3:31 pm UTC
maybe you
could trap a statement using a database trigger... but why? wouldn't the user after getting an ora-20000 error after typing
delete from emp;
just come back and type
delete from emp where 1=1;
You'd have to write a lot of code to check for a where conditional that will evaluate "true" for all rows in all tables in all cases to successfully prevent an unconstrained delete. FGA may be an option to capture this info after-the-fact. My philosphy on delete (or any other ddl) is if you can't 100% trust whoever is writing that statement they should not have the privilege to begin with. If you level of confidence is <100% consider giving them an API that will do the ddl for them, or invest the time and $ into some serious training before you turn them loose with IUD privileges.
A reader, April 16, 2007 - 6:14 am UTC
Dear Tom,
Is there a way in "common" sqlplus to return a value for an SQL statement that returns "no rows selected".
Example:
select site_id "SITE", count(*)"CNT" from ac1_admin group by site_id
returns: "no rows selected"
I would like it to return:
SITE CNT
-------- -----
NA NA
Many thanks in advance.
April 16, 2007 - 1:26 pm UTC
ops$tkyte%ORA10GR2> with data
2 as
3 (select job, count(*) from scott.emp where 1=1 group by job)
4 select * from data
5 union all
6 select 'NA', null from dual where not exists (select null from data);
JOB COUNT(*)
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
ops$tkyte%ORA10GR2> with data
2 as
3 (select job, count(*) from scott.emp where 1=0 group by job)
4 select * from data
5 union all
6 select 'NA', null from dual where not exists (select null from data);
JOB COUNT(*)
--------- ----------
NA
NA/NA won't fly since count is a number.. not a string...
Florin, April 17, 2007 - 11:42 am UTC
Brilliant solution!
I'd like to know more about "WITH" option (with data).
Would you please let me know where can I find more details and example about this?
April 18, 2007 - 10:34 am UTC
Can one
Max, May 02, 2007 - 12:55 pm UTC
I 'd like to use a SQL*PLUS script for explain plan that should take a SQL statement to be explained which in turn can span more than one row and has to be passed to the explain plan command inside the script.
Is there any way to achieve that goal at all?
May 02, 2007 - 6:11 pm UTC
much easier to just
SQL> set autotrace traceonly explain;
but scripts are not entirely "smart" that way, multi-line input for a single parameter, not that I can think of.
set echo on
A reader, May 24, 2007 - 1:36 pm UTC
SET echo ON
set feedback on
SET linesize 1000
SET DEFINE OFF
SET serveroutput ON SIZE 1000000
SET trimspool on
set time on;
SET echo ON
SET DEFINE ON
SET WRAP ON
REM Beginning of script .\sequences\test_seq.sql
CREATE SEQUENCE TEST_SEQ
START WITH 1
INCREMENT BY 1
CACHE 20
NOORDER
NOCYCLE;
REM End of script .\sequences\test_seq.sql
REM Beginning of script .\sequences\test2_info.sql
CREATE SEQUENCE TEST2_INFO
START WITH 1
INCREMENT BY 1
CACHE 40
NOORDER
NOCYCLE;
REM End of script .\sequences\test2_info.sql
REM Beginning of script .\sequences\test3_info.sql
CREATE SEQUENCE TEST3_INFO
START WITH 1
INCREMENT BY 1
CACHE 20
NOORDER
NOCYCLE;
REM End of script .\sequences\test3_info.sql
OUTPUT:
--------
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 24 13:24:54 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 2 13:24:54 3 13:24:54 4 13:24:54 5 13:24:54 6 CREATE SEQUENCE TEST_SEQ
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 2 13:24:54 3 13:24:54 4 13:24:54 5 13:24:54 6 CREATE SEQUENCE TEST2_INFO
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 SQL> 13:24:54 2 13:24:54 3 13:24:54 4 13:24:54 5 13:24:54 6 CREATE SEQUENCE TEST3_INFO
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
-------------
Q: where is the statement (definition of the create stmt) in the log ? , I have set echo on there
i run this script as
sqlplus u/p@oradb < myscript.sql > myscript.out
TIA
May 26, 2007 - 11:35 am UTC
use spool, not redirection.
How to call sqlplus from Java
Ravi Rao, May 30, 2007 - 5:32 am UTC
My requirement is that I need to execute a ".sql" file from within my Java program. This ".sql" file needs some parameters. I can do this from the unix shell:
(echo define ename = 'subs'; echo @test.sql;) | sqlplus -L scott/tiger
(so that the value "subs" gets supplied to the test.sql script)
However, I cannot do the same from my Java Prog. Here, if I do
Runtime r = Runtime.getRuntime();
r.exec("define type = subs; @test.sql | sqlplus -L cmpadmin/cmpadmin");Java complains, as it does not know of an OS command by name "(echo"! Apparently, the first argument to Runtime.exec() should be the OS command itself.
We want our SQL scripts to contain parameter references,
but not parameter bindings. I hope this explains why I cannot do
Runtime r = Runtime.getRuntime();
Process p = r.exec("sqlplus -L scott/tiger@TEST @test.sql subs");as this will tightly tie the first argument to be the ename.
May 30, 2007 - 3:36 pm UTC
your requirement should probably change - error handling = NOT A CHANCE using this 'technique'. I'd give this some serious serious thought.
Given you are putting this in java, you must be doing this over and over and over - error handling is going to be tricky (that is just me, the programmer talking here)
You are mixing up SHELL and "programs"
(...) | ...
that is SHELL, it in itself is almost a script!
you can always run a shell and pass it the shell command line you want to run...
but again, I would seriously and for a long time reconsider this "requirement" - and write the java that actually RUNS the sql itself so you can deal with errors.
echo and command line
abz, June 04, 2007 - 9:22 am UTC
I did the following on AIX, but got error
$ echo execute dbms_output.putline('helle'); | sqlplus cisadm/cisadm
ksh: 0403-057 Syntax error: `(' is not expected.
$
June 05, 2007 - 8:52 am UTC
you need to use proper shell programming constructs of course.
() is special to the shell, you need to escape those characters like ALL SPECIAL CHARACTERS in shell.
[tkyte@tkyte-pc ~]$ ksh
$ echo exec dbms_output.put_line\(\'hello world\'\) | sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 5 08:51:33 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> hello world
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
$
how you got output without serveroutput on?
abz, June 05, 2007 - 9:48 am UTC
How you managed to get it without using serveroutput on?
$ echo execute dbms_output.put_line\(\'hello\'\) |sqlplus ppp/ppp
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 5 18:32:23 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
- 64bit Production
With the Partitioning, OLAP and Data Mining options
$ sqlplus ppp/ppp
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 5 18:35:37 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> execute dbms_output.put_line('hellow');
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> execute dbms_output.put_line('hello');
hello
PL/SQL procedure successfully completed.
SQL>
June 06, 2007 - 1:02 pm UTC
my login.sql file... it has set serveroutput on
To abz
Greg, June 05, 2007 - 10:06 am UTC
I'm not Tom - but I surmise that he's got a
"set serveroutput on" in his login.sql.... or something similar (he's probably using analytics somehow) :-)
running a SQLPLUS command for n times script
abz, June 06, 2007 - 10:23 am UTC
How can I write a script to run a SQLPLUS (not pl/sql)
command for n times or forever.
e.g.
I want do something like this.
for i in 1 to infinite
set pagesize 300;
end for
Thanks
June 06, 2007 - 9:17 pm UTC
you cannot, sqlplus is a very very very very simple command line tool with very very very simple scripting capabilities.
Parameters in sql script
Vin, November 21, 2007 - 6:13 am UTC
Hi Tom,
I have been going through the postings regarding parameters being passed to sql script, and this is something i have been doing successfully. But there is an issue which i have not been able to get around.
I am executing a sql script which takes in date parameters using &1, &2 and spool out the data, but the issue is the spool file contains apart from the result data couple of lines as shown below:
old 42: and trunc(ract.creation_date) between '&2' and '&3'
new 42: and trunc(ract.creation_date) between '10-SEP-2007' and '10-OCT-2007'
Now since i am utilizing the spool file as a report, i do not want to see those 2 statements. Is there a way it can be supressed/not being displayed by setting any Environment Variables as such while running the script?
November 20, 2007 - 1:22 pm UTC
SQL> set verify off
Parameters in SQL Script
Vin, November 21, 2007 - 12:45 pm UTC
Hi,
I found the answer myself, Set Verify off option sets off the display for the message.
Thanks Anyways :)
isqlplus
A reader, January 24, 2008 - 11:08 pm UTC
tom:
there is a web version of sql plus called isqlplus.
1. this was loaded on a web server and uninstalled, would i be able to install a client copy on my machine and access databases same way? if yes where do i get it from.
2. does having it installed on web server really a security concern?
3. is itpossible to configure for read only? do we have to create a ad hoc account in DB and grant only select and then somehow create a TNS service only for that account in the config file for isqlplus so that other accounts cant log in.
January 25, 2008 - 8:40 am UTC
isqlplus is officially deprecated these days.
isqlplus is installed on the server, next to the database - it would be installed on your database server. It is on the installation media included with the server.
you do not configure "sqlplus" (an application) for read only. You configure user accounts that way.
sqlplus
A reader, January 25, 2008 - 7:44 pm UTC
TOm:
SO there is no client isqlplus software that can be run from my machine to access a remote database?
2. I know you create a read only account, but I am sure you can also limit sql plus so it wont accept the other write accounts in case some hacker tried to get in using it.
January 28, 2008 - 7:01 am UTC
So, that would be so not the point. isqlplus is set up so you don't HAVE TO install any software on your system
if you are going to install software on your system - get sqldeveloper.
2) what hacker? If you have accounts that permit "write", cutting off isqlplus access does NOTHING to secure anything - if I have your credentials, heck, I'll just use sqldeveloper, or toad, or sqlplus or x, or y, or any one of a thousand other tools.
cutting off isqlplus would do nothing more than give you a false (absolutely, 100% false) sense of security (insecurity really :)
You'd want to use application level protection with VPD (virtual private database)
sqlplus
A reader, January 28, 2008 - 11:59 am UTC
Tom:
1. what is application level protection with VPD (virtual private database) ??
2. Well you are right you can install sqlplus client but you have to set up the tnsnames.ora and know the ip address and db login.
I think with web sqlplus it is setup on the server for you. so you just need to the oracle login account
January 29, 2008 - 2:48 am UTC
1) did you even try typing that into any search fields anywhere?
2) so? and that is how hard? think about this please.
with isqlplus, you need to know a) the server, b) the port, c) the db login
how is that any different than knowing the database hostname????
sql plus
A reader, July 09, 2008 - 1:44 pm UTC
Tom:
Sql*plus has a nice feature to generate html output.
However ,if i have large table the page is too wide.
I tried exec print_table('query') but it does not put column name in an html table format. It does not seem to even generate an html with it.
is there a way to get the html output in sql8plus for a table where the first colun is "field name" and second column is "Value".
sql*plus
A reader, July 10, 2008 - 7:36 pm UTC
Tom:
I created a tricky query that does the above.
However the
select to_char(CLOB) only displays 4000 bytes.
I tried DBMS_LOB.SUBSTR(CLOB,10000,1) and it seems the same issue.
Is there a way to print the whole file in SQL*PLUS?
thanks
July 11, 2008 - 8:08 am UTC
select clob from table
set long <whatever length you want printed up to>
select clob from table;
clob
A reader, July 11, 2008 - 2:36 pm UTC
Tom:
you are right. I do not know why all this discussion about 4000 byte limitation in sql*plus for varchar2 columns.
I think you wrote it to in the old expert one on one book.
Hash, September 24, 2010 - 8:05 am UTC
please refer to answer to the original question.
C:\>echo exec dbms_output.put_line( 'Hello' ); | sqlplus scott/tiger@ora8i.us.oracle.com
the command stops at SQL prompt. any solutions?
September 27, 2010 - 10:27 am UTC
C:\Documents and Settings\tkyte>echo exec dbms_output.put_line( 'Hello' ); | sql
plus scott/tiger
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Sep 27 08:27:31 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> Hello
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
works for me.
Connect SQLplus from Dos Prompt
Nazmul Hoque, August 18, 2011 - 4:45 am UTC
Hi TOM,
I am unseeing oracle 8.1.7, I am able to connect oracle server from client pc throw SQLplus. I want to connect from dos prompt, Is it possible, Enclosed below the what i have tried;
Please advise.
----------------------------------
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Hoque>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Aug 18 15:37:36 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL> connect hoque/hoque@gasldata
ERROR:
ORA-12154: TNS:could not resolve service name
SQL>
August 23, 2011 - 2:50 am UTC
looks like the tnsnames.ora on your client pc has a tns connect string gasldata that has specified a SERVICE NAME that the server you told it to connect to doesn't provide.
your tnsnames.ora is wrong.
Connect SQLplus from Dos Prompt
Nazmul Hoque, August 24, 2011 - 12:49 am UTC
I am able to connect server, If I first Open SQLplus and user/passwad@gasldata than it work fine. If I try by
sqlplus user/passward@gasldata it does not works
GASLDATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = GASLDHK)(PORT = 1521))
(CONNECT_DATA = (SID = GASLDATA))
)
pls advise.
August 30, 2011 - 3:37 pm UTC
cut and paste the text from the dos window showing it works if you get into sqlplus and then connect versus straight command line user/password.
Nazmul Hoque, September 05, 2011 - 6:07 am UTC
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Hoque>sqlplus hoque/hoque
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 5 16:55:22 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
------------------
C:\Documents and Settings\Hoque>sqlplus hoque/hoque@gasldata
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 5 16:57:11 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve service name
Enter user-name: .
Enclosed the same as you desire.
September 06, 2011 - 8:44 am UTC
ummm, one time you did not log in at all - username and password was not right.
one time you again did not log in at all - your service name in your tnsnames.ora file was not known by the listener (either remote database is not up or remote database has not registered with the listener or your service name is just plain wrong in your tnsnames.ora file)
But in any case, what you describe above:
I am able to connect server, If I first Open SQLplus and user/passwad@gasldata than it work fine.
If I try by
sqlplus user/passward@gasldata it does not works
is most certainly NOT DEMONSTRATED here at all.
connect sqlplus from Dos Prompt
Nazmul Hoque, September 07, 2011 - 1:21 am UTC
One Thing I need inform your that i have istalled personal edition in my pc and I am able connect from Dos Prompt by
SQLplus user/password to my personal Edition Database.
But not the server(enterprise edition) by
SQLplus user/password@database.
But If I change the path C:\orant\BIN and use the below
SQLplus user/password@database.
than it connect to the server.
Pls advise how can from from c:\ to d:\
September 07, 2011 - 8:41 am UTC
you need to provide the example (cut and paste) I asked for. It sounds like your TNSNAMES.ORA is not being found in the first case and we cannot resolve @database - we don't know what database to connect to.
But you are not explaining what you are doing clearly - so why not just cut and paste so we can see what you mean.
Connect SQLplus from Dos Prompt
Nazmul Hoque, September 08, 2011 - 12:46 am UTC
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Hoque>SQLPLUS HOQUE/HOQUE@GASLDATA
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 8 11:38:34 2011
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL>
Now this is solved, I just copy the below line from C:\ORANT\NET80\ADMIN\TNSNAMES.ORA(Developer 6i) to c:\orape\network\admin\tnsnames.ora (Oracle Personal Edition)
GASLDATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = GASLDHK)(PORT = 1521))
(CONNECT_DATA = (SID = GASLDATA))
)
Thanks.
Unable to Quit from sqlplus editor
Vivek, February 14, 2012 - 3:45 am UTC
Hi Tom
I am Unable to quit from the SQLPLUS editor when I run the 'ED' in Linux enterprise 5.0. seems editor is not configured. how to save and exit from that point.
February 14, 2012 - 8:11 am UTC
just type "q" to quit ed.
SQL> select * from dual;
D
-
X
SQL> edit
Wrote file afiedt.buf
21
q
1* select * from dual
SQL>
Julius, February 17, 2012 - 6:07 am UTC
Is there a way to get SQL%ROWCOUNT value in sqlplus? In other words, I'd like to capture the feedback value so that I can store it in a log table. I know I can convert the script into pl/sql block, but would be neat to do it straight in sqlplus ...
February 17, 2012 - 8:40 pm UTC
You can get it for update and deletes - but not insert as select style statements using this technique:
ops$tkyte%ORA11GR2> variable n number;
ops$tkyte%ORA11GR2> update scott.emp
2 set ename = ename
3 where ename like '%A%'
4 returning count(*) into :n;
7 rows updated.
ops$tkyte%ORA11GR2> print n
N
----------
7
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from scott.dept
2 where deptno > 20
3 returning count(*) into :n;
2 rows deleted.
ops$tkyte%ORA11GR2> print n
N
----------
2
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
but seriously, if you want to do this:
... so that I can store it in a log table. ...
your entire script file should become a single plsql block with error handling, logic, the whole thing... A program.
No output
Steve, April 11, 2012 - 10:19 am UTC
Tom,
I have tried your solution to the 1 line sqlplus procedure call on Linux using your above example, however I do not get the "Hello" echoed on my screen. Is there some sqlplus setting that I need to change?
Thank you!
April 11, 2012 - 11:54 am UTC
you do need to have "set serveroutput on" somewhere, I have mine in my login.sql file.
sql*plus output
A reader, September 04, 2013 - 11:36 pm UTC
Tom:
Maybe you can figure out this puzzle.
I use the SQL*Plus for windows (9i) client on winows machine XP 1 to connect to a 9i database server and similar software release on Windows XP machine 2 to connect to 11g database server.
The environment setting for sql*plus is the same on both desktops (defaults).
When i run the sme query on machine 1 like
select substr(a.spid,1,9) pid, substr(a.sid,1,5) sid from
v$session....
I get column sizes that match the substring size.
On machine 2 that connects to 11g database, it does not. it lists the column output based on the view or table size so i do not see much data.
How do i get output on machine 2 to be identical to machine 1 so I can see all the columns in one screen width?
September 09, 2013 - 11:07 am UTC
sam
query v$sql
i believe you'll find two different sql statements - one with cursor sharing = force/similar and one with cursor_sharing=exact. that is in v$sql you'll find:
select substr(a.spid,1,9) pid, substr(a.sid,1,5) sid from ...
select substr(a.spid,:"SYS_BV0",:"SYS_BV1") pid, substr(a.sid,:"SYS_BV2",:"SYS_BV3") sid from ...
and it will be becuase of a login.sql file - or a logon trigger or something like that.
SQL PLus
A reader, September 24, 2013 - 5:48 pm UTC
Tom:
I could not find the query for V$sessions in V$SQL.
The cursor sharing is the SAME for both.
Also, the same LOGIN.SQL used for both.
The only difference is for 11g I use BATCH file to set the home environment for the SQL utility.
Any ideas how to fix this so it will trim the size of columns?
9i> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 0
11g> show parameter cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
LOGIN.SQL
column global_name new_value gname
set termout off
select substr(host_name,1,8)||'$'||lower(user)||'@' ||upper(instance)||decode(substr(version,1,3),'11.','11g','9.2','9i',null)
global_name from v$thread a, v$instance b where a.instance=b.instance_name;
set termout on
set sqlprompt '&gname> '
set feedback on
set serveroutput on size 1000000
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
sqlplusw.bat (used to connect to 11g only)
set oracle_home=c:\oracle92
set NLS_LANG=.AL32UTF8
start c:\oracle92\bin\sqlplusw.exe
sql*plus
A reader, November 08, 2013 - 3:57 am UTC
Tom:
Do you have any ideas on resolving the column width size in sql*plus for windows?
November 11, 2013 - 9:30 am UTC
sam, if you don't see any queries in v$sql, ummm, then what query are you talking about.
run query, demonstrate issue. I don't know what you are talking about. show queries from v$sql (if they ain't there, you didn't run them. run them and they will be there)
A reader, December 27, 2013 - 8:20 pm UTC
You can do the following
sqlplus scott/tiger <<EOF
exec package.procedure_name;
EOF
sqlerrorlogging
bijunator, April 09, 2014 - 10:06 am UTC
I have been trying the below stuff in SQLPLUS.
SQL>@myscript.sql
SQL>select * from sperrorlog;
Shows only error of mysql1.sql and not the one from mysql2.sql! Why??? Is there anyway I can see that too?
myscript.sql -->
set errorlogging on truncate
@mysql1.sql
@mysql2.sql
/
sho errors procedure my_proc
mysql1.sql -->
create table already_existing_table (myvar number);
mysql2.sql -->
create or replace procedure my_proc
as
my_var varchar2(10);
begin
select 'blah blah' into my_var from non_existing_table;
end;
ignore above query
bijunator, April 09, 2014 - 11:45 am UTC
sqlplus / , how it works
A reader, May 04, 2017 - 8:16 am UTC
Respected Tom Sir(long live),
Thank you very much for your contribution towards oracle database concept.
I learnt many things from you directly getting answer from you for my question or indirectly from you post to other peoples' question.
I have tried to find how
sqlplus /
actually works.
Read somewhere in other sites that it is a default login set up in some O/S file with some variable my_ora* environment variable , but never found any variable with that name.
I only know this connects to the schema OPS$name, which is
OPS$unix_logname.
Could you please tell , in which file the default credential(the user name and password) is stored.
And if it is a requirement that "sqlplus /" should be pointed to an different schema, how could it be possible.
Thanks,
Biswaranjan
May 04, 2017 - 9:06 am UTC
When you do "sqlplus /", it uses OS authentication. For this to work you need to create a database user which has the same name as:
<os_authent_prefix> || <OS username>
os_authent_prefix is a database parameter that defaults to ops$. So if my OS user is csaxon, I need a database user named ops$csaxon:
bash-3.00$ echo $USER
csaxon
bash-3.00$ sqlplus /
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 01:57:45 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 01:57:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> grant create session to ops$csaxon identified by csaxon;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
bash-3.00$ sqlplus /
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 01:58:16 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL>
Further reading:
http://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG951 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1769187400346969442 https://oracle-base.com/articles/misc/os-authentication
thanks
Ranjan, May 04, 2017 - 10:20 am UTC
Thank you for the post and the links as well.