Home » SQL & PL/SQL » SQL & PL/SQL » Trouble creating proc (10g)
Trouble creating proc [message #389273] Sat, 28 February 2009 00:03 Go to next message
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 #389275 is a reply to message #389273] Sat, 28 February 2009 00:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Trouble creating proc [message #389278 is a reply to message #389275] Sat, 28 February 2009 00:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #389294 is a reply to message #389289] Sat, 28 February 2009 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then may be using long data type data could be inserted.

No use CLOB.

Regards
Michel
Re: Trouble creating proc [message #389295 is a reply to message #389273] Sat, 28 February 2009 04:31 Go to previous messageGo to next message
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 #389329 is a reply to message #389295] Sat, 28 February 2009 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The string '1234~2345~3456~4567' will be actually coming from application as input

How does it come? file?

Regards
Michel
Re: Trouble creating proc [message #389354 is a reply to message #389273] Sun, 01 March 2009 07:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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 #389383 is a reply to message #389273] Sun, 01 March 2009 13:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
Search http://asktom.oracle.com for his str2tbl or in_list function. That will enable you to separate the delimited values in table a and insert them into table b.

Re: Trouble creating proc [message #389403 is a reply to message #389273] Sun, 01 March 2009 22:21 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

This would help you, have a look at this.

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

Regards,
Ashoka BL
Re: Trouble creating proc [message #389405 is a reply to message #389273] Sun, 01 March 2009 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Ashoka BL,

stop spamming this thread
Re: Trouble creating proc [message #389406 is a reply to message #389273] Sun, 01 March 2009 22:38 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #389426 is a reply to message #389406] Mon, 02 March 2009 00:32 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have deleted a duplicate message.

Forum, lately, looks like it is going to die so many people (especially new members) unintentionally, due to lack of experience and "Your session has expired" messages post more than one message (or open many topics).

That's how it is. Moderators can (and do) clean up this mess when necessary. If our reputable member switched over to his moderator account, he'd be able to do the same. In the meantime, thank you for the notice; no need to make such a fuss about it.
Re: Trouble creating proc [message #389497 is a reply to message #389273] Mon, 02 March 2009 05:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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?

Re: Trouble creating proc [message #389514 is a reply to message #389273] Mon, 02 March 2009 06:52 Go to previous message
J1357
Messages: 33
Registered: November 2008
Member
Yes Got the real answer here :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2189860818012

Thanks gurus for all your inputs.
Previous Topic: Get all days for a month, along with Week, Day when first, last day are passed (merged 5)
Next Topic: time differnce between two databases (merged 3)
Goto Forum:
  


Current Time: Mon Dec 05 18:44:45 CST 2016

Total time taken to generate the page: 0.16024 seconds