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’);
Hello Dayakar,
ReplyDeleteI 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