Solved

Oracle stored procedure input string with variable length/field

Posted on 2007-03-23
10
1,438 Views
Last Modified: 2013-12-07
I need to use a stored procedure to check the input from frontend with various number of accounts (1, 2, or 40) and check EACH of these accounts in th e Oracle database table to see if they are valid or not and give out warning if the account is not valid. What will be the best way to handle this? Shall the input be in one string with accounts numbers seperated with comma or pipe, or it's better to get the accounts in seperate lines (one account in each line)? And, with either method, how to loop through each account to check in the tables and give out status for each account?
0
Comment
Question by:laoqi
  • 5
  • 4
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I'd probably go with a comma separated list.

Here's an example that accepts a comma separated list and returns the valid numbers as a comma separated list.
-------------------------------------
drop table tab1;
create table tab1 ( col1 number );

insert into tab1 values(1);
insert into tab1 values(5);
insert into tab1 values(40);
commit;

create or replace procedure checkAcct(inList in varchar2, validList out varchar2)
is
      myCur sys_refcursor;
      myVal number;
begin
      open myCur for 'select col1 from tab1 where col1 in (' || inList || ')';

      loop
            fetch myCur into myVal;
            exit when myCur%notfound;

            validList := validList  || myVal || ',';
      end loop;
      validList := rtrim(validList,',');

end;
/

show errors

var testOutput varchar2(100)

exec checkAcct('1,2,3,4,5',:testOutput);

print testOutput
0
 

Author Comment

by:laoqi
Comment Utility
Hi Slighwv,
Thank you very much. Your reply definitely help me get things started. I tried your code in an old test envirnment (Oracle version 8.1.7.4.0). It worked after adding the reference cursor declaration. And I learnt from your last 3 lines about how to display the ref cursor (what does "var" really do?).
In my real development envirnment (Oracle version 9.2.0.8.0), I modified the code for the real table and real account number (which are varchar). It compiled fine but keep complain about "invalid identifier" on the last account number in the inlist. Here are the screenprint:
SQL> @tst_act

Procedure created.

No errors.
SQL>
SQL> exec apt_owner.ActValidation('AB12345,Y100003,Y100004');
BEGIN apt_owner.ActValidation('AB12345,Y100003,Y100004'); END;

*
ERROR at line 1:
ORA-00904: "Y100004": invalid identifier
ORA-06512: at "APT_OWNER.ACTVALIDATION", line 10
ORA-06512: at line 1


SQL> exec apt_owner.ActValidation('AB12345');
BEGIN apt_owner.ActValidation('AB12345'); END;

*
ERROR at line 1:
ORA-00904: "AB12345": invalid identifier
ORA-06512: at "APT_OWNER.ACTVALIDATION", line 10
ORA-06512: at line 1


SQL> exec apt_owner.ActValidation('AB12345,Y100003');
BEGIN apt_owner.ActValidation('AB12345,Y100003'); END;

*
ERROR at line 1:
ORA-00904: "Y100003": invalid identifier
ORA-06512: at "APT_OWNER.ACTVALIDATION", line 10
ORA-06512: at line 1


SQL> l
  1  create or replace procedure  apt_owner.ActValidation (
  2    inList IN VARCHAR2
  3  --  , Valmes OUT varchar2
  4  )
  5  IS
  6            TYPE sys_refcursor IS REF CURSOR;
  7            cur sys_refcursor;
  8     Val     varchar2(400);
  9  begin
 10        open cur for 'select ACC_APT_I from pantheon_account where ACC_APT_I in ('||inList|| ')';
 11        loop
 12              fetch cur into Val;
 13             BEGIN
 14             DBMS_OUTPUT.PUT_LINE (Val||' is a pantheon account.');
 15             exception
 16                          when no_data_found then
 17                             DBMS_OUTPUT.PUT_LINE (Val||' is not a pantheon account.');
 18             END;
 19  --            exit when cur%notfound;
 20
 21  --            Valmes := Valmes  || Val || ',';
 22        end loop;
 23  --      Valmes := rtrim(Valmes,',');
 24
 25* end;


0
 
LVL 1

Expert Comment

by:rommeljohnsantos
Comment Utility
hi,

var is used to declare a bind variable.   And slightwv used it as a plcaholder of the output parameter of the procedure.  you should have this when you execute the proc but not necessarily a bind variable as slightwv showed above.

cheers
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Just to add:  variable or var for short defines a SQL*Plus variable.  Then as rommeljohnsantos pointed out, I used that as a bind variable to the procedure.

Now back to your errors:
You are passing strings into the procedure.  The proper syntax for strings in an IN list is:  select...where col in ('a','b','c').  So, you need to get the single quotes around the list.  I would probalby do this in the procedure.

Give this a try.
-----------------------------------------------
drop table tab1;
create table tab1 ( col1 char(1) );

insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
commit;

create or replace procedure checkAcct(inList in varchar2, validList out varchar2)
is
      myCur sys_refcursor;
      myVal char(1);
begin
      open myCur for 'select col1 from tab1 where col1 in (''' || replace(inList,',',''',''') || ''')';

      loop
            fetch myCur into myVal;
            exit when myCur%notfound;

            validList := validList  || myVal || ',';
      end loop;
      validList := rtrim(validList,',');

end;
/

show errors

var testOutput varchar2(100)

exec checkAcct('a,b,e',:testOutput);

print testOutput

0
 

Author Comment

by:laoqi
Comment Utility
Thank you all. I was thinking about the 'replace' trick after I sent the response...now slightwv already gave me the worked out syntax. And rommeljohnsantos also explained me the concept. It is so helpful here.

The procedure compiled fine, but when I tried to exec the procedure, it gave me endless (seems) line of the last account number in the input list. I couldn't figure out where the endless loop could be (the account only has one entrance in the table). The actual output I want is the warning/exception about invalid account.

Screenprint:
If I enter a invalid account number:

exec apt_owner.ActValidation('AB12345');
...
...
is a pantheon account.
is a pantheon account.
...
...
BEGIN apt_owner.ActValidation('AB12345'); END;

*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at "APT_OWNER.ACTVALIDATION", line 15
ORA-06512: at line 1


If I enter valid account numbers (the last two accunt number in the input list have single entrance in the table):
exec apt_owner.ActValidation('AB12345,Y100003,Y100006');
...
...
Y100006 is a pantheon account.
Y100006 is a pantheon account.
Y100006 is a pantheon account.
Y100006 is a pantheon account.
...
...
BEGIN apt_owner.ActValidation('AB12345,Y100003,Y100006'); END;

*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at "APT_OWNER.ACTVALIDATION", line 15
ORA-06512: at line 1


The actual code for the procedure:

  1  create or replace procedure  apt_owner.ActValidation (
  2    inList IN VARCHAR2
  3  --  , Valmes OUT varchar2
  4  )
  5  IS
  6            TYPE sys_refcursor IS REF CURSOR;
  7            cur sys_refcursor;
  8     Val     varchar2(400);
  9  begin
 10        open cur for 'select ACC_APT_I from pantheon_account where ACC_APT_I in (''' || replace(inList,',',''',''') || ''')';
 11  --('||inList|| ')';
 12        loop
 13              fetch cur into Val;
 14             BEGIN
 15             DBMS_OUTPUT.PUT_LINE (Val||' is a pantheon account.');
 16             exception
 17                          when no_data_found then
 18                             DBMS_OUTPUT.PUT_LINE (Val||' is not a pantheon account.');
 19             END;
 20  --            exit when cur%notfound;
 21
 22  --            Valmes := Valmes  || Val || ',';
 23        end loop;
 24  --      Valmes := rtrim(Valmes,',');
 25
 26* end;

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
First:
dbms_output.put_line will not work for what you want.  This is only to display values to the console and really can't be trapped from a front-end.

That's why there was an OUT parameter in the procedure.

Now back to the problem:
Without knowing your table structure and the data, I'm guessing there are MANY rows in pantheon_account with the same ACC_APT_I.  If you can't further narrow it down with additional fields, maybe just add a DISTINCT to the select.
------------------
...
open cur for 'select distinct ACC_APT_I from pantheon_account where ACC_APT_I in (''' || replace(inList,',',''',''') || ''')';
...
0
 

Author Comment

by:laoqi
Comment Utility
As I mentioned, these accounts all have single entrance in the table. Plus the invalid account (not existing in the table) also showed endless line of message but just no account number in the beginning (see the earlier post).

I modified the code with the "distinct", and WHAT I REALLY WANT IS THE ACCOUNTS THAT ARE NOT IN THE TABLE. It dosen't compile. Below is the screenprint and the code:


SQL> @tst_act

Warning: Procedure created with compilation errors.

Errors for PROCEDURE APT_OWNER.ACTVALIDATION:
 

LINE/COL ERROR
-------- -----------------------------------------------------------------
16/3     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         begin case declare end exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge
         <a single-quoted SQL string> pipe

23/7     PLS-00103: Encountered the symbol "VALMES" when expecting one of
         the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
         end not pragma final instantiable order overriding static
         member constructor map

23/34    PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         . ( ) , * % & = - + < / > at in is mod not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like
         between ||

SQL> l
  1  create or replace procedure  apt_owner.ActValidation (
  2    inList IN VARCHAR2
  3    , Valmes OUT varchar2
  4  )
  5  IS
  6            TYPE sys_refcursor IS REF CURSOR;
  7            cur sys_refcursor;
  8     Val     varchar2(400);
  9     Val0    varchar2(400);
 10  begin
 11        open cur for 'select distinct ACC_APT_I from pantheon_account where ACC_APT_I in (''' || replace(inList,',',''',''') || ''')';
 12  --('||inList|| ')';
 13        loop
 14              fetch cur into Val;
 15              Val0 := Val0  || Val || ',';
 16             exception
 17                          when no_data_found then
 18              Valmes := Valmes  || Val || ',';
 19
 20  --            exit when cur%notfound;
 21
 22        end loop;
 23        Valmes := rtrim(Valmes,',');
 24
 25* end;
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 380 total points
Comment Utility
sorry.  I missed this on the first one but you commented out the 'exit when' which will cause an infinite loop.

Back to the original question:

Borrowing some split code from:  http://builder.com.com/5100-6388-5259821.html

I came up with for following to return the 'invalid' numbers.  It's a little more work but it should get you there.
----------------------

drop table tab1;
create table tab1 ( col1 char(1) );

insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
commit;

create or replace procedure checkAcct(inList in varchar2, inValidList out varchar2)
is
      myCur sys_refcursor;
      myVal char(1);
      junk char(1);

    l_idx    pls_integer;
    l_list    varchar2(32767) := inList;
    l_value    varchar2(32767);
    p_del      char(1) := ',';
begin

    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            myVal := substr(l_list,1,l_idx-1);
            l_list := substr(l_list,l_idx+length(p_del));
        else
            myVal := l_list;
        end if;

            begin
              select 'x' into junk from tab1 where col1 = myVal;
              exception
                    when others then
                        dbms_output.put_line('not here: ');
                          inValidList := inValidList || myVal || ',';
        end;

        exit when l_idx <= 0;
    end loop;

    inValidList := rtrim(inValidList,',');
end;
/

show errors

var testOutput varchar2(100)

exec checkAcct('a,b,e,f',:testOutput);

print testOutput

0
 

Author Comment

by:laoqi
Comment Utility
Thank you very very much, slightwv, this really did the job. I learnt a lot from this post and I increased the points. The link is also very helpful.

Sorry in the delay in getting back to you -- besides absorbing the content here I'm also working on some production issue on our side.
0
 

Author Comment

by:laoqi
Comment Utility
I wanted to put slightwv's first response as the multiple solutions also, but once I hit the "accept" button the "multiple solution" botton doesn't appear any more. Anyway, that first response would also work if I just want a list of what's in the table.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now