Hai all I have created procedure which is as follows but while executing I get a error as table or view doesn't exist please refer the query and correct the mistake. Thanks in advance
The procedure is to give 2 inputs and get the output from second input in desc order.
CREATE OR REPLACE PROCEDURE my_proc_sp( p_in1 IN NUMBER
, p_in2 IN VARCHAR2
, p_out1 OUT NUMBER)
AS
v_count NUMBER;
my_exp EXCEPTION;
v_in2 VARCHAR2(30);
v_c NUMBER;
v_id NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM user_tables
WHERE table_name = 'MY_PROC_TB';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE my_proc_tb';
ELSE
EXECUTE IMMEDIATE 'CREATE TABLE my_proc_tb (id NUMBER)';
END IF;
IF (LENGTH(p_in2) - LENGTH(REPLACE(p_in2, ' ', ','))) <> LENGTH(p_in1) - 1 THEN
RAISE my_exp;
END IF;
SELECT REPLACE(p_in2, ' ', ',') INTO v_in2 FROM DUAL;
FOR a IN 1..p_in2
LOOP
SELECT REGEXP_SUBSTR(v_a, '[^,]+', 1, a) INTO v_c FROM dual;
EXECUTE IMMEDIATE 'INSERT INTO my_proc_tb VALUES (v_c)';
COOMIT;
END LOOP;
DECLARE
CURSOR my_proc_c
IS
SELECT id FROM my_proc_tb ORDER BY id DESC;
BEGIN
FOR i IN my_proc_c
LOOP
IF v_id IS NULL THEN
v_id := i.id;
ELSE
v_id := v_id||i.id;
EXIT WHEN my_proc_c%NOTFOUND;
END IF;
END LOOP;
p_out1 := v_id;
END;
EXCEPTION
WHEN my_exp THEN
dbms_output.put_line('Invalid Input Parameter');
END my_proc_sp;
/
Awaiting for your response