asked on

PL/SQL, Array,

i need to pass 10 item number and item description to a proc from informatica and concatenate those values into a string and then pass that string to another proc which will send the string to 'mq'. the second proc which will send the string to 'mq' via pl/sql is already ready... can you help me write a proc which will accept 10 inputs into a proc? do i use a array..?

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

informatica will send the item number and item description to my proc when it updates the iventory items table...the number of items that informatica can send can vary, one day it might send 1, on another day it might send 20... my proc has to accept all and concatenate the item_no and item_desc into a string of varcahr2(3000) and if the string is longer than 3000 then the proc has to concatenate another string no longer than 3000. the second proc which sends the string to mq has a limitation of 3000 bytes...

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

i beleive informatica can send all data types that pl/sql can use..

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

that is the direction we are thinking of going... informatica can easily create a flat file with item_no and item_desc stringed together and will pass it to my proc...

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

yes.....
item_no is number(10) and item_desc is varchar2(240)...

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rao_S

ASKER

i forgot informatica will send one more field item_operation of varchar2(1) which will have I (for insert) or U (for update)...

Rao_S

ASKER

the proc which will send to 'mq' will need varchar2...type...
this is how the data might look....
I;9620000004;HR_PICKLE TEMPER_MULT_C_SH|XLS-XCC-70-01-34-05;I;9600000073;HR_PICKLED_NON_TR_COIL_C_SH|XLS-XCC-70-01-34;

Guy Hengel [angelIII / a3]

so, would this article help?
https://www.experts-exchange.com/A_1536.html

slightwv (䄆 Netminder)

Can informatica in this clob separate each transaction on an individual row?

I;9620000004;HR_PICKLE TEMPER_MULT_C_SH|XLS-XCC-70-01-34-05;
I;9600000073;HR_PICKLED_NON_TR_COIL_C_SH|XLS-XCC-70-01-34;
etc...

I'm having a problem with a logical delimiter. If you can insert a cr, lf or cr/lf, I can work with that.

Also,
What is a likely average size for the CLOB?

Rao_S

ASKER

we found informatica cannot send values to a proc in which i have defined a array... so instead had informatica send a concatenated string as file, the proc will accept the string and process and send the msg to 'mq'...