Home » SQL & PL/SQL » SQL & PL/SQL » bad bind variables while creating a stored procedure
bad bind variables while creating a stored procedure [message #279195] Wed, 07 November 2007 05:13 Go to next message
gadege
Messages: 4
Registered: November 2007
Junior Member
Hi,
Need urgent help to resolve this issue.
Following is the code.

--   -----------------------------------------------------
--   -- Input parameters:
--   --            ALBQ 602071 20050913153100 53290
--   -- exec tab_select('ALBQ', '602071', '053290', '20050912153100')
--   -----------------------------------------------------
--
-- CREATE or REPLACE PROCEDURE tab_select IS
--       type curTyp is ref cursor;
--       tab_cv  curTyp;
--   
   CREATE or REPLACE PROCEDURE tab_select (IN_DPC_CODE IN CHAR, IN_SENSOR_ID IN NUMBER, IN_LAST_BLOCK_NUMBER IN NUMBER,
                        IN_TRAILER_DATE IN CHAR) IS
    
         sql_stmt varchar2(2000);
         sql_stmt2 varchar2(2000);
    
         IN_DPC_CODE            CHAR(4);
         IN_SENSOR_ID           CHAR(6);
         IN_TRAILER_DATE        CHAR(14);
         IN_LAST_BLOCK_NUMBER   number;        
--       in_Dpc_Code            logfc10t.DPC_CODE_ORIG%type;
--       in_Sensor_id           logfc10t.SENSOR_ID%type;
--       in_Trailer_Date_Time   logfc10t.TRAILER_AYE%type;
--       in_Last_Block_Number   logfc10t.LAST_BLOCK_NUMBER%type;
    
--       IN_DPC_CODE            CHAR(4) := 'ALBQ';
--       IN_SENSOR_ID           CHAR(6) := '602071';
--       IN_TRAILER_DATE        CHAR(14) := '20050913153100';
--       IN_LAST_BLOCK_NUMBER   number := 53290;

--       IN_DPC_CODE            logfc10t.DPC_CODE_ORIG%type := 'ALBQ';
--       IN_SENSOR_ID           logfc10t.SENSOR_ID%type := '602071';
--       IN_TRAILER_DATE        CHAR(14) := '20050913153100';
--       IN_LAST_BLOCK_NUMBER   logfc10t.LAST_BLOCK_NUMBER%type := 053290;

         OUT_ROWNUM             number(8) := 0;
         OUT_DPC_CODE_ORIG      CHAR(4);
         OUT_SENSOR_ID          CHAR(6);
         OUT_HEADER_DATE        CHAR(14);
         OUT_FIRST_BLOCK_NUMBER number(9) := 0;
         OUT_LAST_BLOCK_NUMBER  number(9) := 0;
         OUT_RECORD_QUANTITY    number(8) := 0;
         OUT_FILE_ID            CHAR(10);
         OUT_FILE_NAME          CHAR(30);
         OUT_TRACE_SEQUENCE_NO  number(5) := 0;
         OUT_TRAILER_DATE       CHAR(14);
         OUT_PROCESSED_DATE     CHAR(14);
         OUT_ROW_NUMBER         number(8) := 0;

    BEGIN 
 
     DBMS_OUTPUT.DISABLE;
     DBMS_OUTPUT.ENABLE(1000000);

     DBMS_OUTPUT.PUT_LINE('****  ' );
     DBMS_OUTPUT.PUT_LINE('**** BEFORE sql_stmt Line: ' || ' ' ||
                                          IN_DPC_CODE  || ' ' ||
                                          IN_SENSOR_ID || ' ' ||
                                          IN_TRAILER_DATE || ' ' ||
                                          IN_LAST_BLOCK_NUMBER);
     DBMS_OUTPUT.PUT_LINE('****  ' );

     sql_stmt := 'SELECT rNum, dpcCode, sID, hDate, fBlock, ' ||                                      
                     'lBlock, recQty, fID, fName, ' ||                                                
                     'tSeqNo, sType, tDate, fStatCode, pDate, recCount, r ' ||                        
                   'FROM (SELECT rownum rNum, DPC_CODE_ORIG dpcCode, ' ||                             
                           'SENSOR_ID sID, ' ||                                                       
                           'to_char(HEADER_DATE, ''YYYYMMDDHH24MISS'') hDate, ' ||                    
                           'FIRST_BLOCK_NUMBER fBlock, ' ||                                           
                           'LAST_BLOCK_NUMBER lBlock, ' ||                                            
                           'RECORD_QUANTITY recQty, ' ||                                              
                           'FILE_ID fID, ' ||                                                         
                           'substr(FILE_NAME,1,30) fName, ' ||                                        
                           'TRACE_SEQUENCE_NO tSeqNo, ' ||                                            
                           'SENSOR_TYPE sType, ' ||                                                   
                           'to_char(TRAILER_DATE, ''YYYYMMDDHH24MISS'') tdate, ' ||                   
                           'NVL(FILE_STATUS_CODE, ''NL'') fStatCode, ' ||                             
                           'to_char(PROCESSED_DATE, ''YYYYMMDDHH24MISS'') pDate, '||                  
                           'NVL(RECIRCULATE_COUNT, 0) recCount, ' ||                                  
                           'ROW_NUMBER() ' ||                                                         
                              'OVER(ORDER BY LAST_BLOCK_NUMBER, TRAILER_DATE) r ' ||                  
                        'FROM logfc10t ' ||                                                           
                        'WHERE DPC_CODE_ORIG = :IN_DPC_CODE '  ||                                     
                          'AND SENSOR_ID = :IN_SENSOR_ID '  ||                                        
                          'AND (((TRAILER_DATE >= TO_DATE(:IN_TRAILER_DATE, ''YYYYMMDDHH24MISS''))' ||
                             'AND (LAST_BLOCK_NUMBER > :IN_LAST_BLOCK_NUMBER)) ' ||                        
                          ' OR (TRAILER_DATE > TO_DATE(:IN_TRAILER_DATE, ''YYYYMMDDHH24MISS'')))) ' ||
               'WHERE r = 1 ';                                                                        
    
     DBMS_OUTPUT.PUT_LINE('****  ' );
     DBMS_OUTPUT.PUT_LINE('**** BEFORE execute immediate Line: ');
--   p(sql_stmt);
--   Put_Xl_line(sql_stmt);
     DBMS_OUTPUT.PUT_LINE('****  ' );

         BEGIN
          EXECUTE IMMEDIATE sql_stmt
             INTO OUT_ROWNUM, OUT_DPC_CODE_ORIG, OUT_SENSOR_ID, OUT_HEADER_DATE,
                  OUT_FIRST_BLOCK_NUMBER, OUT_LAST_BLOCK_NUMBER, OUT_RECORD_QUANTITY,
                  OUT_FILE_ID, OUT_FILE_NAME, OUT_TRACE_SEQUENCE_NO, OUT_TRAILER_DATE,
                  OUT_PROCESSED_DATE, OUT_ROW_NUMBER 
            USING :IN_DPC_CODE, :IN_SENSOR_ID, :IN_TRAILER_DATE, :IN_LAST_BLOCK_NUMBER;
          EXCEPTION
             WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE('****  ' );
                  DBMS_OUTPUT.PUT_LINE('****  IMMED ' || sqlcode || '   ' || substr(sqlerrm,1,200));
                  DBMS_OUTPUT.PUT_LINE('****  ' );
         END;

     DBMS_OUTPUT.PUT_LINE('****  ' );
     DBMS_OUTPUT.PUT_LINE('**** AFTER execute immediate Line: ');
     DBMS_OUTPUT.PUT_LINE('****  ' );

        DBMS_OUTPUT.PUT_LINE ('*** Next Line: ' || OUT_ROWNUM || OUT_DPC_CODE_ORIG || OUT_SENSOR_ID ||
                   OUT_HEADER_DATE || OUT_FIRST_BLOCK_NUMBER || OUT_LAST_BLOCK_NUMBER ||
                   OUT_RECORD_QUANTITY  || OUT_FILE_ID || OUT_FILE_NAME ||
                   OUT_TRACE_SEQUENCE_NO  || OUT_TRAILER_DATE || OUT_PROCESSED_DATE || OUT_ROW_NUMBER);
    
        EXCEPTION
        WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('****  ' );
            DBMS_OUTPUT.PUT_LINE('****  ERROR ' || sqlcode || '   ' || substr(sqlerrm,1,200));
            DBMS_OUTPUT.PUT_LINE('****  ' );
    
     end;
  end tab_select;
/
set serveroutput on

exec tab_select('OMAN', '612628', '993610', '20070214141700')
--exec tab_select('OMAN', '612628', '6434', '20070214144700')
--exec tab_select('OMAN', '612628', '13098', '20070214150200')
--exec tab_select('OMAN', '612628', '19874', '20070214151700')
--exec tab_select('OMAN', '612628', '33398', '20070214154700')
-- quit

[EDITED by LF: added [code] tags]

[Updated on: Wed, 07 November 2007 05:27] by Moderator

Report message to a moderator

Re: bad bind variables while creating a stored procedure [message #279197 is a reply to message #279195] Wed, 07 November 2007 05:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Instead of
Quote:

'AND (((TRAILER_DATE >= TO_DATE(:IN_TRAILER_DATE, ''YYYYMMDDHH24MISS''))'
Use
Quote:

'AND (((TRAILER_DATE >= TO_DATE('''||IN_TRAILER_DATE ||''', ''YYYYMMDDHH24MISS''))'
Have a look at Forum Guide and use tags and copy paste the errors.

By
Vamsi

[Updated on: Wed, 07 November 2007 05:19]

Report message to a moderator

Re: bad bind variables while creating a stored procedure [message #279200 is a reply to message #279197] Wed, 07 November 2007 05:28 Go to previous messageGo to next message
gadege
Messages: 4
Registered: November 2007
Junior Member
Hi vamsi,
I tried with the syntax what you suggested, but still i am getting same following errors.

SQL> @gadegeNDS.sql

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TAB_SELECT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
89/19 PLS-00049: bad bind variable 'IN_DPC_CODE'
89/33 PLS-00049: bad bind variable 'IN_SENSOR_ID'
89/48 PLS-00049: bad bind variable 'IN_TRAILER_DATE'
89/66 PLS-00049: bad bind variable 'IN_LAST_BLOCK_NUMBER'
113/3 PLS-00103: Encountered the symbol "END"


thanks,
sreeni.
Re: bad bind variables while creating a stored procedure [message #279205 is a reply to message #279195] Wed, 07 November 2007 05:51 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
vamsi kasina: you want to totally outperform the application, right?

By the way, this is not a good idea:
 CREATE or REPLACE PROCEDURE tab_select (
   IN_DPC_CODE IN CHAR,
   IN_SENSOR_ID IN NUMBER,
   IN_LAST_BLOCK_NUMBER IN NUMBER,
   IN_TRAILER_DATE IN CHAR) IS
<some other variables>
 IN_TRAILER_DATE CHAR(14);
<code without initializing IN_TRAILER_DATE>
Why do you declare a local variable with the same name as a parameter?

Then, why not simply use
 'AND (((TRAILER_DATE >= :IN_TRAILER_DATE)'
(assuming that both TRAILER_DATE and IN_TRAILER_DATE have DATE type) and
 USING IN_DPC_CODE, IN_SENSOR_ID, IN_TRAILER_DATE, IN_LAST_BLOCK_NUMBER
However note, binding is done by POSITION of binded variable in SQL string, NOT by the identifier itself (so you have to bind variables with the same name as many times as they appear in SQL statement in the order of their appearance).

But, as I do not see any reason to have this SQL dynamic, you shall use static SQL instead (SELECT INTO).
Re: bad bind variables while creating a stored procedure [message #279206 is a reply to message #279195] Wed, 07 November 2007 05:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Questions


1.Why IN_DPC_CODE ,IN_SENSOR_ID , IN_TRAILER_DATE and IN_LAST_BLOCK_NUMBER re-declared ? (they are Input parameters , Dont need to declare bind variable , and those should be different from input variable)

IN_DPC_CODE            CHAR(4);
         IN_SENSOR_ID           CHAR(6);
         IN_TRAILER_DATE        CHAR(14);
         IN_LAST_BLOCK_NUMBER   number;   


2. why did You use Those 4 VCaribles again in sqlstatement (sql_stmt) insread of some BIND variable like :a, :b: ,... ( Donbt declare bind variables) ?

3. You Used those 4 variables againg in Execute immmediate statement with prefix ":" ? (prefix is not needed here)

USING :IN_DPC_CODE, :IN_SENSOR_ID, :IN_TRAILER_DATE, :IN_LAST_BLOCK_NUMBER;


Thumbs Up
Rajuvan

[Updated on: Wed, 07 November 2007 05:52]

Report message to a moderator

Re: bad bind variables while creating a stored procedure [message #279207 is a reply to message #279200] Wed, 07 November 2007 05:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check the syntax for EXECUTE IMMEDIATE.
Correct your code and try again.
If you still have problems, post your new code.

By
Vamsi

[Updated on: Wed, 07 November 2007 05:53]

Report message to a moderator

Re: bad bind variables while creating a stored procedure [message #279209 is a reply to message #279195] Wed, 07 November 2007 05:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ie ,

1.Remove first four declaration

2. Change statement like

  sql_stmt := 'SELECT rNum, dpcCode, sID, hDate, fBlock, ' ||                                      
                     'lBlock, recQty, fID, fName, ' ||                                                
                     'tSeqNo, sType, tDate, fStatCode, pDate, recCount, r ' ||                        
                   'FROM (SELECT rownum rNum, DPC_CODE_ORIG dpcCode, ' ||                             
                           'SENSOR_ID sID, ' ||                                                       
                           'to_char(HEADER_DATE, ''YYYYMMDDHH24MISS'') hDate, ' ||                    
                           'FIRST_BLOCK_NUMBER fBlock, ' ||                                           
                           'LAST_BLOCK_NUMBER lBlock, ' ||                                            
                           'RECORD_QUANTITY recQty, ' ||                                              
                           'FILE_ID fID, ' ||                                                         
                           'substr(FILE_NAME,1,30) fName, ' ||                                        
                           'TRACE_SEQUENCE_NO tSeqNo, ' ||                                            
                           'SENSOR_TYPE sType, ' ||                                                   
                           'to_char(TRAILER_DATE, ''YYYYMMDDHH24MISS'') tdate, ' ||                   
                           'NVL(FILE_STATUS_CODE, ''NL'') fStatCode, ' ||                             
                           'to_char(PROCESSED_DATE, ''YYYYMMDDHH24MISS'') pDate, '||                  
                           'NVL(RECIRCULATE_COUNT, 0) recCount, ' ||                                  
                           'ROW_NUMBER() ' ||                                                         
                              'OVER(ORDER BY LAST_BLOCK_NUMBER, TRAILER_DATE) r ' ||                  
                        'FROM logfc10t ' ||                                                           
                        'WHERE DPC_CODE_ORIG = :a '  ||                                     
                          'AND SENSOR_ID = :b '  ||                                        
                          'AND (((TRAILER_DATE >= TO_DATE(:c, ''YYYYMMDDHH24MISS''))' ||
                             'AND (LAST_BLOCK_NUMBER > :IN_LAST_BLOCK_NUMBER)) ' ||                        
                          ' OR (TRAILER_DATE > TO_DATE(:d, ''YYYYMMDDHH24MISS'')))) ' ||
               'WHERE r = 1 '; 


3. One more change like ..


EXECUTE IMMEDIATE sql_stmt
             INTO OUT_ROWNUM, OUT_DPC_CODE_ORIG, OUT_SENSOR_ID, OUT_HEADER_DATE,
                  OUT_FIRST_BLOCK_NUMBER, OUT_LAST_BLOCK_NUMBER, OUT_RECORD_QUANTITY,
                  OUT_FILE_ID, OUT_FILE_NAME, OUT_TRACE_SEQUENCE_NO, OUT_TRAILER_DATE,
                  OUT_PROCESSED_DATE, OUT_ROW_NUMBER 
            USING IN_DPC_CODE, IN_SENSOR_ID, IN_TRAILER_DATE, IN_LAST_BLOCK_NUMBER;


Thumbs Up
Rajuvan
Re: bad bind variables while creating a stored procedure [message #279210 is a reply to message #279205] Wed, 07 November 2007 06:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@flyboy
Nooooo Razz
I just confused about the bind variable, as OP doesn't contain the line number for that error.

By
Vamsi
Re: bad bind variables while creating a stored procedure [message #279226 is a reply to message #279195] Wed, 07 November 2007 07:03 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@vamsi: ok, I just noticed you removed binding, which I do not like nearly as much as
using dynamic SQL where static SQL could be used,
issuing TO_DATE to variable with DATE type,
storing dates as CHAR/VARCHAR2 variables,
and many similar practices not present in original code yet Wink
Re: bad bind variables while creating a stored procedure [message #279227 is a reply to message #279195] Wed, 07 November 2007 07:08 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
If this is your actual procedure, and this is an actual requirement and not simply an academic exercise in practicing nds, the follow the advice of flyboy in the last sentence of his post. i.e. there is no need for this to be done in NDS.
Re: bad bind variables while creating a stored procedure [message #279228 is a reply to message #279227] Wed, 07 November 2007 07:34 Go to previous messageGo to next message
gadege
Messages: 4
Registered: November 2007
Junior Member
Hi,
Thanks everybody. i made the changes as you people suggested and now it is working fine. But i got the following error while i have executed the procedure.

"ORA-01008: not all variables bound"

Procedure created.

SQL> exec tab_select('OMAN', '612628', '993610', '20070214141700')
****
**** BEFORE sql_stmt Line: OMAN 612628 20070214141700 993610
****
****
**** BEFORE execute immediate Line:
****
****
**** IMMED -1008 ORA-01008: not all variables bound
****
****
**** AFTER execute immediate Line:
****
*** Next Line: 000000

PL/SQL procedure successfully completed.
Re: bad bind variables while creating a stored procedure [message #279230 is a reply to message #279228] Wed, 07 November 2007 07:37 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:

i made the changes as you people suggested
There were several changes suggested (including a complete rewrite to avoid dynamic sql. Post your 'new' code (and can you remove any comments that don't actually describe process). Thanks
Re: bad bind variables while creating a stored procedure [message #279549 is a reply to message #279195] Fri, 09 November 2007 00:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


IMMED -1008 ORA-01008: not all variables bound


It looks like , you didn't supply the sufficient values in the Using Clause. Rather you copied my suggested code without even looking into what i was trying to say Smile

Do changes like these ..


sql_stmt := 'SELECT rNum, dpcCode, sID, hDate, fBlock, ' ||                                      
                     'lBlock, recQty, fID, fName, ' ||                                                
                     'tSeqNo, sType, tDate, fStatCode, pDate, recCount, r ' ||                        
                   'FROM (SELECT rownum rNum, DPC_CODE_ORIG dpcCode, ' ||                             
                           'SENSOR_ID sID, ' ||                                                       
                           'to_char(HEADER_DATE, ''YYYYMMDDHH24MISS'') hDate, ' ||                    
                           'FIRST_BLOCK_NUMBER fBlock, ' ||                                           
                           'LAST_BLOCK_NUMBER lBlock, ' ||                                            
                           'RECORD_QUANTITY recQty, ' ||                                              
                           'FILE_ID fID, ' ||                                                         
                           'substr(FILE_NAME,1,30) fName, ' ||                                        
                           'TRACE_SEQUENCE_NO tSeqNo, ' ||                                            
                           'SENSOR_TYPE sType, ' ||                                                   
                           'to_char(TRAILER_DATE, ''YYYYMMDDHH24MISS'') tdate, ' ||                   
                           'NVL(FILE_STATUS_CODE, ''NL'') fStatCode, ' ||                             
                           'to_char(PROCESSED_DATE, ''YYYYMMDDHH24MISS'') pDate, '||                  
                           'NVL(RECIRCULATE_COUNT, 0) recCount, ' ||                                  
                           'ROW_NUMBER() ' ||                                                         
                              'OVER(ORDER BY LAST_BLOCK_NUMBER, TRAILER_DATE) r ' ||                  
                        'FROM logfc10t ' ||                                                           
                        'WHERE DPC_CODE_ORIG = :a '  ||                                     
                          'AND SENSOR_ID = :b '  ||                                        
                          'AND (((TRAILER_DATE >= TO_DATE(:c, ''YYYYMMDDHH24MISS''))' ||
                             'AND (LAST_BLOCK_NUMBER > :d)) ' ||                        
                          ' OR (TRAILER_DATE > TO_DATE(:e, ''YYYYMMDDHH24MISS'')))) ' ||
               'WHERE r = 1 '; 


 EXECUTE IMMEDIATE sql_stmt
             INTO OUT_ROWNUM, OUT_DPC_CODE_ORIG, OUT_SENSOR_ID, OUT_HEADER_DATE,
                  OUT_FIRST_BLOCK_NUMBER, OUT_LAST_BLOCK_NUMBER, OUT_RECORD_QUANTITY,
                  OUT_FILE_ID, OUT_FILE_NAME, OUT_TRACE_SEQUENCE_NO, OUT_TRAILER_DATE,
                  OUT_PROCESSED_DATE, OUT_ROW_NUMBER 
            USING IN_DPC_CODE, IN_SENSOR_ID, IN_TRAILER_DATE, IN_LAST_BLOCK_NUMBER,IN_TRAILER_DATE;



Thumbs Up
Rajuvan.
Re: bad bind variables while creating a stored procedure [message #279550 is a reply to message #279195] Fri, 09 November 2007 00:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One more suggestion :

It is always good practice to use VARCHAR instead of CHAR

I think it is time to Oracle eliminate the Datatype CHAR Wink

Thumbs Up
Rajuvan.

[Updated on: Fri, 09 November 2007 00:10]

Report message to a moderator

Re: bad bind variables while creating a stored procedure [message #279558 is a reply to message #279195] Fri, 09 November 2007 00:45 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@rajavu1: I would rather suggest to use DATE datatype for storing dates and convert it to string using TO_CHAR function where needed.
Also issuing TO_DATE on DATE variable is not good (not only two useless conversions are done, it may cause unpredictable behaviour or even error - see Date difference thread)
Re: bad bind variables while creating a stored procedure [message #279562 is a reply to message #279195] Fri, 09 November 2007 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Flyboy ,

I agree that it is better to use date Datatype for storing Date . But i dont think any issue for storing dates as varchar2 and to use date functions whenever it is needed. I think it wont be nice to change the data type of input parameter especially if the code is from OP's Application . Even it may cause more issues ..

Secondly In the Actual /Suggested code , TO_DATE is not applied not on the date datatype but on the CHAR/VARCHAR2 .

Thumbs Up
Rajuvan.
Re: bad bind variables while creating a stored procedure [message #279574 is a reply to message #279562] Fri, 09 November 2007 01:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

But i dont think any issue for storing dates as varchar2 and to use date functions whenever it is needed.


Ahh - the innocence of youth.....

Here's one for you - If you store your dates in DATEs, you can guarantee, absolutely, that each one of them is a valid date/time.
You can't do that storing them in Varchar2s
Re: bad bind variables while creating a stored procedure [message #279578 is a reply to message #279195] Fri, 09 November 2007 02:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I even agreed on that

Quote:

I agree that it is better to use date Datatype for storing Date


But while saying ..

Quote:

But i dont think any issue for storing dates as varchar2 and to use date functions whenever it is needed.


I was specific to this Thread issue . But Pefect use of Character and date functions really matters even for this.

Thumbs Up
Rajuvan
Re: bad bind variables while creating a stored procedure [message #279579 is a reply to message #279578] Fri, 09 November 2007 02:50 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Am I missing something? am I being really obtuse (this is a genuine question, because it is NOT beyond the realms of possibility that I am misreading the scenario here) As far as I can see, there is absolutely no reason to be executing this as Dynamic sql. it is a simple SELECT ... INTO where the only parameters are operands. Surely we should be, as flyboy has already suggested, telling the OP to avoid dynamic SQL.
Previous Topic: Regarding append a 2 digit value to product value (merged)
Next Topic: using select statement wile creating sequence
Goto Forum:
  


Current Time: Fri Dec 09 23:04:16 CST 2016

Total time taken to generate the page: 0.11158 seconds