Home » SQL & PL/SQL » SQL & PL/SQL » define statement in script
define statement in script [message #188678] Mon, 21 August 2006 03:24 Go to next message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
Good day to you,

I am a programmer now working as a systems engineer, dba etc...
At the moment i am configuring an oracle database 10G Express i have installed as a test database. I have to run several SQL scripts. One of the scripts has the define statement. I cannot get it to work. What is wrong with it?

DEFINE asp_user = asp_user;

It should work fine, it worked before on a Oracle 9 database, but it does not here. Any ideas how I can get it to work?
Thanks in advance.

Cor
Re: define statement in script [message #188694 is a reply to message #188678] Mon, 21 August 2006 04:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Are you working from SQL*Plus? Can you please post the output.
Re: define statement in script [message #188696 is a reply to message #188694] Mon, 21 August 2006 04:33 Go to previous messageGo to next message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
Thanks for your answer. I log in, go to SQL Scripts and upload the scripts. When I run the scripts Oracle checks first and i get the message that the 4 DEFINE statements are unknown statements. The scripts are written for Oracle databases.
Re: define statement in script [message #188702 is a reply to message #188696] Mon, 21 August 2006 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What tool are you using to run these scripts?
Is it SQL*Plus or something else?
Re: define statement in script [message #188707 is a reply to message #188678] Mon, 21 August 2006 04:54 Go to previous messageGo to next message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
Good question. Since the database manager came with the database i can tell it is definitely Oracle but I cannot get more information out of it. No command prompt, mouse driven.
Name is SQL Workshop.
Re: define statement in script [message #188708 is a reply to message #188707] Mon, 21 August 2006 05:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DEFINE is an SQL*Plus statement.
If you're not running these scripts in SQL*Plus, why would you expect them to work?
Re: define statement in script [message #188714 is a reply to message #188678] Mon, 21 August 2006 05:11 Go to previous messageGo to next message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
Thanks for your answer. Point taken. Since I am unfamiliar with Oracle databases, we use Miscosoft databases and now see if we can get our application to work on Oracle databases: how do I run the scripts in SQL*Plus? The tool delivered with 10G Express obviously does not do that...
Re: define statement in script [message #188721 is a reply to message #188714] Mon, 21 August 2006 05:36 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You should have an sqlplus.exe somewhere on your system. Find it and connect you your DB (system@xe).
Re: define statement in script [message #188723 is a reply to message #188714] Mon, 21 August 2006 05:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The SQL*Plus executable can be found at %ORACLE_HOME%/bin
The command line version is calles sqlplus.exe
The Windows version is called sqlplusw.exe

From there, if you issue the command @<path to your script> it will execute the script for you.

What were you using to run the scripts in previously?
Re: define statement in script [message #188730 is a reply to message #188678] Mon, 21 August 2006 06:06 Go to previous messageGo to next message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
I used the tool SQL Workshop, delivered with the database. Sqlplus.exe is installed and I found out how to run the scripts. Now I get a window Enter Bind Variables. Script error or my fault?
Re: define statement in script [message #188758 is a reply to message #188730] Mon, 21 August 2006 09:30 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can you post your script here (with output) so we can look at it?
Re: define statement in script [message #188890 is a reply to message #188678] Tue, 22 August 2006 05:01 Go to previous message
Jeeper
Messages: 6
Registered: August 2006
Junior Member
Good day to all,

Thank you for helping me out, appreciate that..
This is the relevant code of the non-working script. No output.
Tablespaces are created.
All that happens when I run it is that a popup window appears which asks for bind varables and has a submit button. When pressed the message ORA-00900 Invalid SQL statement appears in the result window of the tool I use to run the SQL scripts and commands.



promp ...setting up user variables
define asp_user = asp_user;
define asp_password = asp_dcs;
define mc3_user = mc3_user;
define mc3_pwd = mc3_dcs ;
promp ...creating role mc3_ROLE
CREATE ROLE mc3_ROLE NOT IDENTIFIED;
GRANT CREATE INDEXTYPE TO mc3_ROLE;
GRANT CREATE SESSION TO mc3_ROLE;
GRANT CREATE TABLE TO mc3_ROLE;
GRANT CREATE TABLESPACE TO mc3_ROLE;
GRANT CREATE VIEW TO mc3_ROLE;
promp ...creating new ASP user
CREATE USER &&asp_user PROFILE "DEFAULT" IDENTIFIED BY &&asp_password
DEFAULT
TABLESPACE "TDCDATA" ACCOUNT UNLOCK;
promp ...granting rights to new ASP user
GRANT UNLIMITED TABLESPACE TO &&asp_user;
GRANT mc3_ROLE TO &&asp_user;
GRANT CONNECT TO &&asp_user;
ALTER USER &&asp_user QUOTA UNLIMITED on TDCINDEX;
promp ...creating new mc3 user
CREATE USER &&mc3_user PROFILE "DEFAULT" IDENTIFIED BY &&mc3_pwd
DEFAULT
TABLESPACE "TDCDATA" ACCOUNT UNLOCK;
promp ...granting new mc3 user rights
GRANT UNLIMITED TABLESPACE TO &&mc3_user;
GRANT mc3_ROLE TO &&mc3_user;
GRANT CONNECT TO &&mc3_user;
ALTER USER &&mc3_user QUOTA UNLIMITED on TDCINDEX;
promp ...Finished Creating Users

Thanks in advance for the help.
Previous Topic: Displaying PAGE N of M through SQL*PLUS Report
Next Topic: horizontal reporting
Goto Forum:
  


Current Time: Tue Dec 06 00:29:08 CST 2016

Total time taken to generate the page: 0.11485 seconds