Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql procedure using cursors (oracle 11g)
pl/sql procedure using cursors [message #662085] Mon, 17 April 2017 15:18 Go to next message
POGAKU_SANTHOSH
Messages: 19
Registered: April 2017
Junior 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 #662086 is a reply to message #662085] Mon, 17 April 2017 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
code should be like below

LOOP
FETCH CUR INTO V_SRC_FILE_NAMES;
EXIT WHEN CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE ('SOURCE FILE NAMES 1 = ' || V_SRC_FILE_NAMES);

END LOOP;


Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Re: pl/sql procedure using cursors [message #662088 is a reply to message #662086] Mon, 17 April 2017 15:56 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 19
Registered: April 2017
Junior Member
Thank you sir you made my day.

As i am new to this forum and i am a beginner to pl/sql can you say can i write that code in more efficient way. Can i reduce the size of that code.

Regards,
Santhosh.
Re: pl/sql procedure using cursors [message #662090 is a reply to message #662088] Mon, 17 April 2017 16:03 Go to previous message
BlackSwan
Messages: 25793
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.
Previous Topic: Help required in SQL Analytic Function lag()
Next Topic: Regexp magic ! (I hope)
Goto Forum:
  


Current Time: Tue Jan 16 16:14:38 CST 2018

Total time taken to generate the page: 0.03629 seconds