[SQL] Error when using array variable
I use a function using array variable as following. The function returns array type. When array variable is allocated with any value,allocating is not done. I can't find why it is. --- CREATE OR REPLACE FUNCTION arr_test() RETURNS NUMERIC[] AS $BODY$ DECLARE v_count NUMERIC default 0; v_dayIndex NUMERIC default 0; t_modifiedTimes NUMERIC[]; v_testval NUMERIC default 0; BEGIN for v_count in 1..5 loop v_dayIndex := v_dayIndex + 1; t_modifiedTimes[v_dayIndex ] := v_count; v_testval := t_modifiedTimes[v_dayIndex ] ; -->배열에 저장된 값 임시저장 raise exception '임의에러생성 t_modifiedTimes[v_dayIndex ]=', v_testval ; -->강제적으로 exception 발생시킴 end loop; return t_modifiedTimes; END; $BODY$ LANGUAGE plpgsql;
[SQL] Error when using array variable
I use a function using array variable as following. The function returns array type. When array variable is allocated with any value,allocating is not done. I can't find why it is. --- CREATE OR REPLACE FUNCTION arr_test() RETURNS NUMERIC[] AS $BODY$ DECLARE v_count NUMERIC default 0; v_dayIndex NUMERIC default 0; t_modifiedTimes NUMERIC[]; v_testval NUMERIC default 0; BEGIN for v_count in 1..5 loop v_dayIndex := v_dayIndex + 1; t_modifiedTimes[v_dayIndex ] := v_count; v_testval := t_modifiedTimes[v_dayIndex ] ; -->배열에 저장된 값 임시저장 raise exception '임의에러생성 t_modifiedTimes[v_dayIndex ]=', v_testval ; -->강제적으로 exception 발생시킴 end loop; return t_modifiedTimes; END; $BODY$ LANGUAGE plpgsql;
[SQL] Using subselects as joins in POstgeSQL (possible?, examples)
Is it possible to use subselects as joins in PostgreSQL. eg. select a.a, a.b, a.c, b.a, b.b. b.c from (first subsselect) a (second subselect) b where (in table criteria) and a.a = b.a or select a.a, a.b, a.c, b.a, b.b. b.c from (first subsselect) a join (second subselect) b on a.a = b.a where (in table criteria) I have a feeling it is possible but I need the right syntax //Frank This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Are long term never commited SELECT statements are a pr
You may get problems. At least we did.
Having a long term transaction which seemingly just was one Begin with
nothing, we encountered a siginifficant decrease of performance after some
days (70 tx/sec)
During that the pg_subtrans dir filled up with files and the IO-reads of the
disk as well. After closing the particular connection the normal performance
reappeared.
And the files in pg_subtrans became one file again.
So far.
BTW. You will see the DB with a glasses that shows you the time when the
transaction started.
|-Original Message-
|From: Erik Wasser [mailto:[EMAIL PROTECTED]
|Sent: Donnerstag, 21. Juli 2005 15:58
|To: pgsql-sql@postgresql.org
|Subject: [SQL] Are long term never commited SELECT statements are a
|problem?
|
|
|Hello List,
|
|I've written an application in perl using DBI with MySQL (no
|transaction
|support). Then we decide to migrate it to postgresql
|(postgresql-8.0.1-r4).
|
|At first we were using 'AutoCommit => 1' with the application. That
|means that every statement will be commited right away.
|
|Then I discovered the 'magic of transactions' and set AutoCommit to 0.
|Then I rewrite many UPDATE and INSERT statements with support for
|commit and rollback. BUT: the SELECT statements were untouched (and
|that was mistake I think).
|
|Now I've got here a blocking problem. Severel SQL statements (like
|renaming a field or UPDATE of a field) are blocked until I kill a
|certain task. This task DOES only the INSERTS and UPDATES with a
|transaction and the SELECT statements are not within an transaction.
|And this task is a long term running task (some kind of daemon) so the
|SELECT transactions will never be commited. Are long term never
|commited SELECT statements are a problem and could that lead to
|blocking other queries?
|
|To put it in annother way: what kind of thing I produced with the
|following pseudocode?
|
|# open database
|$DBH = DBI->connect(...,USERNAME,PASSWORD, { RaiseError => 1,
|AutoCommit
|=> 0 });
|
|while (true)
|{
| # do some select
| SELECT ... FROM ...
| # do some more
| SELECT ... FROM ...
|
| if (condition)
| # do an UPDATE/INSERT
| eval {
| UPDATE/INSERT/...
| $DBH->commit;
| };
| if ($@) {
| warn "Transaction aborted: $@";
| eval { $DBH->rollback };
| }
| }
|}
|
|Is this some kind of nested transaction? Can there be a problem with
|this code?
|
|Thanks for your help!
|
|--
|So long... Fuzz
|
|---(end of
|broadcast)---
|TIP 2: Don't 'kill -9' the postmaster
|
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Using subselects as joins in POstgeSQL (possible?, examples)
On 7/22/05, frank church <[EMAIL PROTECTED]> wrote: Is it possible to use subselects as joins in PostgreSQL. Absolutely. select * from (select * from table1) as a, (select * from table2) as b where a.a = b.b
Re: [SQL] Error when using array variable
On Fri, Jul 22, 2005 at 06:24:03PM +0900, Dongsoo Yoon wrote: > > When array variable is allocated with any value, > allocating is not done. It's not clear what this means -- what are you expecting to happen, and what actually is happening? > v_testval := t_modifiedTimes[v_dayIndex ] ;-->?? > ?? ?? > > raise exception ' t_modifiedTimes[v_dayIndex ]=', > v_testval ; Is this RAISE just for debugging purposes? Are you aware that you need to include a % character in the format string if you want to display the subsequent argument's value? http://www.postgresql.org/docs/8.0/static/plpgsql-errors-and-messages.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Convert numeric to money
Hi. I have searched in mailing-list archives about converting types, but I couldn't found a function or clause that convert a numeric type to money type. How Can I convert this types? => select '1234'::money; money R$1.234,00 => select '1234'::numeric::money; ERROR: cannot cast type numeric to money The problem is becouse I have a table with "numeric" field, and I need to show it like "money" type (R$ 1.234,00). Is there a function to convert it??? Or is there a function to mask the numeric field to show like money (with R$x.xxx,xx)??? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Error when using array variable
On 7/22/05, Dongsoo Yoon <[EMAIL PROTECTED]> wrote: I use a function using array variable as following. The function returns array type. When array variable is allocated with any value,allocating is not done. I can't find why it is. --- CREATE OR REPLACE FUNCTION arr_test() RETURNS NUMERIC[] AS $BODY$ DECLARE v_count NUMERIC default 0; v_dayIndex NUMERIC default 0; t_modifiedTimes NUMERIC[]; v_testval NUMERIC default 0; BEGIN for v_count in 1..5 loop v_dayIndex := v_dayIndex + 1; t_modifiedTimes[v_dayIndex ] := v_count; You need to initialse the array, Otherwise any value added to the array will be null. v_testval := t_modifiedTimes[v_dayIndex ] ; -->배열에 저장된 값 임시저장 raise exception '임의에러생성 t_modifiedTimes[v_dayIndex ]=', v_testval ; -->강제적으로 exception 발생시킴 end loop; return t_modifiedTimes; END; $BODY$ LANGUAGE plpgsql; -- with regards,S.GnanavelSatyam Computer Services Ltd.
[SQL] Multi-column returns from pgsql
Hi Everyone, Does anyone know if/how it's possible to return multi-column sets from a pgsql function? Right now I'm using something like the following as a work around CREATE OR REPLACE FUNCTION my_func() returns SETOF TEXT AS ' DECLARE rec record; BEGIN FOR rec IN SELECT txt1, txt2 FROM mytable LOOP RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; END LOOP; RETURN; END;' language 'plpgsql'; which leaves me parsing multiple records to achieve the desired end result. Anyone have any thoughts? Thanks, Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Multi-column returns from pgsql
Mark, Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT rec; then your select statement would be select * from my_func() as (txt1 text,txt2 text); Jim -- Original Message --- From: "Mark R. Dingee" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Fri, 22 Jul 2005 11:49:21 -0400 Subject: [SQL] Multi-column returns from pgsql > Hi Everyone, > > Does anyone know if/how it's possible to return multi-column sets from a > pgsql > function? Right now I'm using something like the following as a work around > > CREATE OR REPLACE FUNCTION my_func() returns SETOF TEXT AS ' > DECLARE > rec record; > BEGIN > FOR rec IN SELECT txt1, txt2 FROM mytable LOOP >RETURN NEXT rec.txt1; >RETURN NEXT rec.txt2; > END LOOP; > RETURN; > END;' language 'plpgsql'; > > which leaves me parsing multiple records to achieve the desired end result. > > Anyone have any thoughts? > > Thanks, > Mark > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match --- End of Original Message --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Convert numeric to money
On Fri, Jul 22, 2005 at 11:03:40 -0300, [EMAIL PROTECTED] wrote: > Hi. > I have searched in mailing-list archives about converting types, but I > couldn't > found a function or clause that convert a numeric type to money type. > How Can I convert this types? > > => select '1234'::money; >money > > R$1.234,00 > > => select '1234'::numeric::money; > ERROR: cannot cast type numeric to money > > The problem is becouse I have a table with "numeric" field, and I need to show > it like "money" type (R$ 1.234,00). Is there a function to convert it??? Or is > there a function to mask the numeric field to show like money (with > R$x.xxx,xx)??? You probably want to use to_char to convert the numeric value to a string which can be displayed. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Multi-column returns from pgsql
On 7/22/05, Jim Buttafuoco <[EMAIL PROTECTED]> wrote: > Mark, > > Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT > rec; > > then your select statement would be > select * from my_func() as (txt1 text,txt2 text); > > Jim Besides a simple RETURN NEXT, you'll need to return a SETOF some composite type. You can do something like CREATE TYPE twotexts_t AS (txt1 TEXT, txt2 TEXT); CREATE OR REPLACE FUNCTION my_func() returns SETOF twotexts_t AS ' DECLARE rec record; BEGIN FOR rec IN SELECT txt1, txt2 FROM mytable LOOP RETURN NEXT END LOOP; RETURN; END;' language 'plpgsql'; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Tigger
I've look throught the docs and from what I can see the bellow code should work, however I keep getting the error: ERROR: parser: parse error at or near "$" at character 53 CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ BEGIN -- Check date exists IF NEW.stamp_lastupdate IS NULL THEN NEW.stamp_lastupdate := 'now'; END IF; RETURN NEW; END; $session_update$ LANGUAGE plpgsql; CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW EXECUTE PROCEDURE session_update(); Any help or suggestions of websites I should read would be appercated. David _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Tigger
David Hofmann wrote: > I've look throught the docs and from what I can see the bellow code > should work, however I keep getting the error: > > ERROR: parser: parse error at or near "$" at character 53 > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > BEGIN > -- Check date exists > IF NEW.stamp_lastupdate IS NULL THEN > NEW.stamp_lastupdate := 'now'; > END IF; > RETURN NEW; > END; > > $session_update$ LANGUAGE plpgsql; > > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR > EACH ROW EXECUTE PROCEDURE session_update(); > > > Any help or suggestions of websites I should read would be appercated. > > David Which version of postgresql are you using? I don't believe that the "$" quoting was available in older versions than 8 (or late 7?). -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Tigger
David Hofmann wrote: > I'm using 7.3. > >> From: Bricklen Anderson <[EMAIL PROTECTED]> >> To: David Hofmann <[EMAIL PROTECTED]> >> CC: pgsql-sql@postgresql.org >> Subject: Re: [SQL] Tigger >> Date: Fri, 22 Jul 2005 12:17:41 -0700 >> >> David Hofmann wrote: >> > I've look throught the docs and from what I can see the bellow code >> > should work, however I keep getting the error: >> > >> > ERROR: parser: parse error at or near "$" at character 53 >> > >> > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ >> > BEGIN >> > -- Check date exists >> > IF NEW.stamp_lastupdate IS NULL THEN >> > NEW.stamp_lastupdate := 'now'; >> > END IF; >> > RETURN NEW; >> > END; >> > >> > $session_update$ LANGUAGE plpgsql; >> > >> > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR >> > EACH ROW EXECUTE PROCEDURE session_update(); >> > >> > >> > Any help or suggestions of websites I should read would be appercated. >> > >> > David >> >> Which version of postgresql are you using? I don't believe that the >> "$" quoting >> was available in older versions than 8 (or late 7?). I don't think that it worked then. Simple test: CREATE FUNCTION session_update() RETURNS trigger AS ' BEGIN -- Check date exists IF NEW.stamp_lastupdate IS NULL THEN NEW.stamp_lastupdate := ''now''; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Tigger
I'm using 7.3. From: Bricklen Anderson <[EMAIL PROTECTED]> To: David Hofmann <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Tigger Date: Fri, 22 Jul 2005 12:17:41 -0700 David Hofmann wrote: > I've look throught the docs and from what I can see the bellow code > should work, however I keep getting the error: > > ERROR: parser: parse error at or near "$" at character 53 > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > BEGIN > -- Check date exists > IF NEW.stamp_lastupdate IS NULL THEN > NEW.stamp_lastupdate := 'now'; > END IF; > RETURN NEW; > END; > > $session_update$ LANGUAGE plpgsql; > > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR > EACH ROW EXECUTE PROCEDURE session_update(); > > > Any help or suggestions of websites I should read would be appercated. > > David Which version of postgresql are you using? I don't believe that the "$" quoting was available in older versions than 8 (or late 7?). -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Tigger
David Hofmann wrote: > > I've look throught the docs and from what I can see the bellow code > > should work, however I keep getting the error: > > > > ERROR: parser: parse error at or near "$" at character 53 > > > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > > BEGIN > > -- Check date exists > > IF NEW.stamp_lastupdate IS NULL THEN > > NEW.stamp_lastupdate := 'now'; > > END IF; > > RETURN NEW; > > END; > > > > $session_update$ LANGUAGE plpgsql; > > > > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR > > EACH ROW EXECUTE PROCEDURE session_update(); > > > > > > Any help or suggestions of websites I should read would be appercated. > > > > David > > Which version of postgresql are you using? I don't believe that the "$" > quoting > was available in older versions than 8 (or late 7?). > the $ quoting is available since 8. This seems bad to me also: > > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ > > [..function body..] > > $session_update$ LANGUAGE plpgsql; I think it should be: CREATE FUNCTION session_update() RETURNS trigger AS $$ [..function body..] $$ LANGUAGE plpgsql; -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Tigger
Jaime Casanova wrote: > This seems bad to me also: > >>>CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ >>>[..function body..] >>>$session_update$ LANGUAGE plpgsql; > > > I think it should be: > CREATE FUNCTION session_update() RETURNS trigger AS $$ > [..function body..] > $$ LANGUAGE plpgsql; > No, the identifier between the $$ is legit, providing you're at v8 and above. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Tigger
It didn't error out, however the function didn't work. Specificly the if statement. I took out the if statement and it started updating, I put it back in and it fails to update. Not sure why. I've confirmed that the program not attempting to update the stamp_lastupdate field. Here what I end up with that worked. CREATE or REPLEACE FUNCTION session_update() RETURNS trigger AS ' BEGIN -- Check date exists NEW.stamp_lastupdate := ''now''; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR EACH ROW EXECUTE PROCEDURE session_update(); I appercated the help Bricklen. David From: Bricklen Anderson <[EMAIL PROTECTED]> To: David Hofmann <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Tigger Date: Fri, 22 Jul 2005 12:28:32 -0700 David Hofmann wrote: > I'm using 7.3. > >> From: Bricklen Anderson <[EMAIL PROTECTED]> >> To: David Hofmann <[EMAIL PROTECTED]> >> CC: pgsql-sql@postgresql.org >> Subject: Re: [SQL] Tigger >> Date: Fri, 22 Jul 2005 12:17:41 -0700 >> >> David Hofmann wrote: >> > I've look throught the docs and from what I can see the bellow code >> > should work, however I keep getting the error: >> > >> > ERROR: parser: parse error at or near "$" at character 53 >> > >> > CREATE FUNCTION session_update() RETURNS trigger AS $session_update$ >> > BEGIN >> > -- Check date exists >> > IF NEW.stamp_lastupdate IS NULL THEN >> > NEW.stamp_lastupdate := 'now'; >> > END IF; >> > RETURN NEW; >> > END; >> > >> > $session_update$ LANGUAGE plpgsql; >> > >> > CREATE TRIGGER session_update BEFORE INSERT OR UPDATE ON sessions FOR >> > EACH ROW EXECUTE PROCEDURE session_update(); >> > >> > >> > Any help or suggestions of websites I should read would be appercated. >> > >> > David >> >> Which version of postgresql are you using? I don't believe that the >> "$" quoting >> was available in older versions than 8 (or late 7?). I don't think that it worked then. Simple test: CREATE FUNCTION session_update() RETURNS trigger AS ' BEGIN -- Check date exists IF NEW.stamp_lastupdate IS NULL THEN NEW.stamp_lastupdate := ''now''; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Error when using array variable
On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote: > On 7/22/05, Dongsoo Yoon <[EMAIL PROTECTED]> wrote: > > CREATE OR REPLACE FUNCTION arr_test() > > RETURNS NUMERIC[] AS $BODY$ > > DECLARE > > v_count NUMERIC default 0; > > v_dayIndex NUMERIC default 0; > > t_modifiedTimes NUMERIC[]; > > v_testval NUMERIC default 0; > > > > BEGIN > > > > for v_count in 1..5 loop > > v_dayIndex := v_dayIndex + 1; > > > t_modifiedTimes[v_dayIndex ] := v_count; > > You need to initialse the array, Otherwise any value added to the array will > be null. This isn't necessary in 8.0, which we can infer is being used because of the dollar quotes. See the 8.0 Release Notes: http://www.postgresql.org/docs/8.0/static/release-8-0.html "Updating an element or slice of a NULL array value now produces a non-NULL array result, namely an array containing just the assigned-to positions." I don't recall if early 8.0 versions had any bugs in this respect, but it does indeed work in 8.0.3. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error when using array variable
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote: >> You need to initialse the array, Otherwise any value added to the array will >> be null. > This isn't necessary in 8.0, which we can infer is being used because > of the dollar quotes. However, plpgsql wasn't fixed to follow that behavior till 8.0.2 or so. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Is there a type like a growable array, similar Vector at Java language in postgreSQL?
In Oracle, there is a type like a growable array, similar Vector at Java language. In postgreSQL, is there any type like bellow type? -CREATE OR REPLACE PROCEDURE test( p_size in number ,p_proccode out varchar2 ,p_procmesg out varchar2)IS v_count number default 0; v_dayIndex number default 0; v_size number default 0; type tb_NumTable is table of number(2) index by binary_integer;>like a growable array t_modifiedTimes tb_NumTable;--->declare a variable using above defined type. BEGIN v_size := nvl(p_size, 0); for v_count in 1..v_size loop v_dayIndex := v_dayIndex + 1; t_modifiedTimes[v_dayIndex ] := v_count; end loop; p_proccode := 0; p_procmesg := 'OK'; EXCEPTION WHEN OTHERS THEN p_proccode := SQLCODE; p_procmesg := SUBSTR(SQLERRM, 1, 255); end test;
