evaluation of variables 1

[ceccina]

CREATE OR REPLACE FUNCTION MESSITEST."PROT_GEN" (name_seq IN VARCHAR2) RETURN NUMBER IS
aNumber NUMBER ;


BEGIN

SELECT name_seq.NEXTVAL into aNumber from dual;

RETURN aNumber;
EXCEPTION
WHEN OTHERS THEN
RETURN null;
END PROT_GEN;
/

That gives me an error on select statement.
How can I say to evaluate the variable name_seq?

 

[SantaMufasa]

Ceccina,

Here is code that does what you want:

CREATE OR REPLACE FUNCTION PROT_GEN (name_seq IN VARCHAR2) RETURN NUMBER IS
    aNumber NUMBER;
BEGIN   
    execute immediate 'SELECT '||name_seq||'.NEXTVAL from dual' into anumber;
    RETURN anumber;
EXCEPTION
    WHEN OTHERS THEN
        RETURN null;
END PROT_GEN;
/

Function created.

select prot_gen('CECCINA_SEQ') x from dual;

         X
----------
        26

1 row selected.

select prot_gen('CECCINA_SEQ') x from dual;

         X
----------
        27

1 row selected.

Spero che questo aiuta.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ceccina]

Yes,
thanks a lot.

 

[BJCooperIT]

Dave,
Would you like to provide an explanation why dynamic SQL must be used here?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[SantaMufasa]

Excellent point, Barb!

PL/SQL has limitations to the SQL that it can execute natively (i.e., SQL that it can process without the benefit of the "EXECUTE IMMEDIATE" directive). PL/SQL can natively execute:[ul][li]SQL Data-Manipulation Language(DML) verbs:[/li][ul][li]SELECT...[/li][li]INSERT...[/li][li]UPDATE...[/li][li]DELETE...[/li][li]COMMIT[/li][li]ROLLBACK[/LI][/ul][li]And the above commands only when all of the tokens (i.e., syntactical "pieces") of the commands are fully resolvable (i.e., "defined") at the time that PL/SQL's SQL parser attempts to check the syntax of the DML statement.[/li][/ul]In Ceccina's case, the specific sequence name (the value of "name_seq") is not known at the time that Ceccina wants to CREATE OR REPLACE FUNCTION PROT_GEN. Therefore, we must tell PL/SQL to delay the syntax checking of the SELECT... statement until the moment that Ceccina actually executes the function.



Did that take care of business?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[BJCooperIT]

Perfectly. As always!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)