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


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. 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%')

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


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

    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

  2. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  3. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.

  4. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training