Home » SQL & PL/SQL » SQL & PL/SQL » In list as argument to procedure (merged)
In list as argument to procedure (merged) [message #390124] Thu, 05 March 2009 01:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #390145 is a reply to message #390144] Thu, 05 March 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the source of your procedure FROM USER_SOURCE.

Regards
Michel
Re: In list as argument to procedure (merged) [message #390149 is a reply to message #390144] Thu, 05 March 2009 03:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #390169 is a reply to message #390124] Thu, 05 March 2009 05:29 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
What did you want to do with that execute immediate there?

That is not the way to pass PL/SQL variables.

Just try to substitute it with simple PL/SQL insert.

begin
	insert into jemp
		SELECT *
		FROM   emp
		WHERE empno IN (
				SELECT * 
				FROM TABLE(in_list)
			);
	commit;
end;


Bye Alessandro
Re: In list as argument to procedure (merged) [message #390178 is a reply to message #390169] Thu, 05 March 2009 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's a very good point @Alessandro.
I'm vaguely embarrassed I didn't spot that myself.

Re: In list as argument to procedure (merged) [message #390191 is a reply to message #390178] Thu, 05 March 2009 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is (about) the same question than current deb.b's one: http://www.orafaq.com/forum/t/141555/102589/

Regards
Michel
Re: In list as argument to procedure (merged) [message #390203 is a reply to message #390124] Thu, 05 March 2009 07:39 Go to previous message
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
Previous Topic: Dependencies of procedures within a stored package (merged)
Next Topic: Problem with DBMS_JOB
Goto Forum:
  


Current Time: Wed Dec 07 16:36:18 CST 2016

Total time taken to generate the page: 0.13811 seconds