OraFAQ Forum: Forms » Parse string delimited by new line

Home » Developer & Programmer » Forms » Parse string delimited by new line

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parse string delimited by new line [message #631194]

Sat, 10 January 2015 02:56

tedlaraghu
Messages: 56
Registered: December 2011

Member

Hi all,

I am new to Oracle Forms.

We have a requirement to add new functionality to existing form.

Forms version : 11.1.1.3.0

In the existing form, we need to add a new text field, within the text field user can enter one STYLE or if user wants to enter multiple STYLE's, then user needs to press F2 button in the new text field, when F2 is pressed it should open new pop-up window, there user can enter as many STYLES as needed, we expect user to enter 1 STYLE per line,even if user enters multiple STYLE's per line, we consider as one STYLE per line.

STYLE is varchar field in the database and 15 is the max length.

Sample data user may enter
****************************
AB12345
CD5678
ERTY5468
XYZ456,MNOP876544
12345
12/456
ABC-XYZ

we have dynamic SQL and we need to add STYLE condition to the existing SQL.

My question here is how should I develop the logic so every line entered in the new pop-up window is passed as input parameter to dynamic SQL.

existing dynamic SQL ||
'AND STYLE IN (''AB12345'',''CD5678'',''XYZ456,MNOP876544'')';

Report message to a moderator

Re: Parse string delimited by new line [message #631226 is a reply to message #631194]

Sat, 10 January 2015 11:35

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Keywords you are looking for is oracle rows to columns. There are different ways that enable you to do that - pick the one you find the most suitable to your environment.

By the way, if

and style in (...)

is to be used in Oracle, it is wrong - elements in IN list should be enclosed into single quotes, not double ones.

Report message to a moderator

Re: Parse string delimited by new line [message #631236 is a reply to message #631226]

Sat, 10 January 2015 15:06

tedlaraghu
Messages: 56
Registered: December 2011

Member

Thanks for your response Little Foot.

As the existing SQL is dynamic SQL, I was using double quotes.

My requirement is, user enters all the data in a text box, we need to parse each new line as one IN parameter of STYLE.

What is the data type I need to define for the STYLE in forms and can you please provide small example of how to do it.

I feel this requirement is different to Oracle rows to columns.

Thanks....

Report message to a moderator

Re: Parse string delimited by new line [message #631251 is a reply to message #631236]

Sun, 11 January 2015 13:25

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

On a second thought, you may be right - not exactly rows to columns problem.

As this text is entered into a text item (which is capable of accepting multilines), there's a "new line character" at the end of each line. That is either carriage return (CR) or carriage return / line feed (CR/LF). These would be CHR(10) and CHR(13). Basically, you have

AB12345<CR>
CD5678<CR>
...

Now, you have to replace <CR> with a character(s) you need - a comma and quotes. In order to do that, use REPLACE function. Try it in SQL*Plus first. Then, when you have a working solution, copy/paste it to a form.

Report message to a moderator

Re: Parse string delimited by new line [message #631278 is a reply to message #631251]

Mon, 12 January 2015 05:51

tedlaraghu
Messages: 56
Registered: December 2011

Member

I have tried below 3 scenario's.

Only 3rd one works, what is wrong with 1st two.

Please help me in understanding why 1st two doesn't work as expected.


declare
v_input_enames	varchar2(1000) := 'SMITH
ALLEN
WARD
jagan

JONES
'; 
v_enames   varchar2(1000) := ''''|| rtrim (rtrim(
        replace(v_input_enames,chr(10),''' ,''')
,''''),',');
v_count    number;
v_sql      varchar2(1000) := 'SELECT count(*) from emp where ename in (:names_list)';

begin
dbms_output.put_line('v_enames : ' || v_enames);
execute immediate v_sql
into v_count
using v_enames;
dbms_output.put_line('SQL query : ' || v_sql);
dbms_output.put_line('No of employees : ' || v_count);
end;
/
*****************************************************
declare
v_input_enames	varchar2(1000) := 'SMITH
ALLEN
WARD
jagan

JONES
'; 
v_enames   varchar2(1000) := ''''''|| rtrim (rtrim(
        replace(v_input_enames,chr(10),''''' ,''''')
,''''),',');
v_count    number;
v_sql      varchar2(1000) := 'SELECT count(*) from emp where ename in (:names_list)';
begin
dbms_output.put_line('v_enames : ' || v_enames);
execute immediate v_sql
into v_count
using v_enames;
dbms_output.put_line('SQL query : ' || v_sql);
dbms_output.put_line('No of employees : ' || v_count);
end;
/
declare
v_input_enames	varchar2(1000) := 'SMITH
ALLEN
WARD
jagan

JONES
'; 
v_enames   varchar2(1000) := ''''|| rtrim (rtrim(
        replace(v_input_enames,chr(10),''' ,''')
,''''),',');
v_count    number;
v_sql      varchar2(1000);
begin
dbms_output.put_line('v_enames : ' || v_enames);
v_sql :='SELECT count(*) from emp where ename in (' || v_enames || ')' ;
execute immediate v_sql
into v_count
;
dbms_output.put_line('SQL query : ' || v_sql);
dbms_output.put_line('No of employees : ' || v_count);
end;
/
*****************************************************

Report message to a moderator

Re: Parse string delimited by new line [message #631351 is a reply to message #631278]

Tue, 13 January 2015 00:10

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

See the difference between this code and yours (lines 16 and 20).

SQL> DECLARE
  2     v_input_enames   VARCHAR2 (1000) := 'SMITH
  3  ALLEN
  4  WARD
  5  jagan
  6
  7  JONES
  8  ';
  9     v_enames         VARCHAR2 (1000)
 10        :=    ''''
 11           || RTRIM (
 12                 RTRIM (REPLACE (v_input_enames, CHR (10), ''' ,'''), ''''),
 13                 ',');
 14     v_count          NUMBER;
 15     -- v_sql      varchar2(1000) := 'SELECT count(*) from emp where ename in (:names_list)';
 16     v_sql         VARCHAR2(1000) := 'SELECT count(*) from emp where ename in (' || v_enames || ')';
 17  BEGIN
 18     DBMS_OUTPUT.put_line ('v_enames : ' || v_enames);
 19
 20     EXECUTE IMMEDIATE v_sql INTO v_count; -- USING v_enames;
 21
 22     DBMS_OUTPUT.put_line ('SQL query : ' || v_sql);
 23     DBMS_OUTPUT.put_line ('No of employees : ' || v_count);
 24  END;
 25  /
v_enames : 'SMITH' ,'ALLEN' ,'WARD' ,'jagan' ,'' ,'JONES'
SQL query : SELECT count(*) from emp where ename in ('SMITH' ,'ALLEN' ,'WARD'
,'jagan' ,'' ,'JONES' )
No of employees : 4

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: Parse string delimited by new line [message #631538 is a reply to message #631351]

Wed, 14 January 2015 15:57

tedlaraghu
Messages: 56
Registered: December 2011

Member

Why execute immediate doesn't work when using clause is used in the 1st two scenario.

My first example uses single quote for v_enames and second example used double quotes for v_enames,
I feel one of the them should be working.

Report message to a moderator

Re: Parse string delimited by new line [message #631550 is a reply to message #631538]

Thu, 15 January 2015 02:54

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your dynamic SQL doesn't work as it's the equivalent of this static SQL:

DECLARE

v_input_enames  varchar2(1000) := 'SMITH
ALLEN
WARD
jagan

JONES
'; 
v_enames   varchar2(1000) := ''''|| rtrim (rtrim(
        replace(v_input_enames,chr(10),''' ,''')
,''''),',');
v_count    number;

BEGIN

SELECT count(*) INTO v_count FROM emp where ename in (v_enames);

END;

You can't directly use a variable to hold multiple items for an IN list.
Basically the comma seperators are syntax and so have to be in the query itself rather than any variable referenced by the query.

LF's approach fixes that problem as the final query oracle runs doesn't contain a variable for the list.

Report message to a moderator

Re: Parse string delimited by new line [message #631551 is a reply to message #631550]

Thu, 15 January 2015 03:02

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

To have it work with a USING clause you would need to do this:

DECLARE

v_input_ename1  varchar2(10) := 'SMITH';
v_input_ename2  varchar2(10) :='ALLEN';
v_input_ename3  varchar2(10) :='WARD';
v_input_ename4  varchar2(10) :='jagan';
v_input_ename5  varchar2(10) := '';
v_input_ename6  varchar2(10) :='JONES';
'; 
v_count    number;
v_sql      varchar2(1000) := 'SELECT count(*) from emp where ename in (:names1, :names2, :names3, :names4, :names5, :names6)';

BEGIN

execute immediate v_sql
into v_count
using v_input_ename1, v_input_ename2, v_input_ename3, v_input_ename4, v_input_ename5, v_input_ename66;

END;

But that requires knowing how many variables you might need and populating that appropriately.

The other way of doing this, without using execute immediate, is detailed here: varying in list

Report message to a moderator

Re: Parse string delimited by new line [message #631673 is a reply to message #631551]

Fri, 16 January 2015 21:33

tedlaraghu
Messages: 56
Registered: December 2011

Member

Thanks for the responses.

Report message to a moderator

Previous Topic:	Oracle Forms
Next Topic:	java version Issue to run Oracle Application from Browser

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:56:01 CDT 2024