1

Topic: Dynamic sql (NDS): search OUT of variables of procedure

Greetings!
Whether probably to implement the such:

PROCEDURE sp1
(id_in IN NUMBER
, prm1_out OUT NUMBER
, prm2_out OUT NUMBER
, prm3_out OUT NUMBER)
AS
i NUMBER: = 1;
l_sqlquery VARCHAR2 (200);
l_sqlinto VARCHAR2 (200);
BEGIN
LOOP
l_sqlQuery: ='SELECT smth INTO prm ' || i || ' _out FROM tbl WHERE _ID = ' || id_in || ";
EXECUTE IMMEDIATE l_sqlQuery;
i: = i+1;
EXIT WHEN i> 3;
END LOOP;
END;

So it would be desirable, but does not work because of INTO
Tried so

EXECUTE IMMEDIATE l_sqlQuery INTO l_sqlinto;

Where l_sqlinto tried to appropriate also:

EXECUTE IMMEDIATE ' l_sqlinto: = prm ' || i || ' _out ';

Swears also:

ORACLE wrote:

ORA-00900: invalid SQL statement

So, it is possible idea to implement? If yes,  as?

2

Re: Dynamic sql (NDS): search OUT of variables of procedure

rtyts;
The strange want - area of visibility at them different.
And the given example generally it is not clear to what, to enumerate 3 parameters not a problem:

PROCEDURE sp1
(id_in IN NUMBER
, prm1_out OUT NUMBER
, prm2_out OUT NUMBER
, prm3_out OUT NUMBER)
AS
BEGIN
SELECT
smth
, smth
, smth
INTO
prm1_out
, prm2_out
, prm3_out
FROM tbl t
WHERE t. _ ID=sp1.id_in;
END;

3

Re: Dynamic sql (NDS): search OUT of variables of procedure

wrote:

And what task try to solve? Can be better to do not many output parameters, and one tabular type?

Yes, I already decided to go some other way. Purely scientific interest. I in  , therefore could miss details, even bluntly incorrect syntax.
Let's tell so, it is interesting to me, whether can NDS pull such  or not or expenses are too high...

wrote:

So  in such variant returns some lines

No. Returns only single line. I where strongly reduced section for a topic. But sense that SELECT that unambiguously returns only one value.

4

Re: Dynamic sql (NDS): search OUT of variables of procedure

xtender, it agree.
I already transferred the procedure interface. There were sports interest to NDS.

5

Re: Dynamic sql (NDS): search OUT of variables of procedure

rtyts wrote:

it is interesting to me, whether can NDS pull such  or not

Can.  the task is distinctly formulated. At present the task is determined at the moment of compilation, NDS is not necessary.

6

Re: Dynamic sql (NDS): search OUT of variables of procedure

Elic what to add in the task description? By the way, OUT parameters already 38. Today sent   the version of the interface of procedure. Here thought their cycle to sort out.

7

Re: Dynamic sql (NDS): search OUT of variables of procedure

rtyts wrote:

By the way, OUT parameters already 38.

Without a difference. It all the same does not import uncertainty.
. Also do not try to put that you do not understand, there, where you do not understand.

8

Re: Dynamic sql (NDS): search OUT of variables of procedure

Elic wrote:

Also do not try to put that you do not understand, there, where you do not understand.

Well so to understand, about it it is necessary to read somewhere so? Or there is here someone already was born with understanding?
[quote =] area of visibility at them different

Time is the answer, I do an output what to solve in the beginning the described task that method which I want it is impossible.