| SQL statement in PL/SQL [message #369932] |
Wed, 08 November 2000 03:05  |
jimmy
Messages: 21
Registered: November 2000
|
Junior Member |
|
|
Hi,
I'm trying to build up a select statement in pl/sql using a variable received as a parameter. The code look like this:
create function getNext(s IN VARCHAR2) return NUMBER is
next NUMBER;
begin
select s.nextval INTO next from dual;
return (next);
end getNext;
The question is; How do I get ".nextval" added to s?
Thanks!
Jimmy
|
|
|
|
| Re: SQL statement in PL/SQL [message #369933 is a reply to message #369932] |
Wed, 08 November 2000 04:34  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Yes you can using Dynamic SQL inside the PL/SQL.
I have enhanced the code to suit your requirements. Try this out. If you have any queries please let me know.
create or replace function getNext(s IN VARCHAR2) return NUMBER is
next NUMBER;
v_cursor_name INTEGER ;
v_return INTEGER ;
v_string VARCHAR2(500) := NULL ;
begin
v_string := 'SELECT ' || s ||
'.NEXTVAL FROM DUAL ' ;
v_cursor_name := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE(v_cursor_name, v_string, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(v_cursor_name, 1, next);
v_return := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor_name) ;
DBMS_SQL.COLUMN_VALUE(v_cursor_name,1,next);
DBMS_SQL.CLOSE_CURSOR(v_cursor_name);
return (next);
end getNext;
/
I tried the following example
var babu number ;
exec :babu := getNext('cid_seq')
PL/SQL procedure successfully completed.
print babu
BABU
----------
308106823
I hope there shouldn't be any problem.....
Good Luck!
Babu Paul
|
|
|
|
| Re: SQL statement in PL/SQL [message #369977 is a reply to message #369932] |
Tue, 21 November 2000 16:19 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hi
If u are using Oracle8i, u have a new option of Execute immediate else u need to use DBMS_SQL.
Oracle 8i Solution looks like this:
create function getNext(s IN VARCHAR2) return NUMBER is
next NUMBER;
begin
execute immediate
('select '||s||'.nextval INTO next from dual;');
return (next);
end getNext;
Ref DBMS_SQL package in PLSQL documentation for other solution
Hope this helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
