• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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..?
0
Rao_S
Asked:
Rao_S
6 Solutions
 
Mark GeerlingsDatabase AdministratorCommented:
You may be able to use an array if Informatica can construct one and pass that to PL\SQL. (I don't use Informatica, so I don't know what it can do).  Or, you could declare your procedure to accept 20 input parameters (10 of type number, and 10 of type varchar2) then concatenate them in the procedure into a single string to pass on to the next procedure.
0
 
slightwv (䄆 Netminder) Commented:
What data types can Informatica use?

Since you have 10 and only 10, why not just 10 separate paramaters?

create or replace procedure myproc(p1 in char(1), p2 in char(1), ... p10 in char(1))
is
...

0
 
Rao_SAuthor Commented:
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Again, what data types can Informatica use/pass to Oracle?

We need to know what Oracle data types we can use for the input parameters.
0
 
Rao_SAuthor Commented:
i beleive informatica can send all data types that pl/sql can use..
0
 
slightwv (䄆 Netminder) Commented:
informatica cannot create the CSV in a clob?  then you just need a proc to split the CSV into 3000 character chunks and call the other proc.

This would be a lot easier than creating a database TYPE of a VARRAY or a TABLE of objects.
0
 
Rao_SAuthor Commented:
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...
0
 
slightwv (䄆 Netminder) Commented:
So you are looking for a proc that accepts a CLOB as an input variable, splits up the clob into a max of 3000 character chunks and calls another proc?

Is item_no fixed witdh?  if not what is the max width?
0
 
Rao_SAuthor Commented:
yes.....
item_no is number(10) and item_desc is varchar2(240)...
0
 
slightwv (䄆 Netminder) Commented:
I missed the 'two' columns.

What format does the proc with a max of 3000 characters need?

Please provide some sample data and expected results.
0
 
Rao_SAuthor Commented:
i forgot informatica will send one more field item_operation of varchar2(1) which will have I (for insert) or U (for update)...
0
 
Rao_SAuthor Commented:
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;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, would this article help?
http://www.experts-exchange.com/A_1536.html
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
Rao_SAuthor Commented:
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'...
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now