| getting variable's size runtime ? is it possible in pl/sql [message #339296] |
Thu, 07 August 2008 04:20  |
rohitupgzp
Messages: 11
Registered: August 2008
|
Junior Member |
|
|
Hi,
let us take a senario
var1 varchar2(20);-- can be table1.field1 also which is
varchar2(20)
var2 varchar2(40);
now
var2 := 'fourty characters';
var1 := substr(var2,1,20);
Now instead of hardcoding 20 in the substr while assigning it to var1, I want something to look at var1 and tell ...hey this can take only 20 and i can replace 20 with that function.
Motto : If I reduce the size of var1 .i.e to
var1 varchar2(10);
it should not give me error while assigning any values to var1 but should be smart enough to take only 10 chars.
Regards
Rohit Sinha
|
|
|
|
| Re: getting variable's size runtime ? is it possible in pl/sql [message #339299 is a reply to message #339296] |
Thu, 07 August 2008 04:26  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You can use a variable for the length-parameter of the substr. This variable can be filled from user_tab_columns.
However, I personally think this is very bad designing. First of all, you normally don't go around altering (lowering even!) sizes of columns.
Secondly, the idea that you simply, without checking chop off a part of the information kind of scares me..
|
|
|
|
| Re: getting variable's size runtime ? is it possible in pl/sql [message #339301 is a reply to message #339299] |
Thu, 07 August 2008 04:39 |
rohitupgzp
Messages: 11
Registered: August 2008
|
Junior Member |
|
|
That's correct,
but it will need access to user_tab_columns for the user.
Also for your scare let me put it another way
if I increase the length of var1 i.e
var1 varchar2(50);
it should be able to accept more string but
var1 := substr(var2,1,20);
because of hard code it will still accept 20... this will not give an error but you have to change it to
var1 := substr(var2,1,40);
to get more info...
|
|
|
|
|
|
|
|
| Re: getting variable's size runtime ? is it possible in pl/sql [message #339312 is a reply to message #339307] |
Thu, 07 August 2008 04:47 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Option 1) Define a numeric variable for each string variable that you have that defines it's maximum length, and then use this variable in the SUBSTR
Option 2) If the variable is based on a table column then get the length from user_tab_columns
Option 3) If your variable name is unique in the package, you could look in User_Source for the length that it is defined to, bu I wouldn't recommend it.
Other than that, there is no way in Pl/Sql of finding the maximum length of a variable.
|
|
|
|
| Re: getting variable's size runtime ? is it possible in pl/sql [message #339336 is a reply to message #339307] |
Thu, 07 August 2008 05:25 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| rohitupgzp wrote on Thu, 07 August 2008 11:43 | Hi Michel,
you cannot use "table1.field1%type" with substr function.
var1 := substr(var2,1,20);
i want to replace 20 with dynamic information.
|
I didn't say that, I said DECLARE the variables using that.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
