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 is a foreign key ? : Sql dba
how to enter binary numbers in sql statements? : Sql dba
how can we find the number of rows in a table using mysql? : Sql dba
How do I run a query in pl sql developer?
What is sql scripting?
What view means?
Under what condition it is possible to have a page level lock and row lock at the same time for a query? : Transact sql
What are the string functions in sql?
What is procedure explain with program?
Do ddl statements need commit?
What is sql query optimization?
What is sql stand for?
What are the two characteristics of a primary key?
What is linq to sql?
Do we need to create index on primary key?
