Home » SQL & PL/SQL » SQL & PL/SQL » Variable "in list" using PL/SQL Table
Variable "in list" using PL/SQL Table [message #191304] Tue, 05 September 2006 11:10 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I found this code on askTom but I'm not doing something correct. I'm sure it has to do with my PL/SQL table but I'm not sure how it's suppose to be coded.

I trying to build an "in list" from my "serverSequence" variable that contains multiple servers.


Create SQL TYPE
create or replace type myTableType as table of number;

In the PACKAGE SPEC

type servers is table of varchar2(10)
    index by binary_integer;

PACKAGE BODY

PROCEDURE report (s              raw,
                  reportId       number,
		  serverSequence servers,
		  submit         varchar2) IS
.
.
.
select *
  from (select *
          from server
            where sequence in (select * from THE (select cast (serverSequence as myTableType) from dual))


The variable "serverSequence" will contain a list of server numbers that a person has selected from a web form before calling the report procedure.
Re: Variable "in list" using PL/SQL Table [message #191313 is a reply to message #191304] Tue, 05 September 2006 11:49 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You can't use a PL/SQL type here at all - just the SQL type. The delimited list of servers - serverSequence - needs to be conveted into a variable based on your myTableType and then that variable is used in the TABLE function. Here's my generic example:

sql>create or replace type NumberTable as table of number;
  2  / 
 
Type created.
 
sql>CREATE OR REPLACE function f_number_table(
  2    p_list       in  varchar2,
  3    p_delimiter  in  varchar2 default ',')
  4    return numbertable
  5  is
  6    v_string  long := p_list || p_delimiter;
  7    v_pos     pls_integer;
  8    v_data    numbertable := numbertable();
  9  begin
 10    loop
 11      v_pos := instr(v_string, p_delimiter);
 12      exit when (nvl(v_pos, 0) = 0);
 13      v_data.extend;
 14      v_data(v_data.count) := trim(substr(v_string, 1, v_pos - 1));
 15      v_string := substr(v_string, v_pos + 1);
 16    end loop;
 17    return (v_data);
 18  end f_number_table;
 19  / 
 
Function created.
 
sql>create or replace procedure p_update_emp
  2  (p_emp_list in varchar2, p_sal in emp.sal%type)
  3  is
  4  begin
  5  update emp set sal = p_sal where empno in (select * from table(cast(f_number_table(p_emp_list) 
as NumberTable)));
  6  end;
  7  / 
 
Procedure created.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369       800
     7499      1600
     7521      1250
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.
 
sql>exec p_update_emp('7369,7499,7521', 10000)
 
PL/SQL procedure successfully completed.
 
sql>select empno, sal from emp;
 
    EMPNO       SAL
--------- ---------
     7369     10000
     7499     10000
     7521     10000
     7566      2975
     7654      1250
     7698      2850
     7782      2450
     7788      3000
     7839      5000
     7844      1500
     7876      1100
     7900       950
     7902      3000
     7934      1300
 
14 rows selected.
Re: Variable "in list" using PL/SQL Table [message #191319 is a reply to message #191304] Tue, 05 September 2006 12:13 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ah, so what I need to do is loop through the "serverSequence" variable (since there could be 1, 10 or 50 values) calling some function that converts the values into a SQL type variable. Once that is done, I can then use the SQL type variable in the SQL statement.

I believe that's what you are trying to tell me.

Question: I couldn't just replace "serverSequence servers," with "serverSequence myTableType," in my procedure? I suppose not since maybe PL/SQL wouldn't know anything about myTableType.

Re: Variable "in list" using PL/SQL Table [message #191324 is a reply to message #191319] Tue, 05 September 2006 14:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Well, you wouldn't loop through the serverSequence variable - you would just pass it once into the function which would return a collection to be used in the SQL statement (and that conversion can be done inline in the SQL statement itself as shown in the example).

You could certainly pass the server values into the procedure as myTableType - PL/SQL can see the SQL type (but not the other way around). You would just have to do the delimited list to collection conversion before or during the call to this proc.
Re: Variable "in list" using PL/SQL Table [message #191337 is a reply to message #191304] Tue, 05 September 2006 15:28 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Thanks Todd!

I did get it to work but not with a function. I tried writing a function but it didn't like my "select * from table( cast (sampleFunction(serverSequence) as numberTableType))" call to the function. It keep giving me an error saying the types didn't match or something like that.

This is what I did to get it to work:


PROCEDURE report (s              raw,
                  reportId       number,
		  serverSequence servers,
		  submit         varchar2) IS
				  
  reportName varchar2(70);
  
  numberTable numberTableType := numberTableType();

BEGIN
.
.
.

for i in 1..serverSequence.count loop
  numberTable.extend;
  numberTable(numberTable.count) := serverSequence(i);
end loop;
.
.
select *
  from servers
    where sequence in (select * from table( cast (numberTable as numberTableType))) 			   
Re: Variable "in list" using PL/SQL Table [message #191340 is a reply to message #191337] Tue, 05 September 2006 15:37 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Without seeing your function I can't say for sure, but either you were not passing in (servers) or returning (numberTableType) the correct datatype that the function expected.

Glad you have it working.
Re: Variable "in list" using PL/SQL Table [message #191341 is a reply to message #191304] Tue, 05 September 2006 15:42 Go to previous message
Duane
Messages: 452
Registered: December 2002
Senior Member
I know for a fact I was passing in (servers) but I could have gotten my return wrong (numberTableType). I already deleted the function so I can't double check. I thought for sure I had both parameters correct but who knows.

Thanks again.

[Updated on: Tue, 05 September 2006 15:43]

Report message to a moderator

Previous Topic: How to write the records in the file using UTL_FILE package
Next Topic: Extract only Alphabets.
Goto Forum:
  


Current Time: Sat Dec 10 05:02:19 CST 2016

Total time taken to generate the page: 0.05271 seconds