[pl/sql] Problem of select and display the result

[trakal]

Hello everybody,
i have a problem that i don't know how to solve it.
I created a procedure like this:

create or replace
PROCEDURE Employee_LoadById
(
p_Id NVARCHAR2
)
AS
BEGIN
EXECUTE IMMEDIATE 'SELECT DISTINCT "Employee"."Id", "Employee"."Name", "Employee"."LastWriteTime", "Employee"."CreationTime"
FROM "Employee"
WHERE "Employee"."Id" = :1' USING p_Id;
END;

My questions are:
1- I tried to create this procedure without the command "EXECUTE IMMEDIATE", but i have an error because the pl/sql don't allow to write the select statement without this key word or without the "Select INTO".
That why i use the key word "EXECUTE IMMEDIATE".
I want to know it's a good way to do or not for such procedure (to load the data in the table).
2- This procedure is no problem to compile or to exec by using the command " CALL ..." or "EXEC ..." and it display repectively that "Appel terminé" = "call finished" or " Procédure PL/SQL terminé avec succès" = "PL/SQL procedure finished with success".
This procedure doesn't display the result (the rows in the table) for me.
It has a problem with my procedure or not.

I search in the internet and most of the my result's research, it use the select statement without the command "EXECUTE IMMEDIATE". So can anyone give me any ideas?
Hope to receive some replies for this problem, i say thanks very much in advance.
Have a nice day.

[amitpatel66]

Hello everybody,
i have a problem that i don't know how to solve it.
I created a procedure like this:

create or replace
PROCEDURE Employee_LoadById
(
p_Id NVARCHAR2
)
AS
BEGIN
EXECUTE IMMEDIATE 'SELECT DISTINCT "Employee"."Id", "Employee"."Name", "Employee"."LastWriteTime", "Employee"."CreationTime"
FROM "Employee"
WHERE "Employee"."Id" = :1' USING p_Id;
END;

My questions are:
1- I tried to create this procedure without the command "EXECUTE IMMEDIATE", but i have an error because the pl/sql don't allow to write the select statement without this key word or without the "Select INTO".
That why i use the key word "EXECUTE IMMEDIATE".
I want to know it's a good way to do or not for such procedure (to load the data in the table).
2- This procedure is no problem to compile or to exec by using the command " CALL ..." or "EXEC ..." and it display repectively that "Appel terminé" = "call finished" or " Procédure PL/SQL terminé avec succès" = "PL/SQL procedure finished with success".
This procedure doesn't display the result (the rows in the table) for me.
It has a problem with my procedure or not.

I search in the internet and most of the my result's research, it use the select statement without the command "EXECUTE IMMEDIATE". So can anyone give me any ideas?
Hope to receive some replies for this problem, i say thanks very much in advance.
Have a nice day.

EXECUTE IMMEDIATE command is used to execute Dynamic SQL statements and not the one that you have used here. From your SELECT statement, I could say since there are many columns, you can use a CURSOR rather using INTO clause. A single CURSOR will hold all the columns and you can LOOP through Cursor to perform required operations on each record.

[trakal]

EXECUTE IMMEDIATE command is used to execute Dynamic SQL statements and not the one that you have used here. From your SELECT statement, I could say since there are many columns, you can use a CURSOR rather using INTO clause. A single CURSOR will hold all the columns and you can LOOP through Cursor to perform required operations on each record.

Hello amitpatel66
Thanks alot for your answer. Yes sure, i can use the cursor. But my project is not to use that because this procedure will be used with the application writing by C#.
Because we use the procedure by using the OracleCommand and to read the data by OracleDataReader of ODP.Net. So we don't want to create the cursor in the server (oracle).
do you have any suggestions?
i'm ready to answer you all the questions, if my explication is not enought to understand.

Thanks in advance
ps: may you send me the reference to understand more about that?

[debasisdas]

why you need to use EXECUTE IMMEDIATE for this.

and since you are only selecting ,why u need a procedure.

why not execute the SQL query directly.

[trakal]

why you need to use EXECUTE IMMEDIATE for this.

and since you are only selecting ,why u need a procedure.

why not execute the SQL query directly.

1. I use "EXECUTE IMMEDIATE" as i wrote before that this procedure is not compiled without this command.
2. This procedure is very important for my application console writing in C#.
In my application, i create a method called LoadById(string id). This methode use this procedure to get the data from the Oracle database. For example, i use this method by using the parameter "id", so this parameter's value will be used for the parameter (input type) of the procedure. One time the procedure is executed, so the method will use the OracleDataReader to get the result of the procedure.
3.the execution of SQL Query is not required for my application. the objectif of my application is to use the procedure to get the data from Oracle database.

Hope that i answer for your question.
thanks in advance for your help.

[Saii]

you can directly call this select statement in UI and assign to resultset.

[trakal]

you can directly call this select statement in UI and assign to resultset.

Hello,
As i said recently that my application need to use the procedure in the Oracle Server and display the result in client. We don't want to use the query directly.
All the method, that we use in the application (in C#), call the procedure to display the data.
Thanks before for all and so welcome for all your suggestions.

[Saii]

Add an OUT parameter to your procedure of type SYS_REFCURSOR(9i onwards).
Open refcursor for <your query>;
Handle OUT refcursor resultset in UI.

[trakal]

Hello Saii and all readers,
Sorry that i couldn't have a good explaination in english. I try now to re-ask the question in another way. here are my problems.
My application is writed in C#, use ODP.Net and call the stored procedures in Oracle database (the procedures are created in the type of method CRUD "Create, Read, Update and Delete").

In fact, i want to create a procedure to replace the select query such the example below. The procedure have a parameter in input named "Id".

*****************************************
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT DISTINCT Id, Name from \"Employee\" where \"Id\" = 3";
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
Console.WriteLine(reader.GetString(1));
}
*****************************************

Do you have any ideas for that?
So thanks in advance for your help.
Trakal

[Saii]

Hello Saii and all readers,
Sorry that i couldn't have a good explaination in english. I try now to re-ask the question in another way. here are my problems.
My application is writed in C#, use ODP.Net and call the stored procedures in Oracle database (the procedures are created in the type of method CRUD "Create, Read, Update and Delete").

In fact, i want to create a procedure to replace the select query such the example below. The procedure have a parameter in input named "Id".

*****************************************
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT DISTINCT Id, Name from \"Employee\" where \"Id\" = 3";
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
Console.WriteLine(reader.GetString(1));
}
*****************************************

Do you have any ideas for that?
So thanks in advance for your help.
Trakal

Create the procedure with OUT refcursor in Oracle. Then call that procedure in you UI code. I am not sure about the syntax in C#. maybe something like CommandType = CommandType.StoredProcedure and then handle the paramters and their direction(input and output)
hope that helps!!!

[trakal]

Hello Saii and all the reader,
So thanks alot for your helps Saii, it's so helpful. i can now solve my problem as your proposition by creating the procedure with OUT refcursor in Oracle and (Id) IN number.
One time again so thanks.
Trakal