CODEWITHSUNDEEP
oracle-databaseplsql
prudhvi bikumalla
prudhvi bikumalla

Reputation: 17

Inserting values into table using variable

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

Upvotes: 0

Views: 186

Answers (3)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

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>

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

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>

Upvotes: 1

Related Questions