pl/sql procedure using cursors [message #662085] |
Mon, 17 April 2017 15:18 |
|
POGAKU_SANTHOSH
Messages: 39 Registered: April 2017
|
Member |
|
|
Hi people,
I have a scenario i have a table t1 which has two table names they are 'san' and 'man' and now the two tables 'san','man' each table has multiple table filenames like table 'san' has two file names = (audi.tx ,mercedes.txt) and the second table 'man' has one file name = (hundai.txt).I wrote a procedure where it can return no of rows in the which are present in the respective table with the respective file name .here is the procedure and after executing the procedure i need to get only one distinct file name but unfortunately i am getting multiple same file names.The sample output is present at last.
:sql queries
-- for creating t1 table--
CREATE TABLE HR.T1
(
NAMES VARCHAR2(20 BYTE),
MAPPING_ID VARCHAR2(10 BYTE)
);
SET DEFINE OFF;
Insert into HR.T1
(NAMES, MAPPING_ID)
Values
('san', '1');
Insert into HR.T1
(NAMES, MAPPING_ID)
Values
('man', '1');
COMMIT;
-----------sql query for 'san' table----
CREATE TABLE HR.SAN
(
SRC_FILENAME VARCHAR2(20 BYTE)
);
SET DEFINE OFF;
Insert into HR.SAN
(SRC_FILENAME)
Values
('audi.txt');
Insert into HR.SAN
(SRC_FILENAME)
Values
('mercedes.txt');
COMMIT;
------sql query for man table ----
CREATE TABLE HR.MAN
(
SRC_FILENAME VARCHAR2(20 BYTE)
);
SET DEFINE OFF;
Insert into HR.MAN
(SRC_FILENAME)
Values
('hundai.txt');
COMMIT;
-------package spec -----
CREATE OR REPLACE PACKAGE HR.file_entry
AS
PROCEDURE PKG_PROC_FILES(L_MAPPING_ID NUMBER);
procedure insert_proc (l_object_name VARCHAR2);
END;
-----package body -----
CREATE OR REPLACE PACKAGE BODY HR.file_entry
AS
PROCEDURE PKG_PROC_FILES (L_MAPPING_ID NUMBER)
AS
V_TABLE_NAME VARCHAR2 (50);
V_SCHEMA_NAME VARCHAR2 (50);
TYPE CURTYPE IS REF CURSOR;
V_SCHEMA_NAME VARCHAR2 (50);
----
CURSOR TARGET_OBJ_CUR
IS
SELECT DISTINCT names
FROM t1
WHERE MAPPING_ID = L_MAPPING_ID;
BEGIN
FOR I IN TARGET_OBJ_CUR
LOOP
INSERT_PROC (I.names);
DBMS_OUTPUT.PUT_LINE ('TARGET_TABLE_NAME= ' || I.names);
END LOOP;
END;
PROCEDURE INSERT_PROC (L_OBJECT_NAME VARCHAR2)
AS
V_TABLE_NAME VARCHAR2 (50);
V_SCHEMA_NAME VARCHAR2 (50);
V_QUERY VARCHAR2 (50);
TYPE CURTYPE IS REF CURSOR;
V_SRC_FILE_NAMES VARCHAR2 (200);
CUR CURTYPE;
BEGIN
V_QUERY := 'select distinct src_filename from ' || L_OBJECT_NAME;
OPEN CUR FOR V_QUERY;
LOOP
FETCH CUR INTO V_SRC_FILE_NAMES;
DBMS_OUTPUT.PUT_LINE ('SOURCE FILE NAMES 1 = ' || V_SRC_FILE_NAMES);
COMMIT;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
END;
END;
/
------ AFTER EXECUTING THE PROCEDURE I AM MULTIPLE NAMES FROM THE DBMS'----
O/P
SOURCE FILE NAMES = mercedes.txt
SOURCE FILE NAMES = audi.txt
SOURCE FILE NAMES = audi.txt
TARGET_TABLE_NAME= san
SOURCE FILE NAMES = hundai.txt
SOURCE FILE NAMES = hundai.txt
TARGET_TABLE_NAME= man
/* IN THE BELOW O/P I NEED TO GET ONLY DISTINCT SOURCE FILE NAMES FROM TABLE BUT I AM UNABLE TO UNDERSTAND WHY AM I GETTING THE 'audi.txt' AND ' hundai.txt' MULTIPLE TIMES CAN any one help me out to solve this issue i need a file name once to be printed in the output like 'mercedes.txt' which has been printed only once in the output*/
|
|
|
|
|
Re: pl/sql procedure using cursors [message #662090 is a reply to message #662088] |
Mon, 17 April 2017 16:03 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Do NOT write PL/SQL when you can do the same in plain SQL.
Only plain SQL directly interacts with the database contents.
PL/SQL only adds overheads & forces context switch between the PLSQL engine & SQL engine.
Use of EXECUTE IMMEDIATE is clear sign of a design deficiency.
|
|
|