Home » SQL & PL/SQL » SQL & PL/SQL » using Type in In list
using Type in In list [message #199445] Tue, 24 October 2006 06:23 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everybody,

[Updated on: Tue, 24 October 2006 09:21]

Report message to a moderator

Re: using Type in In list [message #199460 is a reply to message #199445] Tue, 24 October 2006 09:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

this might help without directly answering your question..
server the purpose thou, hope it helps

CREATE OR REPLACE FUNCTION test1
RETURN BOOLEAN
IS
CURSOR cur1
IS
SELECT constraint_name
FROM user_constraints
WHERE constraint_name LIKE 'PK%'
AND constraint_type IN ('P', 'C', 'R');

TYPE tab_type IS TABLE OF VARCHAR2 (30)
INDEX BY PLS_INTEGER;

my_tab tab_type;
v_done BOOLEAN;
BEGIN
OPEN cur1;

LOOP
FETCH cur1
BULK COLLECT INTO my_tab LIMIT 100;

v_done := cur1%NOTFOUND;
FORALL i IN 1 .. my_tab.COUNT
INSERT INTO test2
(constraint_name
)
VALUES (my_tab (i)
);
EXIT WHEN (v_done);
END LOOP;
RETURN TRUE;
END test1;

[Updated on: Tue, 24 October 2006 09:19]

Report message to a moderator

Re: using Type in In list [message #199525 is a reply to message #199445] Wed, 25 October 2006 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@bahy91
Why on earth did you remove your question???
Re: using Type in In list [message #199528 is a reply to message #199525] Wed, 25 October 2006 00:49 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Awful handwriting?
Re: using Type in In list [message #199544 is a reply to message #199528] Wed, 25 October 2006 01:33 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

OMG.....
Last time I put in some work for this guy :=/
Re: using Type in In list [message #200219 is a reply to message #199445] Sun, 29 October 2006 11:50 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi everyone,
i am really sorry if anything happen while i was working on updating my question ,well i will return to the question of the Type but right now am really busy with the SEQUENCES nextval and currval ...
if anyone can hep me with this error i really appreciate it..
thanks in advance

My probem with sequence is that i try to get the currval in a function so i tried the following:
CREATE OR REPLACE FUNCTION fn_GetCrval(p_Seqname IN VARCHAR2) RETURN PLS_INTEGER
IS
str VARCHAR2(32767):=UPPER(p_Seqname);
v PLS_INTEGER;
BEGIN
SELECT str||'.NEXTVAL' INTO v FROM dual;
RETURN v;
END;

and when i tried to get the sequence currval ..Like this:
DECLARE
vs PLS_INTEGER;
BEGIN
vs:=fn_GetCrval('SEQ_CONTENTS_NR')-1;
DBMS_OUTPUT.PUT_LINE('the value is:'||vs);
END;
i've got this error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

is there anyting that make it easy to get the currval in a function..

thanks for everyone
bahy91
Re: using Type in In list [message #200509 is a reply to message #200219] Tue, 31 October 2006 03:27 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are getting this error because you are trying to select the string 'SEQ_CONTENTS_NR.NEXTVAL' into a pls_integer value.

You need to rewrite your function like this:
CREATE OR REPLACE FUNCTION fn_GetCrval(p_Seqname IN VARCHAR2) RETURN PLS_INTEGER IS
  v   PLS_INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT '||p_seqname||'.NEXTVAL FROM dual' INTO v;
RETURN v;
END;
Previous Topic: regarding dbms_job
Next Topic: query help
Goto Forum:
  


Current Time: Mon Dec 05 13:18:41 CST 2016

Total time taken to generate the page: 0.05625 seconds