473,324 Members | 2,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

[pl/sql] Assign a query's value to a variable

17
Hello everybody,
i create a stored producedure in Oracle that will get the user name who connect to Oracle database.
For exemple, i declare a variable "o_user" and i want to assign the value of the query (select user from dual) to the variable.
It's possible?

i know that it's possible to do by this statement "select user into o_user from dual". But i want to know if we have another way to do by using the operation ( := ) to assign.
So thanks before hand, and sorry for my poor english.
Trakal
Sep 11 '07 #1
7 18132
amitpatel66
2,367 Expert 2GB
Hello everybody,
i create a stored producedure in Oracle that will get the user name who connect to Oracle database.
For exemple, i declare a variable "o_user" and i want to assign the value of the query (select user from dual) to the variable.
It's possible?

i know that it's possible to do by this statement "select user into o_user from dual". But i want to know if we have another way to do by using the operation ( := ) to assign.
So thanks before hand, and sorry for my poor english.
Trakal
make use of a CURSOR if u want to use assignment operator:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. o_user VARCHAR2(10);
  3. CURSOR C1 IS SELECT USER FROM DUAL;
  4. BEGIN
  5. FOR I IN C1 LOOP
  6. IF(C1%FOUND) THEN
  7. o_user := I.user;
  8. END IF;
  9. END LOOP;
  10. END;
Sep 11 '07 #2
trakal
17
Hello amitpatel66 and everybody,
So thanks for your quickly answer. this answer is useful for me.
But if you don't mind me, can i ask you another question because this code is look complicated for my programme.
I don't know if you know SQL Server.

Let see this example in SQL Server

CREATE PROCEDURE [dbo].[test]
( @username[nvarchar] (64) = NULL)
AS
IF@username IS NULL
BEGIN
SELECT DISTINCT @username = SYSTEM_USER
END....

This is the extrait of a procedure that let to assign directly the name of database's user. The procedure have a user name as a parameter. it verify if this parameter is null or not, if it's null, it will assign the name of user to this parameter.
I want to use the operator assign (:=) because i hope that it has a query look like the one in SQL Server. it's maybe simplier for generate.
(if you don't understand my question, please let me know, i'll reforme my question)
thanks in advance.
Sep 11 '07 #3
amitpatel66
2,367 Expert 2GB
Hello amitpatel66 and everybody,
So thanks for your quickly answer. this answer is useful for me.
But if you don't mind me, can i ask you another question because this code is look complicated for my programme.
I don't know if you know SQL Server.

Let see this example in SQL Server

CREATE PROCEDURE [dbo].[test]
( @username[nvarchar] (64) = NULL)
AS
IF@username IS NULL
BEGIN
SELECT DISTINCT @username = SYSTEM_USER
END....

This is the extrait of a procedure that let to assign directly the name of database's user. The procedure have a user name as a parameter. it verify if this parameter is null or not, if it's null, it will assign the name of user to this parameter.
I want to use the operator assign (:=) because i hope that it has a query look like the one in SQL Server. it's maybe simplier for generate.
(if you don't understand my question, please let me know, i'll reforme my question)
thanks in advance.
Hi,

In PLSQL, you can assign a value to a variable either using INTO clause or using an explicit cursor as I have shown above.
Sep 12 '07 #4
trakal
17
good morning,
Thanks alot for your answer.
Have a nice day
Sep 12 '07 #5
Saii
145 Expert 100+
USER can be simply assigned to a variable just like sysdate. You dont need to do select ....from dual;
<variable>:=USER;
Sep 12 '07 #6
trakal
17
USER can be simply assigned to a variable just like sysdate. You dont need to do select ....from dual;
<variable>:=USER;
can i do something like SQL Server?
For exemple: SELECT DISTINCT o_user := USER FROM DUAL;
I think that it's impossible. But i just try to ask you all if you already see this problem and solve it.
Thank.
Sep 13 '07 #7
Saii
145 Expert 100+
can i do something like SQL Server?
For exemple: SELECT DISTINCT o_user := USER FROM DUAL;
I think that it's impossible. But i just try to ask you all if you already see this problem and solve it.
Thank.
I am not sure about the SQL Server.
Sep 13 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
0
by: Omar K | last post by:
Hi, I am quite new to frontpage and SQL but I have a Stock Control access database / frontpage to set up. My last problem deals with the automatic updating of the total quantity of stock with parts...
13
by: MLH | last post by:
Suppose I have this simple SQL string... SELECT tblDrivers.DriverID, tblDrivers.DName FROM tblDrivers WHERE (((tblDrivers.DName) Like "N*")) ORDER BY tblDrivers.DriverID; And suppose that...
5
by: devx777 | last post by:
Hello, I am trying to find some information or an example on how to build a dynamic query in DB2 that would allow me to join a table which its name is stored as a field value on another table....
0
by: woollymammoth | last post by:
I can't assign a MS SQL Server table record value to a simple VB variable, should be an easy thing. Sample SQL Server table has the data in the record as a char(30) string, the column for that record...
1
by: woollymammoth | last post by:
I can't assign a MS SQL Server table record value to a simple VB variable, should be a really easy thing. Sample SQL Server table has the data in the record as a char(30) string, the column for that...
10
by: Tony K | last post by:
How would I assign the result of a SQL query to a variable. The result of the following statement will ALWAYS result in 1 row returned. SELECT ProductDescription, ProductID FROM ...
7
by: Peter Nurse | last post by:
Two (almost) identical SQL Server databases (DB1 backed up and restored to DB2 yesterday). DB2.dbo.GetSchPaymentsTD took 1.5 seconds (!) to execute DB1.dbo.GetSchPaymentsTD took less than a...
7
by: RachH | last post by:
I am using Access 2003 and need some guidance on the correct way to reference a variable in two different procedures when the variable's value is based on a SQL query. The database form has a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.