In list as argument to procedure (merged) [message #390124] |
Thu, 05 March 2009 01:30  |
yashora
Messages: 39 Registered: August 2006
|
Member |
|
|
Hi all,
whats wrong with this piece of code?
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000)
/
Type Created.
And now i have a procedure which is having a local function returning a list of values.
create or replace procedure myproc(p_inlist varchar2)
is
FUNCTION in_list
RETURN t_in_list_tab
AS
p_in_list varchar2(2000):=p_inlist;
l_tab t_in_list_tab := t_in_list_tab();
l_text VARCHAR2(32767) := p_inlist || ',';
l_idx NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
begin
execute immediate 'insert into jemp (SELECT *
FROM emp
WHERE empno IN (SELECT * FROM TABLE(in_list)))';
commit;
end;
The problem is everytime it is returning the same set of values initially passed. Say for the first time, if i pass 1,2 as employee numbers those values only are being captured everytime even if i execute the procedure with different values like 3,4
What would be the problem? can anybody suggest me on these lines please?
Regards
Yashora
|
|
|
Re: In list as argument to procedure (merged) [message #390134 is a reply to message #390124] |
Thu, 05 March 2009 02:20   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm afraid I'm going to want to see some proof of that claim.
Can you do this:
In SQL*Plus,
1) delete table Jemp,
2) run your code passing in '1,2'
3) show us the rows in jemp
4) delete jemp
5) run your code passing in '3,4'
6 show us the rows in jemp.
I assume that your string must have a trailing comma - otherwise your code fails to get the last value from the list.
|
|
|
Re: In list as argument to procedure (merged) [message #390144 is a reply to message #390124] |
Thu, 05 March 2009 03:11   |
yashora
Messages: 39 Registered: August 2006
|
Member |
|
|
SQL> delete jemp;
0 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> EXEC MYPROC('1,2');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM JEMP;
EMPNO ENAME
---------- --------------------
1 jag
2 jon
SQL> delete jemp;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from jemp;
no rows selected
SQL> EXEC MYPROC('3,4');
PL/SQL procedure successfully completed.
SQL> select * from jemp;
EMPNO ENAME
---------- --------------------
1 jag
2 jon
|
|
|
|
Re: In list as argument to procedure (merged) [message #390149 is a reply to message #390144] |
Thu, 05 March 2009 03:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I really don't think that the code that you've posted is the code that you're executing.
1) When I run it, I get exactly what I expect - an error saying that the Execute immediate command has no idea what 'IN_LIST' is.2
2) If you take your code and modify it to output the contents of IN_LIST to dbms_output, then you'll see that it ignores the last value in the list - so it's not likely that it inserts values 1 and 2.
In the session where you run this, can you
1) SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'MYPROC';
2) SELECT type,text
FROM user_source
WHERE name = 'MYPROC'
ORDER BY line;
When I run your code, I get this:
drop table emp;
drop table jemp;
create table emp (empno number, ename varchar2(30));
insert into emp values (1,'Emp 1');
insert into emp values (2,'Emp 2');
insert into emp values (3,'Emp 3');
create table jemp as select * from emp where 1=2;
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000)
/
create or replace procedure myproc(p_inlist varchar2) is
FUNCTION in_list RETURN t_in_list_tab AS
p_in_list varchar2(2000):=p_inlist;
l_tab t_in_list_tab := t_in_list_tab();
l_text VARCHAR2(32767) := p_inlist || ',';
l_idx NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);
-- dbms_output.put_line('l_tab('||l_tab.last||')='||l_tab(l_tab.last));
END LOOP;
RETURN l_tab;
END;
begin
execute immediate 'insert into jemp (SELECT * FROM emp WHERE empno IN (SELECT * FROM TABLE(in_list)))';
commit;
end;
/
begin
myproc('1,2');
end;
/
SQL>
SQL> begin
2 myproc('1,2');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-00904: "IN_LIST": invalid identifier
ORA-06512: at "DEV.MYPROC", line 22
ORA-06512: at line 2
|
|
|
Re: In list as argument to procedure (merged) [message #390155 is a reply to message #390124] |
Thu, 05 March 2009 04:21   |
yashora
Messages: 39 Registered: August 2006
|
Member |
|
|
Hi,
I am herewith attaching the screen shot with the steps i have executed in sql*plus environment. The same piece of code i have copied from your reply post and executed that successfully. Plase verify the screenshot and i am thankful to you for your support and at the same let us work together to chase the prob.
Regards
Yashora
|
|
|
Re: In list as argument to procedure (merged) [message #390168 is a reply to message #390155] |
Thu, 05 March 2009 05:22   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think I know what you've done.
I think you've got a function called IN_LIST that returns a table containing the number 1 & 2.
There has to be a function of that name defined somewhere otehr than your procedure, because the call to EXECUTE IMMEDIATE can't see the internal function that you've defined.
Can you run this in SQL*Plus:
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'IN_LIST';
|
|
|
|
|
|
Re: In list as argument to procedure (merged) [message #390203 is a reply to message #390124] |
Thu, 05 March 2009 07:39  |
yashora
Messages: 39 Registered: August 2006
|
Member |
|
|
My apologies for the delayed reply. Thanks to JRowbottom as he identified the stored function with the same name in the database and what he said is absolutely correct. I have dropped that and got the error what he suggested to me earlier. Now i understood clearly the "execute immediate" statement would not see the local function. As stated by Michel Cadot, i found the solution and hope it will solve my problem. Thanks to one and all.
Regards
Yashora
|
|
|