Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

evaluation of variables 1

Status
Not open for further replies.

ceccina

IS-IT--Management
Joined
Nov 18, 2002
Messages
22
Location
IT
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?
 
Ceccina,

Here is code that does what you want:
Code:
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. [2thumbsup]

[santa]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.”
 
Yes,
thanks a lot.
 
Dave,
Would you like to provide an explanation why dynamic SQL must be used here?

Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
 
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?

[santa]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.”
 
Perfectly. As always! [thumbsup2]

Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top