Answer Posted / ajit nayak
FUNCTION
---------
CREATE OR REPLACE FUNCTION FN_WORD_CNT (V_NAME VARCHAR2,V_SRCH_WRD VARCHAR2)
RETURN NUMBER
IS
I_COUNT NUMBER;
I_WRD_CNT NUMBER;
TYPE V_WORD IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
V_S_WORD V_WORD;
BEGIN
I_WRD_CNT := LENGTH(V_NAME);
DBMS_OUTPUT.PUT_LINE(I_WRD_CNT);
I_COUNT := 0;
FOR I IN 1..I_WRD_CNT
LOOP
SELECT SUBSTR(V_NAME,I,1)
INTO V_S_WORD(I)
FROM DUAL;
END LOOP;
FOR I IN 1..V_S_WORD.COUNT
LOOP
IF(V_S_WORD(I) = V_SRCH_WRD) THEN
I_COUNT := I_COUNT + 1;
END IF;
END LOOP;
RETURN I_COUNT;
END;
FOR RUN
-------
select FN_WORD_CNT('AJIT','A')
from dual;
PROCEDURE
---------
CREATE OR REPLACE FUNCTION FN_WORD_CNT (V_NAME VARCHAR2,V_SRCH_WRD VARCHAR2)
RETURN NUMBER
IS
I_COUNT NUMBER;
I_WRD_CNT NUMBER;
TYPE V_WORD IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
V_S_WORD V_WORD;
BEGIN
I_WRD_CNT := LENGTH(V_NAME);
DBMS_OUTPUT.PUT_LINE(I_WRD_CNT);
I_COUNT := 0;
FOR I IN 1..I_WRD_CNT
LOOP
SELECT SUBSTR(V_NAME,I,1)
INTO V_S_WORD(I)
FROM DUAL;
END LOOP;
FOR I IN 1..V_S_WORD.COUNT
LOOP
IF(V_S_WORD(I) = V_SRCH_WRD) THEN
I_COUNT := I_COUNT + 1;
END IF;
END LOOP;
RETURN I_COUNT;
END;
RUN THIS PROCEDURE
------------------
DECLARE
v_name varchar2(10) := '&name';
v_srch varchar(2) := '&str';
i_cnt number;
begin
DBMS_OUTPUT.PUT_LINE('ENTER NAME '||v_name||CHR(10)||' SEARCH STR '||v_srch);
P_FN_WORD_CNT(v_name,v_srch,i_cnt);
END;
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
What are sql ddl commands?
In pl/sql, what is bulk binding, and when/how would it help performance?
What are the rules to be applied to nulls whilst doing comparisons?
What does <> sql mean?
How do I find duplicates in the same column?
Lookups are a key component in sql server integration services (ssis). Explain its purpose?
What is primary key sql?
how many ways to get the current time? : Sql dba
what are the advantages and disadvantages of views in a database? : Sql dba
Is sql free?
What is trigger with example?
Is sql low level language?
Which certification is best for sql?
What does count (*) do in sql?
What are sql*plus environment variables?
