0

I have an encoded string 'Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg=='. I was able to decode it and save those values into a variable and insert the values into a table.

My code:

set serveroutput on


declare
output varchar(255);
function to_base64(t in varchar2) return varchar2 is
begin
return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
end to_base64;
function from_base64(t in varchar2) return varchar2 is
begin
return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
end from_base64;
begin
output:=from_base64('Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg==');
dbms_output.put_line(output);
insert into demo(column_1) values(output);
commit;
end;
/

Output for this code is :

COLUMN_1
B88206B88207B77012

But Required Output is: Values should go into 3 different rows.

COLUMN_1
B88206
B88207
B77012.

What changes do I need to make for the required output?

Create statement for table demo:

create table demo(column_1 varchar(255));
Improve this question
2
  • What do you mean by "3 different rows"? 3 records in the table or one record with 3 lines? Commented Jul 16, 2020 at 11:41
  • yes, 3 records in table and the length of the string in the output variable is not fixed. It can even be 600, in that case, the number of records should be 100. Commented Jul 16, 2020 at 11:44

3 Answers 3

Reset to default
1

Split it; SUBSTR seems to be simple & efficient in this case:

SQL> with test (col) as
  2    (select 'B88206B88207B77012' from dual)
  3  select substr(col,  1, 6) val1,
  4         substr(col,  7, 6) val2,
  5         substr(col, 13, 6) val3
  6  from test;

VAL1   VAL2   VAL3
------ ------ ------
B88206 B88207 B77012

SQL>

If it has to be 3 rows (as you said), then:

SQL> with test (col) as
  2    (select 'B88206B88207B77012' from dual)
  3  select substr(col,  1, 6) val from test union all
  4  select substr(col,  7, 6) val from test union all
  5  select substr(col, 13, 6) val from test;

VAL
------------------------------
B88206
B88207
B77012

SQL>
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I thought of substr. But even though the length on each row is 6. But we won't be knowing the length of the input it can be 600 also. In that case, how can it be done?
You'd split it as you want; though, as Roberto says, it seems that you - actually - don't have to do anything. See his example.
Roberto used the same code as mine but I don't know how he got the output in 3 different rows in the table.
the answer is because you have a chr(13) return carriage in the row, that is why the output looks like three rows, when actually is one. I updated my original answer to cover a way to split the content in as many rows as needed using regexp_substr
0

Try this one:

insert into demo(column_1)
select regexp_substr(output, '[^'||CHR(10)||CHR(13)||']+', 1, level) 
from dual
connect by regexp_substr(output, '[^'||CHR(10)||CHR(13)||']+', 1, level) is not null

Note, utl_encode.base64_decode is limited to 32k characters, in case your strings could be longer see Base64 encoding and decoding in oracle

However, in this case REGEXP_SUBSTR may also fail, I did not test.

Improve this answer

Comments

0

You actually don't need to do anything. The splitting is the expected behaviour because your value contains the carriage return as character. You are getting one row that contains two chr(13) characters , that is why it looks like 3 rows when actually is one.

Oracle 12.2

SQL> create table demo ( column_1 varchar2(4000) ) ;

Table created.

SQL>  declare
 output varchar(255);
 function to_base64(t in varchar2) return varchar2 is
 begin
 return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
 end to_base64;
  2    3    4    5    6    7   function from_base64(t in varchar2) return varchar2 is
 begin
 return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
 end from_base64;
 begin
 output:=from_base64('Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg==');
 dbms_output.put_line(output);
 insert into demo(column_1) values(output);
 commit;
 end;
  8    9   10   11   12   13   14   15   16   17  /
B88206
B88207
B77012

PL/SQL procedure successfully completed.

SQL> select * from demo ;

COLUMN_1
--------------------------------------------------------------------------------
B88206
B88207
B77012


SQL> select dump(column_1) from demo ;

DUMP(COLUMN_1)
--------------------------------------------------------------------------------
Typ=1 Len=22: 66,56,56,50,48,54,13,10,66,56,56,50,48,55,13,10,66,55,55,48,49,50

SQL> select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(column_1))) from demo ;

UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(COLUMN_1))
--------------------------------------------------------------------------------
Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg==

SQL>

However, if you want to get different rows, you only need to split by using the regexp_substr function, as the character in this case is chr(13). I modified your original pl/sql block to include this feature

SQL> select * from demo ;

no rows selected

SQL> declare
output varchar(4000);
counter pls_integer;
v_curr_val varchar(4000);
function to_base64(t in varchar2) return varchar2 is
 begin
 return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
 end to_base64;
function from_base64(t in varchar2) return varchar2 is
 begin
 return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
 end from_base64;
begin
  output:=from_base64('Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg==');
     dbms_output.put_line(output);
     insert into demo values ( output );
     commit;
     counter :=  regexp_count(output , chr(13) , 1 , 'i' ) + 1;
     dbms_output.put_line('counter is '||counter||' ');
     for var in 1 .. counter 
     loop 
        if var=1 
        then
            v_curr_val := regexp_substr( output, '[^'||CHR(10)||CHR(13)||']+' , 1, 1 );
        elsif var < counter
        then
            v_curr_val :=  regexp_substr( output, '[^'||CHR(10)||CHR(13)||']+' , 1, var );
        end if;
        insert into demo values ( v_curr_val );
    end loop;
     commit;
     end;
    /

    B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
B88206
B88207
B77012
counter is 24

PL/SQL procedure successfully completed.

SQL> select count(*) from demo ;

  COUNT(*)
----------
        24

SQL>
Improve this answer

13 Comments

its the same code i used. But when you do select * from table how are you getting it in 3 different rows? I am getting it in single row
it is the very same code. I did not change a line of it. It is the normal behaviour of utl_raw in this case. sqlplus shows three split values , but it is one row and one value. which oracle version do you use ??
As you mentioned it is one row and one value i want it as 3 rows and 1 column. If the length of decoded string is 600 then it should be 100 rows and 1 column.
one second, I will update the answer to put also how to do that
my answer now covers the conversion to as many rows as needed by the original value. Please, if you agreed, upvote and accept the answer. thank you
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.