Home » SQL & PL/SQL » SQL & PL/SQL » how to call the below function in select query
how to call the below function in select query [message #187798] Tue, 15 August 2006 12:41 Go to next message
sitaj99
Messages: 1
Registered: August 2006
Location: Washington DC
Junior Member

/*This function accepts comma separated columns and converts the string to
company_tabcol_list_typ VARRAY in the OUT parameter. Uses DBMS_UTILITY.comma_to_column
function to convert comma separated string to an ARRAY. Returns 0 if it successfully
*/
convers or 1 if it fails to convert.
/* Comma separated Table.Column list in a string */
FUNCTION string2varray (
p_string IN VARCHAR2,
p_tabcol_list OUT NOCOPY company_tabcol_list_typ
)
RETURN PLS_INTEGER
IS
i_count BINARY_INTEGER;
my_table DBMS_UTILITY.uncl_array;
v_tabcol_list company_tabcol_list_typ := company_tabcol_list_typ ();
v_success PLS_INTEGER := 0;
v_fail PLS_INTEGER := 1;
v_modulename VARCHAR2 (64) := 'string2varray';
v_errmessage VARCHAR2 (2000);
BEGIN
v_errmessage := 'Unable to convert Comma to Table. ';
DBMS_UTILITY.comma_to_table (p_string, i_count, my_table);
v_errmessage := 'Unable to populate v_tabcol_list VARRAY.';

IF LENGTH (p_string) > 0 THEN
FOR i IN 1 .. i_count LOOP
v_tabcol_list.EXTEND;
v_tabcol_list (i) := my_table (i);
END LOOP;

p_tabcol_list := v_tabcol_list;
ELSE
p_tabcol_list := company_tabcol_list_typ ();
END IF;

RETURN v_success;
EXCEPTION
WHEN OTHERS THEN
v_errmessage := SQLERRM;
p_tabcol_list := company_tabcol_list_typ ();
RETURN v_fail;
END;


Re: how to call the below function in select query [message #187830 is a reply to message #187798] Tue, 15 August 2006 21:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So what happens when you call it in a SELECT? If it fails, it might be because the function returns PLS_INTEGER. Try returning NUMBER instead.

Ross Leishman
Re: how to call the below function in select query [message #187874 is a reply to message #187798] Wed, 16 August 2006 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your function has an OUT parameter, so you aren't ever going to be calling it from SQL.

When you ran it, did you get an error like:

ORA-06572: Function STRING2VARRAY has out arguments

Re: how to call the below function in select query [message #188574 is a reply to message #187798] Sat, 19 August 2006 21:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You would not call it in a select query. You would call it in pl/sql, as shown below.

SCOTT@10gXE> CREATE OR REPLACE TYPE company_tabcol_list_typ AS VARRAY (10) OF VARCHAR2 (30);
  2  /

Type created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION string2varray (
  2    p_string 	     IN VARCHAR2,
  3    p_tabcol_list OUT NOCOPY company_tabcol_list_typ
  4    )
  5    RETURN PLS_INTEGER
  6  IS
  7    i_count			BINARY_INTEGER;
  8    my_table 		DBMS_UTILITY.uncl_array;
  9    v_tabcol_list		company_tabcol_list_typ := company_tabcol_list_typ ();
 10    v_success		PLS_INTEGER := 0;
 11    v_fail			PLS_INTEGER := 1;
 12    v_modulename		VARCHAR2 (64) := 'string2varray';
 13    v_errmessage		VARCHAR2 (2000);
 14  BEGIN
 15    v_errmessage := 'Unable to convert Comma to Table. ';
 16    DBMS_UTILITY.comma_to_table (p_string, i_count, my_table);
 17    v_errmessage := 'Unable to populate v_tabcol_list VARRAY.';
 18  
 19    IF LENGTH (p_string) > 0 THEN
 20  	 FOR i IN 1 .. i_count LOOP
 21  	   v_tabcol_list.EXTEND;
 22  	   v_tabcol_list (i) := my_table (i);
 23  	 END LOOP;
 24  
 25  	 p_tabcol_list := v_tabcol_list;
 26    ELSE
 27  	 p_tabcol_list := company_tabcol_list_typ ();
 28    END IF;
 29  
 30    RETURN v_success;
 31  EXCEPTION
 32    WHEN OTHERS THEN
 33  	 v_errmessage := SQLERRM;
 34  	 p_tabcol_list := company_tabcol_list_typ ();
 35  	 RETURN v_fail;
 36  END;
 37  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> DECLARE
  2    v_cols	 company_tabcol_list_typ;
  3    v_success PLS_INTEGER;
  4  BEGIN
  5    v_success := string2varray ('deptno,dname,loc', v_cols);
  6    DBMS_OUTPUT.PUT_LINE (v_success);
  7    FOR i IN 1 .. v_cols.COUNT LOOP
  8  	 DBMS_OUTPUT.PUT_LINE (v_cols(i));
  9    END LOOP;
 10  END;
 11  /
0
deptno
dname
loc

PL/SQL procedure successfully completed.

SCOTT@10gXE> 

Re: how to call the below function in select query [message #188893 is a reply to message #188574] Tue, 22 August 2006 05:12 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Can we implement this way.


DECLARE
v_cols company_tabcol_list_typ;
v_success PLS_INTEGER;
BEGIN
v_success := string2varray ('deptno,dname,loc', v_cols);
DBMS_OUTPUT.PUT_LINE (v_success);

FOR cur IN(SELECT * FROM TABLE(CAST(v_cols AS company_tabcol_list_typ)) X)
LOOP
DBMS_OUTPUT.PUT_LINE (Cur.*****) Do not how to access this value Can anyone give some idea on this
END LOOP;
END;
/

Re: how to call the below function in select query [message #188977 is a reply to message #188893] Tue, 22 August 2006 11:20 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
cur.column_value
Re: how to call the below function in select query [message #189107 is a reply to message #188977] Wed, 23 August 2006 04:33 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
But here the type is created as below and we do no not have a column value to be refered.

CREATE OR REPLACE TYPE company_tabcol_list_typ AS VARRAY (10) OF VARCHAR2 (30);

Probably we can refer the same by cur.V_cols(1). Can I ?

Thanks
Re: how to call the below function in select query [message #189215 is a reply to message #189107] Wed, 23 August 2006 11:51 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Like Todd said, literally, "COLUMN_VALUE", as demonstrated below.

SCOTT@10gXE> CREATE OR REPLACE TYPE company_tabcol_list_typ AS VARRAY (10) OF VARCHAR2 (30);
  2  /

Type created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION string2varray (
  2    p_string 	     IN VARCHAR2,
  3    p_tabcol_list OUT NOCOPY company_tabcol_list_typ
  4    )
  5    RETURN PLS_INTEGER
  6  IS
  7    i_count			BINARY_INTEGER;
  8    my_table 		DBMS_UTILITY.uncl_array;
  9    v_tabcol_list		company_tabcol_list_typ := company_tabcol_list_typ ();
 10    v_success		PLS_INTEGER := 0;
 11    v_fail			PLS_INTEGER := 1;
 12    v_modulename		VARCHAR2 (64) := 'string2varray';
 13    v_errmessage		VARCHAR2 (2000);
 14  BEGIN
 15    v_errmessage := 'Unable to convert Comma to Table. ';
 16    DBMS_UTILITY.comma_to_table (p_string, i_count, my_table);
 17    v_errmessage := 'Unable to populate v_tabcol_list VARRAY.';
 18  
 19    IF LENGTH (p_string) > 0 THEN
 20  	 FOR i IN 1 .. i_count LOOP
 21  	   v_tabcol_list.EXTEND;
 22  	   v_tabcol_list (i) := my_table (i);
 23  	 END LOOP;
 24  
 25  	 p_tabcol_list := v_tabcol_list;
 26    ELSE
 27  	 p_tabcol_list := company_tabcol_list_typ ();
 28    END IF;
 29  
 30    RETURN v_success;
 31  EXCEPTION
 32    WHEN OTHERS THEN
 33  	 v_errmessage := SQLERRM;
 34  	 p_tabcol_list := company_tabcol_list_typ ();
 35  	 RETURN v_fail;
 36  END;
 37  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> DECLARE
  2    v_cols	 company_tabcol_list_typ;
  3    v_success PLS_INTEGER;
  4  BEGIN
  5    v_success := string2varray ('deptno,dname,loc', v_cols);
  6    DBMS_OUTPUT.PUT_LINE (v_success);
  7    FOR cur IN
  8  	 (SELECT * FROM TABLE (CAST (v_cols AS company_tabcol_list_typ)) X)
  9    LOOP
 10  	 DBMS_OUTPUT.PUT_LINE (Cur.COLUMN_VALUE);
 11    END LOOP;
 12  END;
 13  /
0
deptno
dname
loc

PL/SQL procedure successfully completed.

SCOTT@10gXE> 



Previous Topic: transform field
Next Topic: Data Pump
Goto Forum:
  


Current Time: Thu Dec 08 19:51:59 CST 2016

Total time taken to generate the page: 0.11924 seconds