469,323 Members | 1,665 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

[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 17804
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

Post your reply

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

Similar topics

5 posts views Thread by devx777 | last post: by
10 posts views Thread by Tony K | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.