EcalCondDB/sql/create_laser_functions.sql

0001 /* the following commands provide a function that returns
0002    a table containing column names and corresponding types.
0003    It is needed since the USER_TAB_COLS is not visible to
0004    other accounts. */
0005
0006 /* create the types needed to define the result of the
0007    function */
0008 CREATE OR REPLACE TYPE T_LMF_COLS AS OBJECT (
0009         COLUMN_NAME VARCHAR(25),
0010         DATA_TYPE   VARCHAR(25)
0011 );
0012 /
0013
0014 CREATE OR REPLACE TYPE T_TABLE_LMF_COLS AS TABLE OF T_LMF_COLS;
0015 /
0016
0017 /* create the function
0018    usage: SELECT * FROM TABLE(LMF_TAB_COLS(table, column_to_exclude))
0019 */
0020 CREATE OR REPLACE FUNCTION LMF_TAB_COLS
0021 (
0022   tblname IN VARCHAR2,
0023   iovFieldName IN VARCHAR2) RETURN T_TABLE_LMF_COLS PIPELINED IS
0024
0025   TYPE      ref0 IS REF CURSOR;
0026
0027   sql_str   VARCHAR(1000);
0028   cur0      ref0;
0029
0030   V_RET     T_LMF_COLS
0031          := T_LMF_COLS(NULL, NULL);
0032
0033   BEGIN
0034     OPEN cur0 FOR
0035         'SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLS WHERE ' ||
0036         'TABLE_NAME = :1 AND COLUMN_NAME != ''LOGIC_ID'' AND COLUMN_NAME != :2'
0037     USING tblname, iovFieldName;
0038     LOOP
0039       FETCH cur0 INTO V_RET.COLUMN_NAME, V_RET.DATA_TYPE;
0040       EXIT WHEN cur0%NOTFOUND;
0041       PIPE ROW(V_RET);
0042     END LOOP;
0043     CLOSE cur0;
0044     RETURN;
0045   END LMF_TAB_COLS;
0046 /
0047
0048 GRANT EXECUTE ON LMF_TAB_COLS TO CMS_ECAL_R
0049 /