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

[trakal]

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

[amitpatel66]

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:

DECLARE
o_user VARCHAR2(10);
CURSOR C1 IS SELECT USER FROM DUAL;
BEGIN
FOR I IN C1 LOOP
IF(C1%FOUND) THEN
o_user := I.user;
END IF;
END LOOP;
END;

[trakal]

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.

[amitpatel66]

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.

[trakal]

good morning,
Thanks alot for your answer.
Have a nice day

[Saii]

USER can be simply assigned to a variable just like sysdate. You dont need to do select ....from dual;
<variable>:=USER;

[trakal]

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.

[Saii]

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.