Simple Variable query 1

[CAFCrew]

HI All,

i am this is a very basic question but i am new to oracle syntax after 5 years of SQL and i am finding it hard to adapt. I am trying to work out how to use variables in oracle to do something similar to :

declare @CompanyName char(100)
declare @RMTID

set @CompanyName =
(select companyname where RMTid = @variable)

print @companyname.

This seems so easy in TSQL, but i have not yet worked out how the syntax works in oracle. I am receiving lots of different syntax errors etc.

Some help on this would be very much appreciated

Thanks in advance for your help

 

[CAFCrew]

p.s. i realise that there are errors in the logic of the above code, its just an example to give you an idea of the way in which i want to use the variables.

Thanks again

Matt

 

[SantaMufasa]

Yes, Matt, your logic is a tad goofy as far as Oracle is concerned since whenever you use the SELECT verb, syntax requires you to include a "FROM <tablename>" to designate the table from which the data derives. Also "companyname" and "RMTid" must be columns that appear in the table from which you SELECT data.

So, to illustrate Oracle's equivalent syntax for the TSQL syntax you asserted, I've created a table (named, "MATT") from which I can read data:

select * from matt;

RMTID COMPANYNAME
----- ------------------
   10 Oracle Corporation
  666 Microsoft Corp

Here is code that is roughly equivalent to your original code, but tailored to produce meaningful results:

SQL> var Companyname char(100)
SQL> var variable number

SQL> exec :variable := 10

PL/SQL procedure successfully completed.

SQL> exec select companyname into :companyname from matt where rmtid = :variable

PL/SQL procedure successfully completed.

SQL> print :Companyname

COMPANYNAME
------------------
Oracle Corporation

If, however, your objective is to simply display onto the screen the results of your query, then you can simplify the code in this fashion:

SQL> var variable number
SQL> exec :variable := 10

PL/SQL procedure successfully completed.

SQL> select companyname from matt where rmtid = :variable;

COMPANYNAME
--------------------
Oracle Corporation

And that isn't even the simplest way to get your results while having SQL*Plus prompt for a particular RMTID. (You must place the following code in a script/file that you then invoke from a SQL*Plus prompt:

(contents of script named "yada.sql"):
accept id_in prompt "Enter the RMTId value to display: "
select companyname from matt where rmtid = '&id_in';

(invocation of "yada.sql" from SQL*Plus prompt):
SQL> @yada
Enter the RMTId value to display: 666

COMPANYNAME
--------------------
Microsoft Corp

Let us know if the above answers your questions to your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

666 Microsoft Corp

lol

 

[CAFCrew]

Hey Mufasa (Dave of Sandy, Utah, USA),

Thanks for this. This is exactly what i was after. There are a few subtle little difference with the way that the syntax is used and the error messages seem to be even less helpful than the ones thrown up by SQL!!

The help pages of the program that i am using are no help whatsoever and i couldn't find anything on google groups or microsoft. I knew one of you guys would know.

Thanks for your help. Its very much appreciated

Matt

 

[Welshbird]

=> CAFCrew

It might be nice to award Santa a star...

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Could you stop calling SQL*Server "SQL". SQL is a generic language for databases and is used by all vendors. It is not a synonym for MicroSoft's relational database offering.

 

[CAFCrew]

Thanks Willif, i have done just that

Apologies Dagon, i didn't realise that this was so important. I am truely very embarrased.I will try and stop this from now on.