date issue within a procedure

hi
again i have date issue , i don't know how to solve it
i created a long procedure having cursor with creteria of date
 , when i execute it , it perform nothing cause the curosr gives no record
i think date problem
to investigate it , i created this simple procedure

create or replace procedure fog (f_c date,f_t date) is


 Cursor c_dent IS 
 select tran_no2,head_desc , ent_benif , ent_location From master_gen_ledger
 Where 
doc_date >= to_date(f_c,'YYYY-MM-DD') and doc_date <= to_date(f_t,'YYYY-MM-DD');
vd  c_dent%rowtype;
    
begin
    OPEN c_dent;
         DBMS_OUTPUT.put_line(' cursor  open   ' );  
 
        loop
         fetch c_dent into vd;
         exit when c_dent%notfound;
                DBMS_OUTPUT.put_line(' Entry No  ' || vd.tran_no2 );  
 
 END loop;

        close c_dent;
           
dbms_output.put_line(sql%rowcount || ' records of ..Msters');  
  
end;

Open in new window


procedure created

then
exec fog(to_date('2015-08-01','yyyy-mm-dd'),to_date('2015-08-31','yyyy-mm-dd'));
it run , but gives only the 1st message
"cursor  open"
it means the cursor is empty
how i can correct the date creteria
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Why is there a TO_DATE inside the procedure?  The parameter you passed in was a date to start with.

The cursor should be:

Cursor c_dent IS
 select tran_no2,head_desc , ent_benif , ent_location From master_gen_ledger
 Where
doc_date >= f_c and doc_date <= f_t,;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
Then why not take it further.  I find that cursor for loops do so much of the work for you.  Why not take advantage of that?  All the code you have could simplify to this:
CREATE OR replace PROCEDURE Fog (f_c DATE, 
                                 f_t DATE) 
IS 
BEGIN 
  FOR vd IN 
  ( 
         SELECT tran_no2, 
                head_desc , 
                ent_benif , 
                ent_location 
         FROM   master_gen_ledger 
         WHERE  doc_date f_c 
         AND    doc_date <= f_t) 
  LOOP 
    dbms_output.Put_line ('got trans - ' 
    || vd.tran_no2); 
  END LOOP; 
  dbms_output.Put_line(SQL%rowcount 
  || ' records of ..Msters'); 
END;

Open in new window

0
NiceMan331Author Commented:
yes , it is ok for my simple procedure
but when i go to the complex one , ( target to insert from cursor to a table , no records inserted )
how i can know the record count of the cursor ?
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.

slightwv (䄆 Netminder) Commented:
>>how i can know the record count of the cursor ?

You will need to create a counter variable and increment it inside the cursor loop.

v_counter := v_counter + 1;
0
NiceMan331Author Commented:
i cannot know the record count before open it ?
0
slightwv (䄆 Netminder) Commented:
>>i cannot know the record count before open it ?

No.  Think about it:  How do you know how many pages are in a book without opening it?

Cursors are processed serially so you cannot even jump to the last page of the book.  You need to go row by row through it and only to the next record.  You cannot even go backwards.
0
NiceMan331Author Commented:
ok , now the procedure is ok
just for knowledgment ,
why in the code of johnos in my previous question become like this :
 sql_string := ' tran_type = 2 and tr_date >= to_date(''' || to_char(:BASIC_BLOCK.D_from,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and tr_date <= to_date(''' || to_char(:BASIC_BLOCK.D_to,'YYYY-MM-DD') || ''',''YYYY-MM-DD'') and trans_acc_no = '|| :BASIC_BLOCK.acc || ' and cost_center between  ' ||:BASIC_BLOCK.cntr_frm|| ' and ' ||:BASIC_BLOCK.cntr_to||'' ;

Open in new window

that where in the form
while here he used the date parameter directly without any to_date or to_char ?
0
NiceMan331Author Commented:
sorry , it was like this
to_date(''' || to_char(:BASIC_BLOCK.D_from,'YYYY-MM-DD') || ''',''YYYY-MM-DD'')
0
johnsoneSenior Oracle DBACommented:
Counter is the best way to go.

The row count for a select statement isn't known until all the records are fetched.

The way I have typically seen SQL%ROWCOUNT used is to determine the number of rows inserted/updated/deleted by a single statement.  Usually just to confirm that at least one row was affected.
0
johnsoneSenior Oracle DBACommented:
Because you were constructing a string that held the SQL statement.  You cannot put a date variable into a SQL statement it is a binary format variable.

Within a PL/SQL procedure, the variable will be used as a bind variable behind the scenes and can be passed in the native format.
0
NiceMan331Author Commented:
The way I have typically seen SQL%ROWCOUNT used is to determine the number of rows inserted/updated/deleted by a single statement.  Usually just to confirm that at least one row was affected.

it was suggested by flow01 in a previous question , and yes , it count one record only
0
slightwv (䄆 Netminder) Commented:
No points please.

>>while here he used the date parameter directly without any to_date or to_char ?

Because the variables are already DATE data types.

You only need to_char when converting a DATE to a VARCHAR2 (string).
You need to_date when converting a VARCHAR2 (string) to a DATE.

You want to compare the same data types to one another.

In your where clause you want to get out of the habit of trying to compare a DATE to a VARCHAR2 to a NUMBER.

Compare dates to dates, strings to strings and numbers to numbers and you'll NEVER go wrong.
0
slightwv (䄆 Netminder) Commented:
>>Because you were constructing a string that held the SQL statement.

Yes, what johnsone said!
0
NiceMan331Author Commented:
ok , thanx to both
but why no points , slighwv
0
slightwv (䄆 Netminder) Commented:
>>but why no points , slighwv

I didn't really add anything to deserve them given the question asked.  Johnsone posted all the code.

I just saw johnsone hadn't posted back and it was a simple counter post.  Not really point worthy.
0
NiceMan331Author Commented:
johnos
Then why not take it further.  I find that cursor for loops do so much of the work for you.  Why not take advantage of that?  All the code you have could simplify to this:
this seemed simpler than cursor as you said
but could use it also to insert into a table ?
0
johnsoneSenior Oracle DBACommented:
An insert statement doesn't use a looping structure, so I'm not sure what you are asking.

If you are asking if you can do an insert inside of the loop, then the answer is yes.  Like this:
CREATE OR replace PROCEDURE Fog (f_c DATE, 
                                 f_t DATE) 
IS 
BEGIN 
  FOR vd IN 
  ( 
         SELECT tran_no2, 
                head_desc , 
                ent_benif , 
                ent_location 
         FROM   master_gen_ledger 
         WHERE  doc_date f_c 
         AND    doc_date <= f_t) 
  LOOP 
    INSERT INTO sometab VALUES 
                ( 
                            vd.tran_no2 
                ); 
     
    dbms_output.Put_line ('got trans - ' 
    || vd.tran_no2); 
  END LOOP; 
  dbms_output.Put_line(SQL%rowcount 
  || ' records of ..Msters'); 
END;

Open in new window


It is the same way you would do it in your original post.  I find the syntax easier to write and read.  Plus, you don't have to worry about the cursor.  It is taken care of for you.  You don't need to worry about having an error handler to always check to see that the cursor is closed.  It really takes care of a lot for you.
0
NiceMan331Author Commented:
Because my procedure success , it consume 30 seconds to insert from table into another one , while it select within a seconds , I'm looking about how to reduce this time of insert
0
johnsoneSenior Oracle DBACommented:
Look into BULK COLLECT.  That should speed things up.

Also, if you aren't doing anything within the loop other than inserting, why do you even need a procedure?
INSERT INTO sometab 
SELECT tran_no2, 
       head_desc , 
       ent_benif , 
       ent_location 
FROM   master_gen_ledger 
WHERE  doc_date >= :f_c 
AND    doc_date <= :f_t;

Open in new window

That would eliminate the procedure call, cursor and a whole bunch of other things that take time.
0
johnsoneSenior Oracle DBACommented:
Alternately, if you need a procedure for something in forms, simplify it
CREATE OR replace PROCEDURE Fog (f_c DATE, 
                                 f_t DATE) 
IS 
BEGIN 
    INSERT INTO sometab 
    SELECT tran_no2, 
           head_desc, 
           ent_benif, 
           ent_location 
    FROM   master_gen_ledger 
    WHERE  doc_date >= f_c 
           AND doc_date <= f_t; 
END; 

Open in new window

0
NiceMan331Author Commented:
I used the procedure because it had to check some condition first in the table , then to insert
0
slightwv (䄆 Netminder) Commented:
>>while it select within a seconds

You understand why, right?  The blocks you need are likely cached in memory so every time you re-select them there is no disk i/o.

When you do an insert, there is redo/undo generated in addition to the actual blocks that have changed.

What and why are you inserting?  Given all your previous questions it is about getting data to a form.  I sure hope you are not inserting into a table for the purpose of then querying it to display in a form.

That would likely be a bad thing to do...
0
NiceMan331Author Commented:
No no , it is not for just selecting , now I'm using direct select even within the form which become easier and faster ,       I have problem in our accounting system , it is big story but I will simplify it like this :                                       Our transactions posted in tables of the main oracle user , then , every period we have to add some adjustment transactions which is for reconsilation purposes only , which must be removed before starting a transaction of the next period , total set of regular transactions and the temporary adjustment called , period end records , we need it for later on reporting only , the company of the software fail to provide a solution to keep both sets in same user and design reports for our purposes , they provide foolish solution which is to every period export this 2 sets of transactions to a new oracle user , do your report from there , and then continue in the main user for the regular transactions after deleting the adjusted one ,          Here , I created one table to insert into it the adjusted sets  in same user , then I can call any time for any period with any creteria , hope I explained well
0
slightwv (䄆 Netminder) Commented:
>>hope I explained well

Well enough.  As long as you weren't creating a temp table for your forms.

The insert into select will likely be the fastest way.
0
NiceMan331Author Commented:
I will not do any temp again in my life , even in the form of my the very earlier question I adjust it to direct select , I found that scenario easy and very fast
0
slightwv (䄆 Netminder) Commented:
Temp tables have their place.  In Oracle they are Global Temporary Tables (GTTs).  You just need to know when they are needed.  In Oracle they are not needed as quick and dirty tables like other databases.

I use GTTs in production in a couple of places.  They were the correct tool to solve a specific problem.
0
NiceMan331Author Commented:
Problem Such what ? Just to have an example if possible
0
slightwv (䄆 Netminder) Commented:
>>Problem Such what ?

Sorry but I don't understand what you are asking.
0
NiceMan331Author Commented:
What example of problems where using GTT may solve it
0
johnsoneSenior Oracle DBACommented:
One place that I use GTT is in conjunction with statement level triggers.  Row level trigger puts the rowids of the rows processed into a GTT and then statement level trigger does it's processing.  This is a classic way to prevent a mutating table error.
1
slightwv (䄆 Netminder) Commented:
>>What example of problems where using GTT may solve it

I use them when I need to store a subset of data that I use more than once further in the process and executing the query against the main table isn't efficient.

I also use them for some package calls that populate a table.  

ctx_doc.highlight for example:  Syntax 2: Result Table Storage
http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdocpkg.htm#CCREF0706
0
slightwv (䄆 Netminder) Commented:
I forgot to include the main reason I use them:
When multiple users are involved along with the subsets and package calls.  With a GTT, only the session that inserts the data can see the data and the data is automatically deleted when the session ends.

Makes multi-user apps much easier when you need subsets of data.
0
NiceMan331Author Commented:
for my side i dislike to use to store the data temporary in a table then query it , because i can't see the updated data directly unless i do to update it in the GTT
but i started to use that method since mark advised for it in the beginning of my date issue with a view
honestly i found a lot of difficulties because as i said , that view in that example responsible to show sales transactions , and you know , sales data updated time to time and minute to minute
but now it is ok for me , i changed it to direct select from the tables
johnson
could you please have an example of that statement level trigger
0
johnsoneSenior Oracle DBACommented:
Basically a statement level trigger does not contain the FOR EACH ROW clause.  There are many uses for them.  One of the common uses is to avoid a mutating table error.  This occurs when you need to select or update a row in the table that the trigger fires on.  If you want a code example, just search around for mutating table, ORA-04091.
0
NiceMan331Author Commented:
ok , thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.