Skip to Main Content
Forums

New to Java

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

convert from char to int

843785Jul 5 2008 — edited Jul 5 2008
hey
I've an input string ("1234")
What i need to do is to fill a matrix 2*2 with the input string.
I thought about running over the string, and by str.charAt(ind) to get a needed charters, but the question is ho do i convert charAt result to int?

tanx

Comments

Solomon Yakobson
Answer

You can use VARRAY:

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_i in 1..iv_req_id.count loop
select count(1) into lv_count from staging where req_id = iv_req_id(v_i);
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = iv_req_id(v_i)) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available


PL/SQL procedure successfully completed.


SQL> select * from target_tab;


    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2


SQL>

SY.

Marked as Answer by Albert Chao · Oct 25 2021
Albert Chao

@solomon-yakobson Can we do this using for loop cursor?

Solomon Yakobson

We could but why call SQL from PL/SQL if all can be done in PL/SQL?

create or replace procedure sp_main_target(iv_req_id IN sys.OdciNumberList)
is
lv_count number(10);
begin
for v_rec in (select * from table(iv_req_id)) loop
select count(1) into lv_count from staging where req_id = v_rec.column_value;
if lv_count > 0 then
dbms_output.put_line('Insertion into target table');
MERGE INTO target_tab t
   USING (SELECT key_id, inv_id, i_name, req_id FROM staging
   WHERE req_id = v_rec.column_value) S
   ON (t.inv_id = S.inv_id)
   WHEN MATCHED THEN UPDATE SET
   t.key_id = s.key_id,
   t.i_name = s.i_name,
   t.req_id = s.req_id
   WHEN NOT MATCHED THEN INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
     VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
else
dbms_output.put_line('Request id is not available');
end if;
end loop;
commit;
end sp_main_target;
/

Procedure created.

SQL> exec sp_main_target(sys.OdciNumberList(1,2,3));
Insertion into target table
Insertion into target table
Request id is not available

PL/SQL procedure successfully completed.

SQL> select * from target_tab;

    KEY_ID     INV_ID I_NAME                             REQ_ID
---------- ---------- ------------------------------ ----------
       110       1001 test1                                   1
       111       1002 test2                                   2

SQL>

SY.

Albert Chao

@solomon-yakobson Actually this stored procedure I am going to invoke from the front end so will this work? Front end wherein I will provide the request-id and process will happen. Or do we have to use connect by caluse for comma-separated inputs?

Solomon Yakobson

Front end is very generic statement? Is it Java, .Net, perl, python....? Anyway, you can use OracleArray in JDBC.
SY.

Albert Chao

@solomon-yakobson Thanks. Got it

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 2 2008
Added on Jul 5 2008
2 comments
426 views