Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 3 April 2016

SQL Functions in PL/SQL

Available in procedural statements
Single-row number and character functions
Data type conversion functions
Date functions
Not available in procedural statements
Decode Function
Group Functions (Directly)
Valid use of functions in a PL/SQL block: 
          v_name := UPPER(‘dayakar’);
          v_date := TO_DATE(’12,February 1998’,’DD,Month YYYY’);   
Invalid use of functions in a PL/SQL block: 
          v_tot_sal := SUM(sal);
         v_grade := DECODE(job,’MANAGER’,’A’,’CLERK’,’B’,’C’);
Share this article :

1 comments:

  1. Hello Dayakar,


    I genuinely do look forward for the time where you post some new write ups. Your blog makes me feel so educated! Continue soaring and writing please.

    I have a script I've been using in an anonymous PL/SQL block that searches some tables for a string value.

    Database trigger is stored PL/SQL program unit
    associated with a specific database table. Usages are Audit data modifications, Log events transparently, enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

    I want to change this so that it is a Stored Procedure where the user that calls the procedure can supply the table_owner info.

    FOR t IN (SELECT owner, table_name, column_name
    FROM all_tab_columns
    WHERE owner = 'BLAH' and data_type LIKE '%CHAR%')
    LOOP

    so can i substitute the 'BLAH' text for a variable as in ..

    DECLARE
    OWNER_VARIABLE NVARCHAR2;
    BEGIN

    FOR t IN (SELECT owner, table_name, column_name
    FROM all_tab_columns
    WHERE owner = OWNER_VARIABLE and data_type LIKE '%CHAR%')
    LOOP

    I have tried this but it doesn't work. I'm not sure of the syntax to substitute text for a variable in a for loop.


    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thanks and Regards
    Ivan

    ReplyDelete