Home » SQL & PL/SQL » SQL & PL/SQL » Is there any limit for stored procedure's arguments?
Is there any limit for stored procedure's arguments? [message #186703] Wed, 09 August 2006 02:50 Go to next message
cavinlam83
Messages: 24
Registered: August 2006
Location: MALAYSIA
Junior Member

I found that I only can accept certain limit of arguments in stored procedure. Once over the limit, I cannot execute it. Any solution? Can I change the setting for stored procedure? Thanks
Re: Is there any limit for stored procedure's arguments? [message #186806 is a reply to message #186703] Wed, 09 August 2006 12:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you actually hitting this limit?
As an excercise, I've just created a stored procedure with over 3000 input parameters (3073 to be precise, and then I ran out of space in the VC2 I was using.I could probably have got up to 3075, perhaps 3076, but I didn't see the point.)

If your system has procedures larger than that then you truely have my sympathy.

But no, the limit (whatever it is) is built into pl/sql and will not be user definable.
Re: Is there any limit for stored procedure's arguments? [message #186809 is a reply to message #186703] Wed, 09 August 2006 12:30 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

JRowbottom,
I think you have lot of Patience budddy... Laughing
But its always good to test before putting on board...

Naveen
Re: Is there any limit for stored procedure's arguments? [message #186811 is a reply to message #186809] Wed, 09 August 2006 12:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I didn't do it by hand!!!!

DECLARE
  v_sql   VARCHAR2(32767);
  v_max_iter  PLS_INTEGER := 3073;
BEGIN
  v_sql := 'create or replace procedure test_inp_lim ( ';
  FOR i IN 1..v_max_iter LOOP
    v_sql := v_sql||'c'||trim(TO_CHAR(i))||' date';
    IF i< v_max_iter THEN
      v_sql := v_sql||',';
    ELSE
      v_sql := v_sql||') as begin null; end;';
    END IF;
  END LOOP;
  
  EXECUTE IMMEDIATE v_sql;
END;
Re: Is there any limit for stored procedure's arguments? [message #186855 is a reply to message #186811] Wed, 09 August 2006 22:11 Go to previous message
cavinlam83
Messages: 24
Registered: August 2006
Location: MALAYSIA
Junior Member

Thanks, I found that INSERT statement in Oracle SQL has a limit of 999 arguments. Therefore, I cannot create a variable-length array of more than 999 elements using the SDO_GEOMETRY constructor (spatial) inside a transactional INSERT statement; however, I also found that I can insert a geometry using a host variable, and the host variable can be built using the SDO_GEOMETRY constructor with more than 999 values in the SDO_ORDINATE_ARRAY specification.

But the problem is I dun know how to build a host variable using the SDO_GEOMETRY constructor with more than 999 values in the SDO_ORDINATE_ARRAY specification. Can somebody help me?? Thanks.
Previous Topic: SQL help needed
Next Topic: Can I sort value in array ?
Goto Forum:
  


Current Time: Sun Dec 04 08:53:18 CST 2016

Total time taken to generate the page: 0.05258 seconds