Solved

Oracle stored procedure input string with variable length/field

Posted on 2007-03-23
10
1,479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18781352
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
ID: 18783006
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
ID: 18791583
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18792297
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
ID: 18793383
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18793940
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
ID: 18794207
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 380 total points
ID: 18795733
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
ID: 18802319
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
ID: 18802359
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

630 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