Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql code
pl/sql code [message #210821] Fri, 22 December 2006 08:37 Go to next message
pavan_034
Messages: 16
Registered: October 2006
Junior Member
HOW TO PRINT ALL POSSIBLE COMBINATIONS OF A STRING(A WORD)
USING PL/SQL? FOR EXAMPLE IF YOU GIVE "PUT" AS INPUT , THE PROGRAM SHOULD RETURN PUT,PTU,TPU,TUP,UPT,UTP.
Re: pl/sql code [message #210849 is a reply to message #210821] Fri, 22 December 2006 11:38 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Pavan,

I have tried and one am sending you one solution:

1. create a table
create table st(one_st char(3));

2. insert into st values('PUT');
commit;

3. write this procedure

Quote:
create or replace procedure one_string
is
fir st.one_st%type;
sec st.one_st%type;
thi st.one_st%type;
cursor c1 is select one_st from st;
begin
for r1 in c1
loop

fir := substr(r1.one_st,1,1);
sec := substr(r1.one_st,2,1);
thi := substr(r1.one_st,3,1);

dbms_output.put_line(fir||sec||thi);
dbms_output.put_line(fir||thi||sec);
dbms_output.put_line(thi||fir||sec);
dbms_output.put_line(thi||sec||fir);
dbms_output.put_line(sec||thi||fir);
end loop;
end;
/


4. exec one_string

You will get the desired output.

Bye
Ashu
Re: pl/sql code [message #210852 is a reply to message #210849] Fri, 22 December 2006 11:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Ashu, it looks like you forgot to include the combination:

dbms_output.put_line(sec||fir||thi)


BTW Pavan, what version of Oracle are you working on ?
Re: pl/sql code [message #210855 is a reply to message #210852] Fri, 22 December 2006 11:57 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi ebrian,

thanks for recalling the combination . where r u from?

bye
Ashu
Re: pl/sql code [message #210857 is a reply to message #210855] Fri, 22 December 2006 12:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You're welcome Ashu. I'm from the US.
Re: pl/sql code [message #211364 is a reply to message #210849] Thu, 28 December 2006 03:43 Go to previous messageGo to next message
pavan_034
Messages: 16
Registered: October 2006
Junior Member
HI ASHU,
THANKS FOR UR REPLY.BUT DON'T YOU THINK YOU ARE MANUALLY ENTERING THE COMBINATIONS IN UR PROCEDURE? IF U HAVE ANY OTHER SOLUTION PLEASE SEND IT TO ME.ANY WAY THANKS AGAIN.
Re: pl/sql code [message #211687 is a reply to message #211364] Sun, 31 December 2006 14:04 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I can't think of a really elegant solution, but you could try this:

CREATE OR REPLACE PROCEDURE combinations
    ( p_str VARCHAR2 )
IS
    k_length CONSTANT PLS_INTEGER := LENGTH(p_str);
    v_column_list  LONG;
    v_table_list   LONG;
    v_where_clause LONG;

    v_sql LONG :=
        'WITH r AS ( SELECT rownum rn, SUBSTR(:str,rownum,1) AS ch'  || CHR(10) ||
                    'FROM  dual CONNECT BY rownum <= LENGTH(:str) )' || CHR(10) ||
        'SELECT DISTINCT r1.ch #column_list# AS combo'               || CHR(10) ||
        'FROM   r r1 #table_list#'                                   || CHR(10) ||
        'WHERE  #where_clause#';

    c_results SYS_REFCURSOR;
    v_result VARCHAR2(500);
BEGIN
    FOR i IN 2..k_length LOOP
        v_column_list := v_column_list || ' || r' || i || '.ch';
        v_table_list  := v_table_list  || ', r r' || i;

        v_where_clause := v_where_clause || CHR(10) || 'AND r' || i || '.rn NOT IN(';

        FOR j IN 1..i -1 LOOP
            v_where_clause := v_where_clause || 'r' || j || '.rn, ';
        END LOOP;

        v_where_clause := RTRIM(v_where_clause,', ') || ')';
    END LOOP;

    v_sql := REPLACE(v_sql,' #column_list#',v_column_list);
    v_sql := REPLACE(v_sql,' #table_list#',v_table_list);
    v_sql := REPLACE(v_sql,' #where_clause#',SUBSTR(v_where_clause,5));

    -- ECHO(v_sql,100);

    OPEN c_results FOR v_sql USING p_str,p_str;

    LOOP
        FETCH c_results INTO v_result;
        EXIT WHEN c_results%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_result);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || c_results%ROWCOUNT || ' combinations found.');
    CLOSE c_results;
END;
/

Procedure created.

SQL> exec combinations('EARS');
RSEA
SAER
SREA
ASER
SRAE
AERS
ARES
RESA
ERSA
ESRA
AESR
ASRE
REAS
RAES
EASR
ESAR
SARE
RSAE
ERAS
ARSE
RASE
SERA
EARS
SEAR

24 combinations found.

PL/SQL procedure successfully completed.


[Updated on: Mon, 01 January 2007 08:31]

Report message to a moderator

Re: pl/sql code [message #211724 is a reply to message #211687] Mon, 01 January 2007 21:28 Go to previous messageGo to next message
pavan_034
Messages: 16
Registered: October 2006
Junior Member
It is working!Thanks for the code Mr.Williams.

thanks and regards
PAVAN
Re: pl/sql code [message #211741 is a reply to message #211724] Tue, 02 January 2007 00:53 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try http://www.orafaq.com/forum/mv/msg/64650/177099/78693/#msg_177099 for v9i
or http://www.orafaq.com/forum/mv/msg/64650/177291/78693/#msg_177506 for 10g

Ross Leishman
Previous Topic: 1 statement to delete , update 2 tables
Next Topic: Query to get 5th Max salary
Goto Forum:
  


Current Time: Fri Dec 09 07:40:05 CST 2016

Total time taken to generate the page: 0.06482 seconds