Inserting values into table using variable

Question

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));

What do you mean by "3 different rows"? 3 records in the table or one record with 3 lines? — Wernfried Domscheit, Jul 16 '20 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. — prudhvi bikumalla, Jul 16 '20 at 11:44

score 1 · Answer 1 · answered Jul 16 '20 at 10:29

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>

answered Jul 16 '20 at 10:29

Littlefoot

131,892
15
35
57

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? – prudhvi bikumalla Jul 16 '20 at 10:39
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. – Littlefoot Jul 16 '20 at 10:52
Roberto used the same code as mine but I don't know how he got the output in 3 different rows in the table. – prudhvi bikumalla Jul 16 '20 at 11:09
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 – Roberto Hernandez Jul 16 '20 at 12:12

Roberto Hernandez · Answer 2 · 2020-07-16T15:10:11.740

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>

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 — prudhvi bikumalla, Jul 16 '20 at 11:05
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 ?? — Roberto Hernandez, Jul 16 '20 at 11:08
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. — prudhvi bikumalla, Jul 16 '20 at 11:12
one second, I will update the answer to put also how to do that — Roberto Hernandez, Jul 16 '20 at 11:40
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 — Roberto Hernandez, Jul 16 '20 at 12:06
yes its working properly just one small change is needed i guess. when you do select * from demo you are getting the same value repeated (B88207). Where should the change be done? — prudhvi bikumalla, Jul 16 '20 at 12:46
you are totally right. the last part of the loop is not getting the right value because it does not retrieve the last value. One second — Roberto Hernandez, Jul 16 '20 at 12:58
yes it is working fine for 3 values. thanks a lot Roberto. Thank you so much. But I have tried with 24 values with the below encoded string 'Qjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg0KQjg4MjA2DQpCODgyMDcNCkI3NzAxMg==' its not working properly. Can you please confirm? It is giving null values in between. — prudhvi bikumalla, Jul 16 '20 at 14:03
is there a way i can accept two answers? I had a very good discussion and proper inputs from you. it was helpful. Thanks a lot. — prudhvi bikumalla, Jul 16 '20 at 15:23
@prudhvibikumalla, unfortunately you can't. but thank you for your comments — Roberto Hernandez, Jul 16 '20 at 15:39

score 0 · Accepted Answer · answered Jul 16 '20 at 12:19

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.

Inserting values into table using variable

3 Answers3