Home » SQL & PL/SQL » SQL & PL/SQL » Variable passing please explain difference between T-SQL and PL/SQL
icon5.gif  Variable passing please explain difference between T-SQL and PL/SQL [message #208850] Tue, 12 December 2006 06:21 Go to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
I'm trying to run a query where I just do a simple variable replacement. I use this to test logic of code against different values if I must manually replace the value in many places. For simplicity I've only used the variable in one place below.

In Transact SQL, used by MS SQL Server, I would just do a declare then a set command then use the variable.

As a simplified example this works in T-SQL:

declare @freq int;
set @freq=5000;
select (round((@freq+2.15774) / 4.3125,0)-6)*2+1;


This returns 2309.

In PL/SQL:

select ((ROUND( (5000+2.15774)/4.3125 )-6)*2)+1 as Zippy from DUAL; 


This also returns a value of 2309. Note 5000 used instead of a variable.

All I wish to do is pass the number in a similar fashion to TSQL but it fails.
I get the message PLS-00428: an INTO clause is expected in this SELECT statement.

declare freq int :=5000;
begin
select ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1 as Zippy from DUAL; end;


I do not have the permissions to create functions so how can accomplish passing a variable in PL/SQL?

Thanks in advance for your help.
Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208881 is a reply to message #208850] Tue, 12 December 2006 07:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In pl/sql you are not required to do calculations as Select statements - you can (and absolutely should - it's much quicker) just execute the required statement:
declare
  freq int :=5000;
begin
  freq := ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1; 
end;


If you absolutely insist on using a SELECT statment, you need to select it INTO a value in pl/sql - this is to give you control over where and into what data types the data returned from a query goes:
declare
  freq int :=5000;
begin
  select ((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1 as Zippy 
  INTO freq
  from DUAL;
end;
Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208886 is a reply to message #208881] Tue, 12 December 2006 07:28 Go to previous messageGo to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
Both your code snippets work but I do not get any output?

I use Oracle SQL Developer and when I run the select with 5000 in place of the variable I get an ouptut.

When I run your code it just tells me how long it took to run but I don't see 2309 which is what I expect to see as output?
Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208889 is a reply to message #208886] Tue, 12 December 2006 07:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pl/Sql does not (without making it) return anything to the user - this is because it is generally called from other pieces of pl/sql or some form of GUI, and you wouldn't want it outputing lots of irrelevant details.

There are several ways of getting the result of your calculation back (create the whole thing as a function and return the value, use DBMS_OUTPUT to display it in SQL*Plus, update some part of the databse with it, raise it as the Error message of a use defined exception) - if you can give us some clue what you're trying to do, we can help more.
Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208891 is a reply to message #208889] Tue, 12 December 2006 07:59 Go to previous messageGo to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
In general I'm trying to run a SQL command to return values. As I'm very limited in permissions being able to pass a variable to code is similar to being able to create a function. That is what I was trying to accomplish. Simply to pass freq as a variable to obtain a value. This functionality allows me to test the logic of the select statement. It is useless to not get output. I tried using
set serveroutput on
AND
exec dbms_output.enable(10000);
Neither did anything for me in Oracle SQL Developer. I guess the solution is to get permissions to create functions as that is essentially why I'm trying to pass variables. Thanks.
Re: Variable passing please explain difference between T-SQL and PL/SQL [message #208893 is a reply to message #208891] Tue, 12 December 2006 08:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you waqnt to use Dbms_output, you need to modify the pl/sql anonymous block to output it's result:
declare
  freq int :=5000;
begin
  dbms_output.put_line(((ROUND( (freq+2.15774)/4.3125 )-6)*2)+1);
end;
Previous Topic: Help 2 Oracle scripts
Next Topic: Distinct is killing the performance...
Goto Forum:
  


Current Time: Sun Dec 04 23:08:06 CST 2016

Total time taken to generate the page: 0.08187 seconds