Can we reference such a function (point 1) in pure SQL?

it would have to return an OBJECT type and cannot by definition have OUT parameters.


ops$tkyte@ORA920.US.ORACLE.COM> create or replace type myType as object
  2  ( x int,
  3    y date,
  4    z varchar2(30)
  5  )
  6  /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace function f return myType
  2  as
  3  begin
  4          return myType( 55, sysdate, 'Hello World' );
  5  end;
  6  /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select f()
  2    from dual
  3  /

F()(X, Y, Z)
-----------------------------------------------------------------------------------------------------------------------------------
MYTYPE(55, '19-OCT-02', 'Hello World')
 

Hello Tom,

is there a possibility to use function returning record
in an SQL update statement like:

update t
set (x, y, z) = f();

Oracle 9i says: ORA-01767: UPDATE ... SET expression must be a subquery ...

Richard

Its really gr8. Thanks for the quick reply. Just keep it up...............

If i wants to run 2nd function in normal fashion, how can i do that ? means...
select f(1,2,3,4,5) from dual........

Thankx in advance

not sure that "select f(.....)" would be classified as the "normal fashion", most of my plsql functions are used in plsql -- i rarely call plsql from sql but anyway...


giving what error?  My crystal ball is on the blink -- so, I guess I'll just have to guess.

You are trying the example verbaitim.  SQL doesn't understand PLSQL datatypes -- only SQL types.  So, the example would be:

<b>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace type ARRAY as table of number
  2  /
</b>
Type created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace package my_pkg
  2  as
  3          function f( p_data in array ) return number;
  4  end;
  5  /

Package created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace package body my_pkg
  2  as
  3
  4  function f( p_data in array ) return number
  5  is
  6          l_greatest number;
  7  begin
  8          if ( p_data is null OR p_data.count = 0 )
  9          then
 10                  return null;
 11          end if;
 12
 13          l_greatest := p_data(1);
 14          for i in 2 .. p_data.count
 15          loop
 16                  if ( p_data(i) > l_greatest )
 17                  then
 18                          l_greatest := p_data(i);
 19                  end if;
 20          end loop;
 21          return l_greatest;
 22  end;
 23
 24  end;
 25  /

Package body created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select my_pkg.f( <b>ARRAY(</b> 1, 2, 3, 4, 5 ) ) from dual;

MY_PKG.F(ARRAY(1,2,3,4,5))
--------------------------
                         5

ops$tkyte@ORA920.US.ORACLE.COM> 

When we used in-built function we dont have to give any datatype there eg.greatest(...),least(...). As you have specified ARRAY here. I wants to write this query like following:

select f(1,2,3,4,5) from dual

We need to compare records of production/sales on a component basis for a specific time slice(month) and location. There are generally 14 components (could extend) and thousands (growing) locations. The client request has been made to compare individual locations by time slice.

Your proposal for the array would seem to fit this request and if it works puts the work back where I like it..the DB not JAVA.

The question is this...
Can I do the compare in this form and would I pass in two arrays of 14 items or two objects of 14 properties with these two being IN/OUT parameters and generate the return of a similar type being the difference?
Can you include a small example?

We are using 9iR2.

Hi

What are the differences between greatest/least and max/min

from the documentation they seem the same...

greatest/least work IN A ROW


max/min ACROSS ROWS


ops$tkyte@ORA920> select greatest(deptno,sal), least(deptno,sal) from emp;
 
GREATEST(DEPTNO,SAL) LEAST(DEPTNO,SAL)
-------------------- -----------------
                 800                20
                1600                30
                1250                30
                2975                20
                1250                30
                2850                30
                2450                10
                3000                20
                5000                10
                1500                30
                1100                20
                 950                30
                3000                20
                1300                10
 
14 rows selected.
 
ops$tkyte@ORA920> select max(deptno), max(sal), min(deptno), min(sal) from emp;
 
MAX(DEPTNO)   MAX(SAL) MIN(DEPTNO)   MIN(SAL)
----------- ---------- ----------- ----------
         30       5000          10        800
 
ops$tkyte@ORA920>
 

Why oracle does not have following, seems like would be useful

select deptno, sum(sal), count(*), concat(empid,' ,') names
from emp
group by deptno order by 1;

Thanks

Stragg is great.

"problem is -- it is very hard to solve generically as the result of concat would quickly exceed 4000 in many real cases."

I was told by an Oracle person (way back -probably when Oracle7 was new and exciting) that the issue was an ordering one.
IE 1+2+3+4 is always the same as 1+4+2+3, so SUM(col) is easy.
but 'A'||'B'||'C' isn't the same as 'C'||'B'||'A'
so that either
(a) a CONCAT(col) function would end up returning different results with the same input data (ie non-deterministic) or
(b) there would have to be a layer of sort between the selecting and the concatenation.

(b) isn't impossible, but probably not very pretty from a performance point of view.

Hi Tom, just a followup for the person above.  AS you once showed be me before (think it was you), if his need or desire was to use an existing function that gave the desired results, as input to an update it is possible to wrap the function in a select to satisfy syntax.  Thus he could use his function code in the update.  Here is an example:

SQL> set feedback 1
SQL> 
SQL> drop type c_temp1
  2  /

Type dropped.

SQL> drop type o_temp1
  2  /

Type dropped.

SQL> drop table a
  2  /

Table dropped.

SQL> drop function foo
  2  /

Function dropped.

SQL> 
SQL> create type o_temp1 is object (a number,b number)
  2  /

Type created.

SQL> 
SQL> create type c_temp1 is table of o_temp1
  2  /

Type created.

SQL> 
SQL> create table a (a number,b number)
  2  /

Table created.

SQL> 
SQL> insert into a values (1,1)
  2  /

1 row created.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> create function foo return c_temp1 is
  2      c_temp1_v c_temp1 := c_temp1();
  3  begin
  4      c_temp1_v.extend;
  5      c_temp1_v(1) := o_temp1(2,2);
  6      return c_temp1_v;
  7  end;
  8  /

Function created.

SQL> show errors
No errors.
SQL> 
SQL> select * from a
  2  /

         A          B
---------- ----------
         1          1

1 row selected.

SQL> 
SQL> update a set
  2      (a,b) = (select* from table(cast(foo as c_temp1)))
  3  /

1 row updated.

SQL> 
SQL> select * from a
  2  /

         A          B
---------- ----------
         2          2

1 row selected.

SQL> 
SQL> rollback
  2  /

Rollback complete.

SQL> 
SQL> select * from a
  2  /

         A          B
---------- ----------
         1          1

1 row selected.

SQL> 
SQL> spool off
 

table_t

name1 date1 name2 date2 name3 date3
John 8/9/04 Joe 8/4/04 Doe 8/8/04


I want to select the name that associated with the latest date with it in a simple query.
In this example, I want return just John. Assuming the dates will never be the same.

select name from table_t having greatest(date1,date2,date3).

How can I write a simple query?

Thank you for your help.

Dear sir
i have been throught the OCP , while i was studying the oracle university books. they have mentioned that there are hundreds of sunctions avaliable in oracle (old and new) please if you can provide me with a large set of those functions (single row ,multiple row,....) those are not mentioned in books..

Hi,

i am trying to make a funtion that would be a division of two columns (and then multiply the result by 100) but the problem is that i am not getting the output in the format i want

I have two numeric columns in a table both have number datatypes with maximum length of 3 

suppose these are the values in two columns

    COL_1      COL_2
      322        222
      330        333
        9          4
      122        121
        3        952
      232        224
      234        900
       11          4
      345        180
        2         53
      122         12
       23         24

now throughout my calculation I would like to always divide COL_1 by COL_2 and then multiply the result by 100  i.e. COL_1/COL2*100

so  now if I divide them and multiply by 100 the output is 

SQL> SELECT COL_1, COL_2, COL_1/COL_2*100 FROM Table

    COL_1       COL_2   COL_1/COL_2*100
       322        222        145.045045 
       330        333        99.0990991 
         9          4               225        
       122        121        100.826446 
         3        952         .31512605        
       232        224        103.571429 
       234        900                26        
        11          4               275        
       345        180        191.666667 
         2         53        3.77358491 
       122         12        1016.66667 
        23         24        95.8333333 

now my requirement is that i want the output based on following Conditions:

1. If the result of the division multiply by 100 is in double digits (no decimal) then a decimal    and two trailing zeros should come after the decimal point (i.e 234/900*100 = 26  my    requiremnet = 26.00)

2. If the result of the division x 100 is in three digits (no decimal) then a decimal and one    trailing zero should come after the decimal point (i.e 9/4*100 = 225  my requiremnet = 225.0)

3. Rounded to 2 decimal places with a leading zero, if result of the division x 100 has a maximum    of ONE digit before decimal (i.e 2/53*100 = 3.77358491  my requiremnet = 03.77) 

4. Rounded to 2 decimal places if result of the division x 100 has a maximum of two digits before    decimal (i.e 330/333*100 = 99.0990991  my requiremnet = 99.10 or whatever comes after             rounding off to 2 decimal places)

5. Rounded to 1 decimal place if result of the division x 100 has a maximum of three digits          before decimal (i.e 322/222*100 = 145.045045  my requiremnet = 145.1 or whatever comes after      rounding off to 1 decimal place)

6. Rounded to three decimal places with a leading zero before the decimal, if result of the         division x 100 has no digit before decimal (i.e 3/952*100 = .31512605  my requiremnent - 0.315)

7. NULL if the result of the division x 100 has four or more digits before decimal (i.e    122/12*100 = 1016.66667  my requiremnent = '')

so is the query and my desired output in the last column

     COL_1      COL_2   COL_1/COL_2*100      DESIRED OUTPUT
       322        222        145.045045      145.1               condition 5 above
       330        333        99.0990991      99.10               condition 4 
         9          4               225      225.0               condition 2 
       122        121        100.826446      100.8               condition 5 
         3        952         .31512605      0.315               condition 6 
       232        224        103.571429      103.6               condition 5 
       234        900                26      26.00               condition 1 
        11          4               275      275.0               condition 2 
       345        180        191.666667      191.7               condition 5 
         2         53        3.77358491      03.77               condition 3 
       122         12        1016.66667                          condition 7 
        23         24        95.8333333      95.83               condition 4  


hope this will clear my requirement, i would be highly grateful if you solve my problem

regards,
Asim.



 

Hi Tom,
apologies for not providing the ddl statements in my question..so here are they..


create table t (col_1 number(3), col_2 number(3))
/


insert into t (col_1, col_2) values (330, 333);
insert into t (col_1, col_2) values (9, 4);
insert into t (col_1, col_2) values (122, 121);
insert into t (col_1, col_2) values (3, 952);
insert into t (col_1, col_2) values (232, 224);
insert into t (col_1, col_2) values (234, 900);
insert into t (col_1, col_2) values (11, 4);
insert into t (col_1, col_2) values (345, 100);
insert into t (col_1, col_2) values (2, 52);
insert into t (col_1, col_2) values (122, 12);
insert into t (col_1, col_2) values (23, 24);
insert into t (col_1, col_2) values (50, 0);


Tom, just a a little update on my original question..i.e. if the value in col_2 = 0 for any row then the output for that row should be a dash or hyphen..'--'

so my desried output is this

COL_1 COL_2 Desired output
322 222 145.0
330 333 99.10
9 4 225.0
122 121 100.8
3 952 0.315
232 224 103.6
234 900 26.00
11 4 275.0
345 180 191.7
2 53 03.77
122 12
23 24 95.83
50 0 -

i would be greatul if uou make an efficient function of this process


thanks in advance,
regards,
Ramis.

hi tom
its gr8 surfing your questions.Can you please tell me how can i ask the questions i mean do i need to send mail to you if yes on which id and if no please tell me the procedure.I am dying to ask questions to you.

Thanks in advance
Nakul

Hi, tom,
following your reply in my original question (two posts above in this thread)
I tried making a function that returns me a rounded figure according to some conditions mentioned in the function..the function is currrently based on a table and by passing two column names of that table it returns the values..


CREATE or replace FUNCTION GET_ROUNDED_FIGURE (COL1 NUMBER, COL2 NUMBER) 
RETURN VARCHAR2 AS
A VARCHAR2(10);
BEGIN
SELECT 
          DECODE (num_length,
          3, TO_CHAR (ROUND (calc_num, 1), 9999.9),
          2, TO_CHAR (ROUND (calc_num, 2), 999.99),
          1, TO_CHAR (ROUND (calc_num, 2), 900.99),
          0, TO_CHAR (ROUND (calc_num, 3), 90.999), NULL) INTO A FROM
(SELECT 
          COL1 / COL2 * 100 AS calc_num,
          DECODE (TRUNC (COL1 / COL2 * 100), 0, 0,
          LENGTH (TRUNC (COL1 / COL2 * 100))) AS num_length 
FROM DUAL, T WHERE COL_2 <> 0
AND T.COL_1 = COL1
AND T.COL_2 = COL2) 
UNION ALL
SELECT   
          '-' FROM DUAL, T WHERE COL2 = 0
          AND T.COL_1 = COL1
          AND T.COL_2 = COL2;
RETURN (A);
EXCEPTION    WHEN NO_DATA_FOUND THEN RETURN NULL;
END   ;

 
 
SQL. SELECT GET_ROUNDED_FIGURE (COL_1, COL_2) FROM T
/ 
 
GET_ROUNDED_FIGURE(COL_1,COL_2)
--------------------
  145.0
  99.10
  225.0
  100.8
  0.315
  103.6
  26.00
  275.0
  191.7
  03.77
  95.83
 
as you can see that is ok when i execute the function mentioning  the same table name as mentioned in the fucntion
 
but when i run this query
 
SQL> SELECT GET_ROUNDED_FIGURE (23, 34) FROM DUAL
/ 
 
GET_ROUNDED_FIGURE(23,34)
 
 
IT SHOWS NOTHING..
 
 
 
I want a generic function so that given any two values not necessarily of any particular table it retrieves the rounded figure..
 
here is the sample ddl data
 
create table t (col_1  number(3), col_2 number(3))
/ 
 
 
insert into t (col_1, col_2) values (330, 333);
insert into t (col_1, col_2) values (9, 4);
insert into t (col_1, col_2) values (122, 121);
insert into t (col_1, col_2) values (3, 952);
insert into t (col_1, col_2) values (232, 224);
insert into t (col_1, col_2) values (234, 900);
insert into t (col_1, col_2) values (11, 4);
insert into t (col_1, col_2) values (345, 100);
insert into t (col_1, col_2) values (2, 52);
insert into t (col_1, col_2) values (122, 12);
insert into t (col_1, col_2) values (23, 24);
insert into t (col_1, col_2) values (50, 0);
 

regards,
 

Hi Tom,

Is there any way of knowing which single-row SQL functions are available in a specific Oracle version.
I know, I can refer the documentation for this.

What I would like to know is, is there any dictionary view available which can be queried to get the list of functions?

Thanks

Dinesh