Home » SQL & PL/SQL » SQL & PL/SQL » getting variable's size runtime ? is it possible in pl/sql (9i)
getting variable's size runtime ? is it possible in pl/sql [message #339296] Thu, 07 August 2008 04:20 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #339304 is a reply to message #339296] Thu, 07 August 2008 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use "table1.field1%type" to define your variables and so have no problem about sizing.

Regards
Michel
Re: getting variable's size runtime ? is it possible in pl/sql [message #339307 is a reply to message #339304] Thu, 07 August 2008 04:43 Go to previous messageGo to next message
rohitupgzp
Messages: 11
Registered: August 2008
Junior Member
Hi Michel,

you cannot use "table1.field1%type" with substr function.

var1 := substr(var2,1,20);

i want to replace 20 with dynamic information.
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Is It Possible To define a size of the Procedure Parametere?
Next Topic: Query approach
Goto Forum:
  


Current Time: Wed Dec 07 06:35:34 CST 2016

Total time taken to generate the page: 0.13900 seconds