Interested to Buy Any Domain ? << Click Here >> for more details...
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
Explain the significance of the & and && operators in pl sql.
how to convert character strings to numeric values? : Sql dba
What are triggers in sql?
How do I install sql?
What is the use of stored procedures?
how does a local variable is defined using t-sql? : Transact sql
How do I remove duplicates in two columns?
What are triggers and its uses?
what is difference between delete and truncate commands? : Sql dba
Can we create view in stored procedure?
Can we join 3 tables in sql?
What are the triggers associated with image items?
What is rank function in sql?
what are ddl statements in mysql? : Sql dba
How many primary keys can a table have?
