how to run the stored procedure in batch mode or in run it in parallel processing

We are iterating 100k+ records from global temporary table.below stored procedure will iterate all records from glogal temp table one by one and has to process below 3-steps.

1)to see whether product is exists or not 2)to see whether product inside the assets are having the 'category' or not. 3)to see whether the assets are having file names starts with '%pdf%' or not.

So each record has to prcoess these 3-steps and final document names will be stored in the table for the successful record.if any error comes in any of the steps then error message will be stored for that record.

Below stored procedure is taking long time to process Because its processing sequentially.

1)Is there any way to make this process faster in the stored procedure itself by doing batch process? 2)If its not possible in stored procedure then Can we change this code into Java and run this code in multi threaded mode like creating 10 threads and each thread will take 1 record concurrently and process this code.I would be happy if somebody gis some pseudo code.

which approach is going to suggest?


DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;  
FINAL_FILNAME VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 AN
        WHERE 
      ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.NAME_ID =26 AND
    ANP.CATEORGY='category' AND
    ANP.QNAME_ID='categories'  AND
        ANP.NODE_ID IN(SELECT CHILD_NODE_ID 
                  FROM TABLE_ASSOC START WITH PARENT_NODE_ID IN(v_isbn) 
                      CONNECT BY PRIOR CHILD_NODE_ID = PARENT_NODE_ID);


BEGIN
--Iterating all Products
FOR R1 IN C1 
LOOP

FINAL_FILNAME :='';
BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TABLE1 AN,
TABLE2 ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;
V_FINAL_ERRORMESSAGE :='';

--To check Whether Product inside the assets are having the 'category' is applied or not
FOR R2 IN C2(R1.ISBN) 
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  

BEGIN
--In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
SELECT ANP.STRING_VALUE  into V_FILENAME
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 ACD
        WHERE 
       ANP.QNAME_ID=21  AND 
       ACD.ID=ANPP.LONG_VALUE 
       ANP.NODE_ID=ANPP.NODE_ID AND
       ANPP.QNAME_ID=36 AND
       ANP.STRING_VALUE LIKE '%pdf%'  AND 
       ANP.NODE_ID=R2.NODE_ID; 

    FINAL_FILNAME := FINAL_FILNAME  || V_FILENAME ||',';

   EXCEPTION WHEN
     NO_DATA_FOUND THEN
     V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  R2.NODE_ID || ':Documents[LIKE %pdf%] were not found ;';
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;


     END;--Iterating for each NODEID

END LOOP;--Iterating the assets[Nodes] for each product of catgeory

  --  DBMS_OUTPUT.PUT_LINE('R1.ISBN:' || R1.ISBN ||'::V_CATEGORY_COUNT:' || V_CATEGORY_COUNT);

 IF(V_CATEGORY_COUNT  = 0) THEN
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  'Category is not applied to none of the Assets for this Product'  WHERE ISBN= R1.ISBN;
   END IF;  


EXCEPTION WHEN
NO_DATA_FOUND THEN
      UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=   'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

  -- DBMS_OUTPUT.PUT_LINE( R1.ISBN || 'Final documents:'||FINAL_FILNAME);
      UPDATE GT_ADD_ISBNS SET FILENAME=FINAL_FILNAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;

Open in new window

chaituu chaituAsked:
Who is Participating?
 
chaituu chaituAuthor Commented:
tuned the stored procedure a little extent.removed the cursor for loop and used listagg..I dont  see much improvement.
SET SERVEROUTPUT ON;
DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;  
FINAL_FILNAME VARCHAR2(2000);
V_ERRORMESSAGE VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);
sql_stmt  VARCHAR2(2000);

CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

BEGIN
--Iterating all Products
FOR R1 IN C1 
LOOP

BEGIN

--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TEMP_NODE AN,
	  TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(R1.ISBN);

V_FINAL_ERRORMESSAGE :='';
--To check Whether Product inside the assets are having the Category is applied or not
SELECT DISTINCT LISTAGG(ANP.NODE_ID, ',') 
WITHIN GROUP (ORDER BY ANP.NODE_ID) NODE_ID INTO V_NODE_ID
		FROM 
		TEMP_NODE_PROPERTIES ANP,
		TEMP_NODE_PROPERTIES ANPP,
		TEMP_NODE AN
		WHERE 
	  ANP.NODE_ID=AN.ID AND
      ANPP.NODE_ID=ANP.NODE_ID AND
      AN.TYPE_QNAME ='asset' AND
   (ANP.STRING_VALUE ='uPDF' AND
    ANPP.STRING_VALUE ='Entire PDF')  AND
    ANP.TYPE_QNAME='categories'  AND
 		ANP.NODE_ID IN(SELECT CHILD_ID 
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(R1.ISBN)) 
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID);
 

DBMS_OUTPUT.PUT_LINE('R1.ISBN:'|| R1.ISBN || ':V_NODE_ID:' ||V_NODE_ID);
      
     --In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
  sql_stmt := 'SELECT LISTAGG(ANP.STRING_VALUE, '','') WITHIN GROUP (ORDER BY ANP.STRING_VALUE) 		FROM 		TEMP_NODE_PROPERTIES ANP
		WHERE   ANP.TYPE_QNAME=''name''  AND  (( ANP.STRING_VALUE LIKE ''%pdf%''  AND  ANP.STRING_VALUE NOT LIKE ''%pod%'') 
       )  AND  ANP.NODE_ID IN (' || V_NODE_ID ||')';
       
     
  if(V_NODE_ID  IS NOT NULL)  THEN
  EXECUTE IMMEDIATE sql_stmt into V_FILENAME;
  else
  DBMS_OUTPUT.PUT_LINE('Category is not applied to none of the Assets for this Product');
   V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is not applied to none of the Assets for this Product';
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;
  GOTO CATEGORY_NOT_FOUND;
  END IF;
  
   
   IF(V_FILENAME is NULL) THEN
   DBMS_OUTPUT.PUT_LINE('Category is applied for this Product But for the asset:'||  V_NODE_ID || ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;');
    V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  V_NODE_ID || ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;';
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;
   END IF;
  
  <<CATEGORY_NOT_FOUND>>  -- not allowed unless an executable statement follows
   NULL; -- add NULL statement to avoid error


EXCEPTION WHEN
NO_DATA_FOUND THEN
      UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=   'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

   DBMS_OUTPUT.PUT_LINE( R1.ISBN || '::Final documents:'||V_FILENAME);
      UPDATE GT_ADD_ISBNS SET CATEGORY11_FILENAME=V_FILENAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;

Open in new window

0
 
Geert GOracle dbaCommented:
i'll give it a pseudo attempt

dunno, if you have access to livesql.oracle.com ?
https://livesql.oracle.com/apex/livesql/s/gav3j57wi5x7yptfm74eebe9e

I created the tables based on the code.

you have a qname_id which you use with a text 'categories' and an integer 26
very odd

it would be nice to upload a representative sample of your data to work with
0
 
chaituu chaituAuthor Commented:
I dont have oracle login.I think only access to those guys who are working in oracle.I will try to give sample data to all the tables.If possible can you paste the code here..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Geert GOracle dbaCommented:
create table statements:

create table gt_add_isbns ( 
  isbn varchar2(50 char) , 
  error_message varchar2(500 char), 
  filename varchar2(256 char) 
  );

 create table table1 ( 
  node_id integer, 
  category varchar2(50 char), 
  qname_id varchar2(50 char), 
  string_value varchar2(50 char) 
  );

 create table table2 ( 
  node_id integer, 
  value varchar2(50 char), 
  qname_id varchar2(50 char), 
  long_value integer 
  );

 create table table3 ( 
  id integer, 
  name_id integer 
  );

 create table table_assoc ( 
  child_node_id integer, 
  parent_node_id integer);

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
All but the first query seem to access the same tables over and over.  I would look at trying to access the tables once with a single query.  I don't know your tables, data or requirements so I doubt I'll be able to provide a copy/paste solution.

If you can create a really simple test case that shows what you are tying to accomplish we might be able to help with a runnable test case.

>>I dont have oracle login.I think only access to those guys who are working in oracle.

How are you expected to provide working code if you don't have access to a system to test on?
0
 
Geert GOracle dbaCommented:
>>I dont have oracle login.I think only access to those guys who are working in oracle.

Might be better to let those guys ask the questions ?
0
 
chaituu chaituAuthor Commented:
Please check the DDL's and the respective tables sample data.

there are some other extra columns are there in TEMP_NODE_PROPERTIES but it is not required here to get the final put.

if you run the GT_ADD_ISBNS table  then the final output will be shown against each record.

created the account in livesql.oracle but couldnot login due to unknown  errors from that server itself.so pasted all the tables and the sample data.


<code>
SET SERVEROUTPUT ON;
DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;  
FINAL_FILNAME VARCHAR2(2000);
V_ERRORMESSAGE VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

CURSOR C2(v_isbn in varchar2) IS
SELECT AN.ID NODE_ID
            FROM
            TEMP_NODE_PROPERTIES ANP,
            TEMP_NODE_PROPERTIES ANPP,
            TEMP_NODE AN
            WHERE
        ANP.NODE_ID=AN.ID AND
      ANPP.NODE_ID=ANP.NODE_ID AND
      AN.TYPE_QNAME ='asset' AND
   (ANP.STRING_VALUE ='uPDF' AND
    ANPP.STRING_VALUE ='Entire PDF')  AND
    ANP.TYPE_QNAME='categories'  AND
             ANP.NODE_ID IN(SELECT CHILD_ID
                  FROM TEMP_CHILD_ASSOC START WITH PARENT_ID IN(
                 SELECT CHILD_ID FROM TEMP_CHILD_ASSOC WHERE NAME in(v_isbn))
                      CONNECT BY PRIOR CHILD_ID = PARENT_ID);


BEGIN
--Iterating all Products
FOR R1 IN C1
LOOP

FINAL_FILNAME :='';
BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID
FROM TEMP_NODE AN,
        TEMP_NODE_PROPERTIES ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.TYPE_QNAME = 'product' AND
ANP.STRING_VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;
V_FINAL_ERRORMESSAGE :='';

--To check Whether Product inside the assets are having the 'category' is applied or not
FOR R2 IN C2(R1.ISBN)
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  

BEGIN
--In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
SELECT ANP.STRING_VALUE  into V_FILENAME
            FROM
            TEMP_NODE_PROPERTIES ANP
            WHERE
       ANP.TYPE_QNAME='name'  AND
       ((
       ANP.STRING_VALUE LIKE '%pdf%'  AND
       ANP.STRING_VALUE NOT LIKE '%pod%')
       )  AND
       ANP.NODE_ID=R2.NODE_ID ;

    FINAL_FILNAME := FINAL_FILNAME  || V_FILENAME ||',';
   
    DBMS_OUTPUT.PUT_LINE('R1.ISBN:'|| R1.ISBN || ':R2.NODE_ID:' || R2.NODE_ID ||  ':FILENAME:'|| FINAL_FILNAME);

   EXCEPTION WHEN
     NO_DATA_FOUND THEN
     V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  R2.NODE_ID || ':Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;';
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;


     END;--Iterating for each NODEID

END LOOP;--Iterating the assets[Nodes] for each product of catgeory

    DBMS_OUTPUT.PUT_LINE('R1.ISBN:' || R1.ISBN ||'::V_CATEGORY_COUNT:' || V_CATEGORY_COUNT);

 IF(V_CATEGORY_COUNT  = 0) THEN
     UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=  'Category is not applied to none of the Assets for this Product'  WHERE ISBN= R1.ISBN;
   END IF;  


EXCEPTION WHEN
NO_DATA_FOUND THEN
      UPDATE GT_ADD_ISBNS SET CATEGORY11_ERROR_MESSAGE=   'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

   DBMS_OUTPUT.PUT_LINE( R1.ISBN || '::Final documents:'||REGEXP_REPLACE((FINAL_FILNAME), ',$', ''));
      UPDATE GT_ADD_ISBNS SET CATEGORY11_FILENAME=FINAL_FILNAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;
</code>

<code>

--------------------------------------------------------
--  File created - Wednesday-February-21-2018  
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table TEMP_CHILD_ASSOC
--------------------------------------------------------

  CREATE TABLE "TEMP_CHILD_ASSOC" ("PARENT_ID" VARCHAR2(20), "CHILD_ID" VARCHAR2(20), "NAME" VARCHAR2(2000))
REM INSERTING into TEMP_CHILD_ASSOC
SET DEFINE OFF;
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1141','1143','9780748700585');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1202','testfolder1');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1143','1203','testfolder2');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1341','1343','9780748700587');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1343','1344','folder');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1223','9781424005468_p01_covpdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1226','9781424005468_p01_hipdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1229','9781424005468_p01_pod.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1202','1232','9781424005468_p01_pod_chaitu.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1344','1345','x.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1344','1346','y.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1441','1443','9780748700588');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1241','1243','9780748700586');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1243','1244','testfolder');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1244','1245','john_testpdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1244','1246','stab_pdf.sit');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1443','1444','9781424005468_p01_hipdf.zip');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1541','1543','9780748700589');
Insert into TEMP_CHILD_ASSOC (PARENT_ID,CHILD_ID,NAME) values ('1543','1544','xyz.zip');

</code>

<code>
--------------------------------------------------------
--  File created - Wednesday-February-21-2018  
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table TEMP_NODE
--------------------------------------------------------

  CREATE TABLE "TEMP_NODE" ("ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20))
REM INSERTING into TEMP_NODE
SET DEFINE OFF;
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1143','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1202','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1203','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1226','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1232','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1229','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1244','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1223','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1243','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1245','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1246','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1343','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1344','folder');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1345','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1346','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1443','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1444','asset');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1543','product');
Insert into TEMP_NODE (ID,TYPE_QNAME) values ('1544','asset');
</code>


<code>
--------------------------------------------------------
--  File created - Wednesday-February-21-2018  
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table TEMP_NODE_PROPERTIES
--------------------------------------------------------

  CREATE TABLE "TEMP_NODE_PROPERTIES" ("NODE_ID" VARCHAR2(20), "TYPE_QNAME" VARCHAR2(20), "STRING_VALUE" VARCHAR2(300))
REM INSERTING into TEMP_NODE_PROPERTIES
SET DEFINE OFF;
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1143','product','9780748700585');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1223','name','9781424005468_p01_hipdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','name','9781424005468_p01_covpdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1226','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1243','product','9780748700586');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1244','folder',null);
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1245','name','john_testpdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1246','name','stab_pdf.sit');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1343','product','9780748700587');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1344','folder',null);
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1345','name','x.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','categories','uPDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','categories','Entire PDF');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1346','name','y.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1443','product','9780748700588');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1444','name','9781424005468_p01_hipdf.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1543','product','9780748700589');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','name','xyz.zip');
Insert into TEMP_NODE_PROPERTIES (NODE_ID,TYPE_QNAME,STRING_VALUE) values ('1544','categories','Image');

</code>

<code>
--------------------------------------------------------
--  File created - Wednesday-February-21-2018  
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table GT_ADD_ISBNS
--------------------------------------------------------

  CREATE TABLE "GT_ADD_ISBNS" ("ISBN" VARCHAR2(15), "CATEGORY11_FILENAME" VARCHAR2(2000), "CATEGORY11_ERROR_MESSAGE" VARCHAR2(2000))
REM INSERTING into GT_ADD_ISBNS
SET DEFINE OFF;
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700585','9781424005468_p01_covpdf.zip,9781424005468_p01_hipdf.zip,',null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700586','stab_pdf.sit,john_testpdf.zip,',null);
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700587',null,'Category is applied for this Product But for the asset:1346:Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;Category is applied for this Product But for the asset:1345:Documents[LIKE %pdf% AND LIKE %pod% ] were not found ;');
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700588',null,'Category is not applied to none of the Assets for this Product');
Insert into GT_ADD_ISBNS (ISBN,CATEGORY11_FILENAME,CATEGORY11_ERROR_MESSAGE) values ('9780748700589',null,'Category is not applied to none of the Assets for this Product');
</code>
0
 
Geert GOracle dbaCommented:
sorry, too busy, yer still processing item per item
0
 
chaituu chaituAuthor Commented:
nobody has not given best solution.
0
 
Geert GOracle dbaCommented:
the best solution is by someone with access to your system
without access to the system, nothing can be verified

we try to help you by giving advice, but in the end it's you who has to do it
tuning requires lots of trials/attempts and verifications

we hire a tuning expert who comes in 1 day per week
not only do the databases get tuned, but devs get educated too
it's a process without end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.