This discussion is archived
13 Replies Latest reply: Dec 12, 2012 1:01 PM by AlbertoFaenza RSS

How to append input array parameter to a conditon in where clause

557534 Newbie
Currently Being Moderated
CREATE OR REPLACE PROCEDURE file_upload(p_array_code IN DIAG_CODE,p_desc_code IN DIAG_CODE_DESC,p_code_result OUT DIAG_CODE_TABLE) IS
v_count NUMBER;
v_range1 VARCHAR2(8);
v_range2 VARCHAR2(8);
l_count pls_integer := 0;
l_diag_code_table1 DIAG_CODE_TABLE;
l_diag_code_table2 DIAG_CODE_TABLE;

l_loop_diag_code_table1 DIAG_CODE_TABLE;
l_loop_diag_code_table2 DIAG_CODE_TABLE;


BEGIN
l_loop_diag_code_table1 := diag_code_table();
l_loop_diag_code_table2 := diag_code_table();

FOR i IN 1..p_array_code.count
LOOP

SELECT diag_code_rec(d.icd9_pcs_text_href,
d.icd9_procedure_deci_code,
d.icd9_procedure_long_desc,
d.icd9_pcs_gem_flag_desc,
g.gem_icd9_icd10pcs_flag,
dc.icd10_procedure_code,
dc.icd10_procedure_long_desc)
BULK COLLECT INTO l_diag_code_table2
FROM icd9_procedure_codes d,
icd10_procedure_codes dc ,
gem_icd9_icd10pcs g
WHERE d.icd9_procedure_code=g.gem_icd9_pcs_code
AND g.gem_icd10_pcs_code=dc.icd10_procedure_code(+)
AND d.ICD9_PROCEDURE_code like p_array_code(i)||'%' ;


FOR j IN 1..l_diag_code_table1.count
LOOP
l_loop_diag_code_table1.extend;
l_loop_diag_code_table1 (l_loop_diag_code_table1.last):= l_diag_code_table1(j) ;
END LOOP;


END LOOP;
p_code_result := l_loop_diag_code_table1 ;
END;
/

DIAG_CODE_DESC is declared as type
create or replace type DIAG_CODE_DESC as table of varchar2(300);

p_desc_code contains array of keywords

I need to add one more condition to the above select query using the p_desc_code as
(regexp_like(S.ICD9_PROCEDURE_LONG_DESC, '(p_desc_code (1)') AND regexp_like(S.ICD9_PROCEDURE_LONG_DESC, '(p_desc_code (2))' and ... upto inpu array limit.

Could anyone help me through in writing the above the sql with the additional condition?

I would really appreciate the help.

Thanks,
in advance
  • 1. Re: How to append input array parameter to a conditon in where clause
    sb92075 Guru
    Currently Being Moderated
    can only run SQL when we have tables & data which you did NOT provide for us.

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    ICD9_PROCEDURE_CODES _ table Name
    Coulmn name -- All are varchar columns
    I CD9_PROCEDURE_CODE
    ICD9_PROCEDURE_DECI_CODE
    ICD9_PROCEDURE_LONG_DESC
    ICD9_PROCEDURE_SHORT_DESC
    ICD9_VERSION
    ICD9_PCS_TEXT_HREF
    ICD9_PCS_GEM_FLAG_DESC

    Data for ICD9_PROCEDURE_CODES
    0441 04.41 Decompression trigem Scenaroio0 Scenario0:text_href
    0442 04.42 Other carnial Cran Scenario1 Scenario1: text_href


    ICD10_PROCEDURE_CODES - Table Name
    Column Name Varchar columns
    ICD10_PROCEDURE_CODE
    ICD10_PROCEDURE_SHORT_DESC
    ICD10_PROCEDURE_LONG_DESC
    ICD10_PCS_TEXT_HREF
    ICD10_PCS_GEM_FLAG_DESCRIPTION

    Data for icd10_procedure_codes
    00NK0ZZ Release Nerve Scenario0 Scenario0:text_href
    00NK3ZZ Diabetes Neuropathy Scenario11 Scenario11: text_href
    00NK4ZZ Nerve disorder Nerve malfunction Scenario12 Scenario12: text_href

    GEM_ICD9_ICD10PCS_FLAG -- Table name
    Coulmn Name -- Varchar
    GEM_ICD9_PCS_CODE
    GEM_ICD10_PCS_CODE
    GEM_ICD9_ICD10PCS_FLAG

    Data for gem_icd9_icd10pcs
    ('0441','00NK0ZZ','10000');
    ('0441','00NK3ZZ','10000');
    ('0441','00NK4ZZ','10000');

    Please find the above data and table description
  • 3. Re: How to append input array parameter to a conditon in where clause
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Ugly code.

    Using a loop to repeatedly execute a SQL is fundamentally wrong ito performance and scalability.

    Using an unconstrained bulk fetch - fetching all the output of the SQL cursor and stuffing this into expensive PGA memory - is also fundamentally wrong ito basic server health. You can do serious "damage" that way and cause severe swap daemon trashing as the kernel struggles to meet unrealistic memory demands.

    Using PL/SQL as a buffer cache for SQL data, is also fundamentally wrong - in all respects.

    I would not add code to this procedure. I would trash it. And then go back to the drawing board with the question - what are the requirements?
  • 4. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    Below are the requirements:

    A file will be uploaded from front end .
    Data from the file will be sent as arraya from java code:
    Two types of data
    1. values from file
    2. array of string values

    Sample data
    1000
    2000
    3000
    4000

    'abc','def',..upto 5

    I have to run the select query based on each value .

    Both the input values are varchar

    COuld you please advise me how to revise the current code and also how to dynamiically append where clause based on second input string?


    Thanks,
    in advance
  • 5. Re: How to append input array parameter to a conditon in where clause
    sb92075 Guru
    Currently Being Moderated
    how is it that you post to forum called SQL & PL/SQL;
    yet NEVER post any actual code in either language?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 6. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    I have posted both code and data format if you see the first 2 posts of this thread
  • 7. Re: How to append input array parameter to a conditon in where clause
    AlbertoFaenza Expert
    Currently Being Moderated
    user554531 wrote:
    I have posted both code and data format if you see the first 2 posts of this thread
    I think that Billy Verreynne has already given you the best suggestion regarding your code:
    I would not add code to this procedure. I would trash it. And then go back to the drawing board with the question - what are the requirements?
    Regards.
    Al
  • 8. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    Any advise how to rewrite the code will be greatly appreciated.

    Thanks
  • 9. Re: How to append input array parameter to a conditon in where clause
    AlbertoFaenza Expert
    Currently Being Moderated
    user554531 wrote:
    Any advise how to rewrite the code will be greatly appreciated.

    Thanks
    What about describing requirements? Please describe what you want to do with this procedure.

    The code you have posted is always returning an empty collection. So no idea of what you are trying to achieve.

    Also, as mentioned before, post CREATE TABLE and INSERT statement of some sample data.

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 11, 2012 10:08 PM
  • 10. Re: How to append input array parameter to a conditon in where clause
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user554531 wrote:
    Below are the requirements:

    A file will be uploaded from front end .
    Data from the file will be sent as arraya from java code:
    Two types of data
    1. values from file
    2. array of string values
    Why use Java to process the file? Oracle has SQL*Loader and supports files as external tables. Keep in mind the distance file data needs to travel in your approach. All the way from disk into Java across JDBC into PL/SQL and then to SQL and then finally to disk again.

    A lot of moving parts. Which means increased complexity and decreased performance.
    I have to run the select query based on each value .
    So if there are a million lines to process in the file, you need to execute a million SQLs. Bad idea.

    This approach has a 1:1 relationship between line loaded from file and making a PL/SQL call and running a SQL statement to process that line. This will never scale.

    Bigger file? Slower processing. Absolutely no way this approach will be able to handle increased file volumes.

     
    The better approach is to eliminate as many moving parts as possible. And that would be to ONLY use SQL. To read the file. To process a line. To insert/update the database. Nothing else.

    And this approach is possible using external tables, enabling you to read directly from that file using SQL. No PL/SQL needed. No Java required. For what it is worth - I load over 30 files per minutes and with around 150,000 lines (rows) per file. And I don't do this using PL/SQL. Or Java.
  • 11. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    Thankyou for providing the insight .
    My application is based on client server architecture where the user will upload the file and click the submit upon which file has to be read and results should display on the screen.

    We need to pass the results back to the screen.

    File will have maximum of 3000 rows .

    Current code will pass the result set to java where the data will read and dispalyed on screen
  • 12. Re: How to append input array parameter to a conditon in where clause
    557534 Newbie
    Currently Being Moderated
    Below are the requirements:

    A file will be uploaded from front end .
    Data from the file will be sent as arrays from java code:
    Two types of data
    1. values from file
    2. array of string values

    data format from file
    1.Sample data
    1000
    2000
    3000
    4000
    array of strings upto 5 strings limiy
    2.'abc','def',..upto 5

    Both the input values are varchar.

    create table ICD9_PROCEDURE_CODES(

    ICD9_Procedure_Code          Varchar2(6)     Primary Key,

    ICD9_Procedure_Deci_Code     Varchar2(7),     

    ICD9_Procedure_Long_Desc     Varchar2(300),     

    ICD9_Procedure_Short_Desc     Varchar2(100),     

    ICD9_PCS_Gem_Flag_Desc          Varchar2(500),     

    ICD9_Version               Varchar2(10));     

    insert into icd9_procedure_code(ICD9_PROCEDURE_CODE , ICD9_PROCEDURE_DECI_CODE,
    ICD9_PCS_TEXT_HREF, ICD9_PCS_GEM_FLAG_DESC)
    values(0441 ,04.41 ,Decompression trigem ,Scenaroio0 ,Scenario0:text_href)
    insert into icd9_procedure_code(ICD9_PROCEDURE_CODE , ICD9_PROCEDURE_DECI_CODE,
    ICD9_PCS_TEXT_HREF, ICD9_PCS_GEM_FLAG_DESC)
    0442, 04.42 ,Other carnial Cran ,Scenario1 ,Scenario1: text_href)

    create table ICD10_PROCEDURE_CODES(

    ICD10_PCS_Order_Num               Varchar2(7),     

    ICD10_Procedure_Code               Varchar2(8)     Primary Key,

    ICD10_Procedure_Decimal_Code          Varchar2(9),     

    ICD10_PCS_Code_Header               Number(1),     

    ICD10_Procedure_Short_Desc     Varchar2(60),

    ICD10_Procedure_Long_Desc     Varchar2(300),     

    ICD10_PCS_Gem_Flag_Description          Varchar2(500),     

    Version               Varchar2(10),

    );
    insert into icd10_procedure_codes
    ( ICD10_PROCEDURE_CODE, ICD10_PROCEDURE_LONG_DESC, ICD10_PCS_TEXT_HREF, ICD10_PCS_GEM_FLAG_DESCRIPTION)
    values

    (00NK0ZZ,00N.K0ZZ, Release Nerve, Scenario0, Scenario0:text_href
    00NK3ZZ, 00N.K3ZZ,Diabetes Neuropathy, Scenario11, Scenario11: text_href
    00NK4ZZ, 00NK.4ZZ,Nerve disorder, Scenario12 ,Scenario12: text_href


    create table GEM_ICD9_ICD10PCS(

    GEM_ICD9_PCS_Code          Varchar2(6),

    GEM_ICD10_PCS_Code          Varchar2(8),     

    GEM_ICD9_ICD10PCS_Flag          Number(5),     

    GEM_Version          Varchar2(10),

    constaraint GEM_ICD9_ICD10PCS_PK PRIMARY KEY (GEM_ICD9_PCS_Code,GEM_ICD10_PCS_Code,GEM_ICD9_ICD10PCS_Flag))

    insert into gem_icd9_icd10pcs (GEM_ICD9_PCS_Code,GEM_ICD10_PCS_Code,GEM_Version)
    values
    ('0441','00NK0ZZ','10000');
    ('0441','00NK3ZZ','10000');
    ('0441','00NK4ZZ','10000');

    Thanks for looking into this thread
  • 13. Re: How to append input array parameter to a conditon in where clause
    AlbertoFaenza Expert
    Currently Being Moderated
    Sorry, these are just input data and partial requirements. I only see input data, file and tables.
    What do you have to do with these?
    What is the expected output for the sample data you have provided?

    Also be sure that INSERT statements you have posted are valid. I see some missing quotes for varchar2 columns values. Have you tried to execute yourself what you have posted?

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 12, 2012 9:59 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points