Inline Functions

[speial]

Anyone have an idea how to embed a function with an SQL statement ?

I would like to embed ( define, declare and execute) functions within an SQL query.

I don not want to 'store'(create a function object within the DB schema).

 

[SantaMufasa]

I'll be interested to see/hear what others say since I'm not aware of any method(s) of having SQL execute functions that are not either built-in (e.g., substr(), to_char(), sum(), max(), et cetera) or are user-defined functions that are stored in the database (using the "CREATE or REPLACE FUNCTION..." construct).

If you want to run SQL and have have the benefits of user-defined functions that are not stored in the database, then why not use an anonymous block of PL/SQL code in which you define, then run, your function and also execute your SQL?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[jimirvine]

As Dave says, you cannot to what you seem to be asking for. You can execute SQL inside PL/SQL but not vice versa (barring, of course, stored functions)

 

[taupirho]

Can you provide a small example of what you'ld like to do


In order to understand recursion, you must first understand recursion.

 

[speial]

Hello,

I tried using the Declare but I received lots of errors. Perhaps I was not using the correct syntax for 'functions' in an anonymous block?

I have attached the Calling SQL and the Funciton I want to embed.

Thanks for taking the time to assist. Please let me know what you find......

++++++
Calling SQL >>>>
select D.SUBST_ID "SUBSTATION ID", s.subst_nam "SUBSTATION NAME",
SUBSTR(c.circt_nam,instr(c.circt_nam,'_',1)+1,6)"CIRCUIT NAME",
d.dni_equip_type "DEVICE TYPE",
DECODE(d.dni_equip_type, 'RECL', d.equip_serial_no, 'SSUB', ' ', d.display_text) "DEVICE ADDRESS",

jeacust.j_cust_cnt_prem_f (d.equip_stn_no, d.circt_id,d.dni_equip_type) "NUMBER OF CUST"


from dni D, SUBSTATION S, circuit c

where S.SUBST_ID = D.SUBST_ID AND
d.circt_id = c.circt_id and
D.DNI_EQUIP_TYPE in ('SSUB', 'RECL', 'SWITCH')
GROUP BY s.subst_nam, D.subst_id, c.circt_nam, d.dni_equip_type, d.equip_serial_no, d.display_text,
d.equip_stn_no, d.circt_id,d.dni_equip_type
order by s.subst_nam, D.subst_id, c.circt_nam, d.dni_equip_type, d.equip_serial_no, d.display_text,
d.equip_stn_no, d.circt_id,d.dni_equip_type





Function >>>>

CREATE OR REPLACE
FUNCTION jeacust.j_cust_cnt_prem_f
(
IN_EQUIP_STN_NO IN VARCHAR2,
IN_CIRCUIT_ID IN VARCHAR2,
IN_DNI_EQUIP_TYPE IN VARCHAR2
)

RETURN VARCHAR2

IS



OUT_COUNT_CUST VARCHAR2(100);


BEGIN
BEGIN
SELECT COUNT(premise_custno_id)
INTO OUT_COUNT_CUST
FROM premise
WHERE strctur_no IN
(
SELECT DISTINCT dni.strctur_no
FROM dni,
(
SELECT equip_stn_no
FROM jeacust.j_dni_connectivity_v jc
START WITH decode(IN_DNI_EQUIP_TYPE, 'SSUB', jc.tie_equip_stn_no,
jc.equip_stn_no) = IN_EQUIP_STN_NO
CONNECT BY PRIOR jc.equip_stn_no = jc.tie_equip_stn_no
AND jc.valid_link_flg = 'T'
AND jc.circt_id = IN_CIRCUIT_ID
) jc_inline
WHERE dni.equip_stn_no = jc_inline.equip_stn_no
)
AND cust_type_cd = 'ELEC' ;

EXCEPTION
WHEN OTHERS
THEN
OUT_COUNT_CUST := 'ERROR';
END;

RETURN (OUT_COUNT_CUST );
END;
/

 

[Dagon]

It would help if you gave the error messages or at least the leading three or four.

 

[SantaMufasa]

Speial,

I cannot test your code since I do not have tables and data, but the following code shows how to restructure your code (from above) into an anonymous PL/SQL block of code. One of the errors in your code, above, is the qualifier ("jeacust.") for your in-block-defined function...in an anonymous-block definition, there is no "jeacust." visible to the anonymous block.

Here is a "re-structure" of your code, above:

set serveroutput on format wrap
declare
    hold_cust_count varchar2;
FUNCTION j_cust_cnt_prem_f
(
  IN_EQUIP_STN_NO      IN       VARCHAR2,
  IN_CIRCUIT_ID        IN       VARCHAR2,
  IN_DNI_EQUIP_TYPE    IN       VARCHAR2
)
  RETURN    varchar2    
  IS
OUT_COUNT_CUST  varchar2;
BEGIN
    BEGIN
        SELECT COUNT(premise_custno_id)  
        INTO   OUT_COUNT_CUST
        FROM   premise
        WHERE  strctur_no IN 
            (
            SELECT DISTINCT dni.strctur_no 
            FROM dni,
                (
                SELECT equip_stn_no 
                FROM jeacust.j_dni_connectivity_v jc 
   START WITH decode(IN_DNI_EQUIP_TYPE, 'SSUB', jc.tie_equip_stn_no,
                                    jc.equip_stn_no) = IN_EQUIP_STN_NO
                CONNECT BY PRIOR jc.equip_stn_no = jc.tie_equip_stn_no
                AND jc.valid_link_flg = 'T'
                AND jc.circt_id = IN_CIRCUIT_ID
                ) jc_inline
            WHERE dni.equip_stn_no = jc_inline.equip_stn_no
            )
        AND cust_type_cd = 'ELEC' ; 
    EXCEPTION
      WHEN  OTHERS
      THEN
         OUT_COUNT_CUST  := 'ERROR';
   END; 
RETURN (OUT_COUNT_CUST );
END;
begin
    dbms_output.enable(1000000);
    For x in (select D.SUBST_ID "SUBSTATION ID", s.subst_nam "SUBSTATION NAME",  
                     SUBSTR(c.circt_nam,instr(c.circt_nam,'_',1)+1,6)"CIRCUIT NAME", 
                     d.dni_equip_type "DEVICE TYPE", 
                     DECODE(d.dni_equip_type, 'RECL', d.equip_serial_no, 'SSUB', ' ', d.display_text) "DEVICE ADDRESS",       
                     d.equip_stn_no, d.circt_id,d.dni_equip_type)  "NUMBER OF CUST"
                from dni D, SUBSTATION S, circuit c
               where S.SUBST_ID = D.SUBST_ID
                 AND d.circt_id = c.circt_id
                 and D.DNI_EQUIP_TYPE in ('SSUB', 'RECL', 'SWITCH')
               GROUP BY s.subst_nam, D.subst_id, c.circt_nam, d.dni_equip_type, d.equip_serial_no, d.display_text,
                     d.equip_stn_no, d.circt_id,d.dni_equip_type
               order by s.subst_nam, D.subst_id, c.circt_nam, d.dni_equip_type, d.equip_serial_no, d.display_text,
                     d.equip_stn_no, d.circt_id,d.dni_equip_type) loop
        hold_cust_count := j_cust_cnt_prem_f(x.equip_stn_no, x.circt_id,x.dni_equip_type);
        dbms_output.put_line('-- Formatted output from each row, above, goes here.');
        -- In the dbms_output.put_line statement, above, be sure to refer to each expression from the
        -- CURSOR FOR LOOP with the prefix "x.<expression".
    end loop;
end;
/

Also, I did not format your output (due to my needing to get to my office), but you can experiment with the "dbms_output.put_line" PL/SQL client-output statement.

Now for my overriding suggestion: Your function is simply a SQL query...why not eliminate the function entirely by placing your "cust_count" logic directly in your SQL-query statement?

Let us know if any of this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[speial]

Thanks for your response.

The SQL (function) was originally part of the entire SQL.
The results with the "group by" did not sum the data as expected. Thinking that using a function (which I can verify independently) would help me check the results.

With the information provided, I will give it another try.