Home » SQL & PL/SQL » SQL & PL/SQL » problem with oracle sql+
problem with oracle sql+ [message #244632] Wed, 13 June 2007 10:07 Go to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Hi,
can anyone help me with an issue with oracle 9i,
I log in to the server as SYSTEM SYSDBA, but when I try to to execute sql script in sql+ it gives me error:
SP2-0640: Not connected

thank you,
Re: problem with oracle sql+ [message #244649 is a reply to message #244632] Wed, 13 June 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you did: copy and paste your SQL*Plus session.
Post your script.
Post your Oracle version (4 decimals).
Please read and follow How to format your posts.

Regards
Michel

[Updated on: Wed, 13 June 2007 11:12]

Report message to a moderator

Re: problem with oracle sql+ [message #244658 is a reply to message #244632] Wed, 13 June 2007 12:21 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Hi, Michael
first thank you for helping me.

There's no difference what sort of script do I run with SQL+
it give's me the same error even if I try to write
the simplest SQL query
Oracle database version is 10.1.0.2.

Thank you again.
Re: problem with oracle sql+ [message #244663 is a reply to message #244658] Wed, 13 June 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then you didn't connect. Try "connect" statement.

Regards
Michel
Re: problem with oracle sql+ [message #244696 is a reply to message #244663] Wed, 13 June 2007 17:07 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Yes I've tried that too but it gives me error
ora-12560: TNS: protocol adapter error

thanks again
Re: problem with oracle sql+ [message #244699 is a reply to message #244632] Wed, 13 June 2007 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Are you incapable or unwilling to follow simple directions/requests?

Post what you did: copy and paste your SQL*Plus session.
Post your script.
Post your Oracle version (4 decimals)!!!!!!!!!!!!!!!!!!
Please read and follow How to format your posts.

What is Operating System name & version?

[Updated on: Wed, 13 June 2007 17:11] by Moderator

Report message to a moderator

Re: problem with oracle sql+ [message #244700 is a reply to message #244699] Wed, 13 June 2007 17:19 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
I'm running oracle10g on windows XP
can you please tell me how to post SQL* plus session

thank you
Re: problem with oracle sql+ [message #244701 is a reply to message #244632] Wed, 13 June 2007 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
open a command window & invoke sqlplus
Those who live by the GUI, die by the GUI.
Re: problem with oracle sql+ [message #244702 is a reply to message #244701] Wed, 13 June 2007 17:35 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
yes I'm already dead by the GUI.
when I tried to connect to the database by invoking the command line I made it thank you,
but anyway I don't know yet what the problem is with the GUI. I still can't connect using GUI sqlplus.

So should I run a script in the command line to create the tables?

thank you for understanding Ana
Re: problem with oracle sql+ [message #244722 is a reply to message #244702] Wed, 13 June 2007 23:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Open a DOS/Command box.
Execute: set ORACLE_HOME=<your oracle home>
Execute: set ORACLE_SID=<your oracle sid> (if your database is local)
Verify %ORACLE_HOME%\bin is in your PATH
Execute: sqplus /nolog
Execute: connect <user>/<password> if your database is local
or connect <user>/<password>@<name for remote database> if your database is remote
If you can connect (no error), execute: select * from v$version;
Use your mouse to select all these.
Copy it.
Paste it here.

Regards
Michel

[Updated on: Wed, 13 June 2007 23:20]

Report message to a moderator

Re: problem with oracle sql+ [message #244860 is a reply to message #244722] Thu, 14 June 2007 06:38 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Hi again Michel
the code is here

E:\Documents and Settings\Vlatko.KINGSTONE>sqlplus /nologon

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 14 13:19:46 2007

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

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-C <v>] [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_identifier>] | / | /NOLOG
      <start>  ::= @<URL>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-C" sets SQL*Plus compatibility version <v>
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode

E:\Documents and Settings\Vlatko.KINGSTONE>sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 14 13:20:20 2007

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

Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> connect system\vd8511 as sysdba
Enter password:
Connected.
SQL>select * from &version
  2


I don't know what the problem is but every sql query i try to execute gives me as a result - 2

thanks

[Updated on: Thu, 14 June 2007 06:42]

Report message to a moderator

Re: problem with oracle sql+ [message #244862 is a reply to message #244860] Thu, 14 June 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
/NOLOG

Where do you see /nologon?

Quote:
<username>[/<password>]

Where do you see \?

Quote:
select * from v$version;

Where do you see &version?

Regards
Michel

Re: problem with oracle sql+ [message #244866 is a reply to message #244862] Thu, 14 June 2007 06:59 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Sorry lapsus scribae
the new code is
E:\Documents and Settings\Vlatko.KINGSTONE>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 14 13:19:46 2007

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

SQL> connect system/vd8511 as sysdba
Enter password:
Connected.
SQL>select * from v&version
  2


but the result is the same

thanks for the patience

[Updated on: Thu, 14 June 2007 07:01]

Report message to a moderator

Re: problem with oracle sql+ [message #244880 is a reply to message #244866] Thu, 14 June 2007 07:48 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Use dollar sign, not ampersand.
Re: problem with oracle sql+ [message #244881 is a reply to message #244880] Thu, 14 June 2007 07:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also the "2" is not the result, but the prompt to enter a second line, since you didn't finish the command with ";"
Re: problem with oracle sql+ [message #244905 is a reply to message #244881] Thu, 14 June 2007 09:40 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Ohh, here's the result

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL>


thanks, but how can this help me with the sp2-0640 not connected problem with the gui

[Updated on: Thu, 14 June 2007 10:31]

Report message to a moderator

Re: problem with oracle sql+ [message #244923 is a reply to message #244905] Thu, 14 June 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just to show you that if it uses "connect" then it does not have any error.

Regards
Michel
Re: problem with oracle sql+ [message #244942 is a reply to message #244923] Thu, 14 June 2007 12:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Sounds to me like like you have a different installation for the pathetic GUI and command line sqlplus. You'll have to copy the TNS entry from the tnsnames.ora file from your ORACLE_HOME/network/admin for the command line to the GUI's ORACLE_HOME/network/admin
Re: problem with oracle sql+ [message #244943 is a reply to message #244905] Thu, 14 June 2007 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now type "sqlplusw" instead of "sqlplus" and do the same thing.

Regards
Michel
Re: I've solved [message #244969 is a reply to message #244923] Thu, 14 June 2007 14:20 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
Thank you for helping, i've solved the problem.

Can anyone tell me can I parse an sql script generated with the forward engineering tool of ERwin 4.1 in oracle's sqlplus to generate the database?

thank you

[Updated on: Thu, 14 June 2007 14:22]

Report message to a moderator

Re: I've solved [message #244970 is a reply to message #244969] Thu, 14 June 2007 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the problem was?
This may help someone else.

Regards
Michel
Re: I've solved [message #244971 is a reply to message #244970] Thu, 14 June 2007 14:31 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
I don't know whether the .sql script generated by ERwin is compatible with oracle's sqlplus because it generates some errors when I try to parse the script.

some of the errors were:
ora-04089 cannot create triggers on objects owned by sys 
ora-00942 table or view does not exist


in fact these are the only repeatedly appearing errors.

thanks again Michel
Re: I've solved [message #244972 is a reply to message #244971] Thu, 14 June 2007 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a close look at your script.
It tries to execute illegal statements against SYS schema.

Regards
Michel
Re: I've solved [message #244981 is a reply to message #244972] Thu, 14 June 2007 15:07 Go to previous messageGo to next message
vlatko
Messages: 11
Registered: June 2007
Location: Radovis
Junior Member
I see in the script something like

CREATE UNIQUE INDEX XPKLOCATION ON LOCATION
(
   LOCATION_ID           ASC
);



this means that the primary key is ascending but isn't that not implemented in oracle, because I use to do it using triggers
oracle complains about every primary key in each of the tables
?
Re: I've solved [message #244984 is a reply to message #244981] Thu, 14 June 2007 15:37 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This statement can't cause the error you posted unless you create your table and index in the SYS schema.
THIS MUST NOT BE DONE.
Quote:
this means that the primary key is ascending but isn't that not implemented in oracle

Of course it is.
SQL> create index t_i on t (col asc);

Index created.

Quote:
I use to do it using triggers

This is a very bad idea.

Regards
Michel

[Updated on: Thu, 14 June 2007 15:37]

Report message to a moderator

Previous Topic: Example code for procedure
Next Topic: Day of the week function in PL/SQL
Goto Forum:
  


Current Time: Fri Dec 09 03:55:28 CST 2016

Total time taken to generate the page: 0.07711 seconds