CODEWITHSUNDEEP
databaseoracle-databaseplsqlplsqldeveloper
Data-Base
Data-Base

Reputation: 8598

PL/SQL converts name-value pairs to single row table

Using PL/SQL, how do I convert a table of name-value pairs like this...

Name      Value
--------- -------
Firstname   Bob
Surname     Smith
Address1    101 High Street
City        London
Country     UK

...to a single row table like this:

Firstname Surname Address1        City   Country
--------- ------- --------------- ------ -------
Bob       Smith   101 High Street London UK

actually what I need is to convert the name-value pairs to a SYS_REFCURSOR as the single row table above

The full story is: I receive the data as a single string like this (Firstname;Bob;Surname;SmithAddress1;101 High Street;City;London;Country;UK) all I need is to convert this to a SYS_REFCURSOR

I started by creating types

TYPE order_type IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
neworder order_type;

so far I managed to do so with name-value pairs

is it a good approach?

Upvotes: 0

Views: 997

Answers (2)

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

came across an interesting utility I didn't know about. dbms_utility.comma_to_table. http://mohsoracle.blogspot.com/2010/04/oracle-breaking-comma-separated-string.html

so if you wanted to use it to return a sys ref cursor from a delimited string maybe something like.

/* Formatted on 10/28/2016 9:42:52 AM (QP5 v5.256.13226.35510) */
CREATE OR REPLACE PROCEDURE myproc (v_cur OUT SYS_REFCURSOR)
IS
   lv_Str_List   VARCHAR2 (1000)
      := 'Firstname, Bob, Surname, Smith, Address, 101 High Street, City, London, Country, UK';
   lb_cnt        BINARY_INTEGER;
   la_Tab_Str    DBMS_UTILITY.UNCL_ARRAY;
BEGIN
   lv_Str_List := '"' || REPLACE (lv_Str_List, ',', '","') || '"';
   -- parse the string into comma separated table
   DBMS_UTILITY.COMMA_TO_TABLE (lv_Str_List, lb_cnt, la_Tab_Str);

   FOR i IN 1 .. la_Tab_Str.COUNT
   LOOP
      -- display substring
      DBMS_OUTPUT.PUT_LINE (TRIM (la_Tab_Str (i)));
   END LOOP;

   OPEN v_cur FOR
      SELECT *
        FROM (SELECT REPLACE (TRIM (la_Tab_Str (2)), '"', '') AS Firstname,
                     REPLACE (TRIM (la_Tab_Str (4)), '"', '') AS Surname,
                     REPLACE (TRIM (la_Tab_Str (6)), '"', '') AS Address,
                     REPLACE (TRIM (la_Tab_Str (8)), '"', '') AS City,
                     REPLACE (TRIM (la_Tab_Str (10)), '"', '') AS Country
                FROM DUAL) t;
END;

I hard coded in the string but it could come in as a variable then replace your semicolons with commas.

when I ran the following in toad to test it.

DECLARE 
  V_CUR SYS_REFCURSOR;

BEGIN 
  V_CUR := NULL;

  MYPROC ( V_CUR );
  :to_grid := V_CUR;
  COMMIT; 
END;

I got

FIRSTNAME SURNAME  ADDRESS           CITY     COUNTRY
Bob       Smith    101 High Street   London   UK

Upvotes: 0

Kacper
Kacper

Reputation: 4818

create table t1 (name varchar2(20), value varchar2(20));
insert into t1 values('Firstname','Bob');
insert into t1 values('Surname','Smith');
insert into t1 values('Address1','101 High Street');
insert into t1 values('City','London');
insert into t1 values('Country','UK');

select * from t1 pivot (max(value) for (name) in ('Firstname' as firstname, 'Surname' as surname, 'Address1' as address, 'City' as city, 'Country' as country));

Such SQL converts column data into one row.

If you get your data as a separated string with fixed order I would just parse it using combination of instr('Firstname;Bob;Surname;Smith;Address1;101 High Street;City;London;Country;UK',';',1) and substring function.

Something like:

declare
text varchar2(200) := 'Firstname;Bob;Surname;Smith;Address1;101 High Street;City;London;Country;UK';
firstname varchar2(40);
surname varchar2(40);
address varchar2(40);
city varchar2(40);
country varchar2(40);
begin
firstname := substr(text,instr(text,';',1) + 1,instr(text,';',2) - instr(text,';',1));
surname := substr(text,instr(text,';',3) + 1, instr(text,';',4) - instr(text,';',3));
....
end;

Upvotes: 1

Related Questions