Uncategorized

Array in oracle

DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray(‘Kavita’, ‘Pritam’, ‘Ayan’, ‘Rishav’, ‘Aziz’);
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line(‘Total ‘|| total || ‘ Students’);
FOR i in 1 .. total LOOP
dbms_output.put_line(‘Student: ‘ || names(i) || ‘
Marks: ‘ || marks(i));
END LOOP;
END;

split a string to array

/* Formatted on 13-Mar-2015 16:09:21 (QP5 v5.126) */
DECLARE
bar VARCHAR2 (200) := ‘1,2,3’;
BEGIN
SELECT param_value
INTO bar
FROM import_item
WHERE ROWNUM = 1;

FOR v_param IN ( SELECT REGEXP_SUBSTR (bar,
‘[^,]+’,
1,
LEVEL)
txt
FROM DUAL
CONNECT BY REGEXP_SUBSTR (bar,
‘[^,]+’,
1,
LEVEL) IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (foo.txt);

FOR fooo IN ( SELECT REGEXP_SUBSTR (foo.txt,
‘[^:]+’,
1,
LEVEL)
txtt
FROM DUAL
CONNECT BY REGEXP_SUBSTR (foo.txt,
‘[^:]+’,
1,
LEVEL) IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (fooo.txtt);
END LOOP;
END LOOP;
END;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s