| Trouble creating proc [message #389273] |
Sat, 28 February 2009 00:03  |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
Gurus,
Need a stored procedure for the requirment mentioned :
I am having a column which gets values through an external application which is in the form of string '1234~2345~3456~4567'
so on and so forth...Actually, 1234 forms a part of a column value.There will be maximum of 25 such values which will be concatenated by token '~'.Now, the problem is
1)To check whether length of string which will be less than varchar2 maximum value of 32767 (as it may be a possibility).
1.1)Extraction of file names one by one and inserting them into another table.
Also,this will be applicable other way round like when sending file name concatenated by '~' which will also limit the length of out variable by 32767 so that it will start populationg the second out variable.
Table A:
Session Id Column_1
---------- -------------------------------------------------------------
1111 1234~2345~3456~4567
22222 2345~3456~5678~67897545~6546878921~4874654314
33333 3456213~46768765~465486541~15799399333~976646249633~4657981
Now each of rows has a column value of other table say Table B
Session Id Column_11
---------- ---------
1111 1234
1111 2345
1111 3456
1111 4567
22222 2345
22222 3456
22222 5678
22222 67897545
22222 6546878921
22222 4874654314
33333 3456213
33333 46768765
33333 465486541
33333 15799399333
33333 976646249633
33333 4657981
like wise..
|
|
|
|
|
|
| Re: Trouble creating proc [message #389278 is a reply to message #389275] |
Sat, 28 February 2009 00:24  |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
There are 2 tables one used by application and other used by database.Structures is given inthe script
create table table_1_appln (reqd_session number(10),file_name varchar2(32767))
create table table_2_act (reqd_session number(10),file_name varchar2(32767))
Session Id File_Name
---------- -------------------------------------------------------------
1111 1234~2345~3456~4567
22222 2345~3456~5678~67897545~6546878921~4874654314
33333 3456213~46768765~465486541~15799399333~976646249633~4657981
I need 2 procedures
i)One will insert the delimitized file names into main data base as is given below :
Session Id File_Name
---------- ---------
1111 1234
1111 2345
1111 3456
1111 4567
22222 2345
22222 3456
22222 5678
22222 67897545
22222 6546878921
22222 4874654314
33333 3456213
33333 46768765
33333 465486541
33333 15799399333
33333 976646249633
33333 4657981
ii)One which will output the filenames for a session id to the application.
Session Id File_Name
---------- -------------------------------------------------------------
1111 1234~2345~3456~4567
22222 2345~3456~5678~67897545~6546878921~4874654314
33333 3456213~46768765~465486541~15799399333~976646249633~4657981
Things to take care while creating procedures is
1)Length of filename can exceed the normal maximum capacity of varchar2 datatype i.e 32767.
If it does , then for the second procedure while the file name is outputted start populating second out variable.
|
|
|
|
| Re: Trouble creating proc [message #389281 is a reply to message #389278] |
Sat, 28 February 2009 00:38 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If the data come from a file, use external table.
SQL> create table table_1_appln (reqd_session number(10),file_name varchar2(32767))
2 /
create table table_1_appln (reqd_session number(10),file_name varchar2(32767))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
Regards
Michel
[Updated on: Sat, 28 February 2009 00:39] Report message to a moderator |
|
|
|
| Re: Trouble creating proc [message #389289 is a reply to message #389273] |
Sat, 28 February 2009 02:03 |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
1 point left to mention:
Maxmimum filenames coming form application would be restricted to 25.Hence, there will be max 24 occurrances of '~'.
If varchar2 does not work ,then may be using long data type data could be inserted.
|
|
|
|
|
|
| Re: Trouble creating proc [message #389295 is a reply to message #389273] |
Sat, 28 February 2009 04:31 |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
|
I think 'table_1_appln' table is creating confusion as this is just eample to explain the problem.The string '1234~2345~3456~4567' will be actually coming from application as inputs.Please don't get confused with table_1_appln table.
|
|
|
|
|
|
| Re: Trouble creating proc [message #389354 is a reply to message #389273] |
Sun, 01 March 2009 07:48 |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
String is actually coming by application which gives filenames which are actually represented here by 1234,2345,etc.
Gurus,I really need you help on this one.
|
|
|
|
| Re: Trouble creating proc [message #389359 is a reply to message #389354] |
Sun, 01 March 2009 09:29 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You don't understand my question: where is the data when you want to do what you want to do?
Where is '1234~2345~3456~4567' when you want to put it in your table(s)?
From where your "procedure" will get the string?
Who will execute the "procedure"? The application that generates the data or another one?
Regards
Michel
[Updated on: Sun, 01 March 2009 09:31] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Trouble creating proc [message #389406 is a reply to message #389273] |
Sun, 01 March 2009 22:38 |
|
|
Hi,
I dont think that i am spamming here, i just wanted to give the solution like you people,
If by mistake i click twice and 2 replies displayed here, i am sorry for it.
but i know that i am not here to spam !!
Regards,
Ashoka BL
Bengaluru
|
|
|
|
|
|
| Re: Trouble creating proc [message #389497 is a reply to message #389273] |
Mon, 02 March 2009 05:57 |
J1357
Messages: 33
Registered: November 2008
|
Member |
|
|
The user checks the file names he wants from the application and the application gives the file names to database.
Now 1)There will be max 25 files in concatenation which would be parsed by character '~'.
2) These file name need to be chipped into single filenames
which in turn would be inserted into another table_b.
Gurus,kindly help with the code.
|
|
|
|
| Re: Trouble creating proc [message #389500 is a reply to message #389497] |
Mon, 02 March 2009 06:08 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
It seems that you have already been pointed towards several potential routes to a solution. have you looked at any of them. Do they look like they might suit. If not, can you explain why it is that they are not suitable?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
