Home » SQL & PL/SQL » SQL & PL/SQL » Using Bind variable
Using Bind variable [message #195185] Wed, 27 September 2006 06:11 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to use a variable v_startdate which will store a date value from the select statement. I have to then use that value in printing heading.
 
VARIABLE g_heading VARCHAR2(2048)

DECLARE
v_startdate varchar2(10):= trunc(sysdate); 
BEGIN
   select startdate into v_startdate from table1 where rownum=1;
  :g_heading := 'PLAN_ID,Purch,DESCR,ITEM,AVG_HIST,COLUMNHEADER,';
  FOR i IN 0..27 LOOP
   :g_heading := :g_heading||TO_CHAR(TO_DATE(v_startdate,'MM/DD/RRRR')+(7*i),'dd-Mon-yy')||',';
    
  END LOOP;
  :g_heading := :g_heading ||'SUBORD,OH,S/R,QTY,SCHEDDATE'||',';
  
END;


The above code is not working. Please advice as how can I store the value of the startdate in a variable to include in :g_heading.

Thanks,
Mona

/
Re: Using Bind variable [message #195187 is a reply to message #195185] Wed, 27 September 2006 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
"Not working" means what?
It errors?
It returns a value, but not the one you are expecting?
It sets fire to your cpu?

Give us a clue.
Re: Using Bind variable [message #195190 is a reply to message #195187] Wed, 27 September 2006 06:21 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

It gives no answer.
The heading comes all blank.

I am mainly interested here to make the below query run and hence :g_heading would give the desired value.
select startdate into v_startdate from table1 where rownum=1;

If I leave this part, its running and giving desired answer.
Please help me to understand as how can I run the above query in this scenario.



Thanks,
Mona

[Updated on: Wed, 27 September 2006 06:41]

Report message to a moderator

Re: Using Bind variable [message #195193 is a reply to message #195190] Wed, 27 September 2006 06:39 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

It gives no answer.

Well, you haven't asked it to output an answer. (along with various other issues) Is this the actual code?

[Updated on: Wed, 27 September 2006 06:39]

Report message to a moderator

Re: Using Bind variable [message #195194 is a reply to message #195193] Wed, 27 September 2006 06:42 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

This is part of the code.

I am trying to see the answer by the following :
SPOOL &csvfile
SELECT :g_heading FROM DUAL;

Re: Using Bind variable [message #195201 is a reply to message #195194] Wed, 27 September 2006 06:50 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
try tidying up your ode first.

1. Do not assign a date value to a string which you then convert back to a date, then back to a string.

Since you have populated a bind variable, just check the results by printing the variable:

DECLARE
v_startdate date:= trunc(sysdate);
BEGIN
   select startdate into v_startdate from table1 where rownum=1;
  :g_heading := 'PLAN_ID,Purch,DESCR,ITEM,AVG_HIST,COLUMNHEADER,';
  FOR i IN 0..27 LOOP
   :g_heading := :g_heading||TO_CHAR(v_startdate+(7*i),'dd-Mon-yy')||',';
  END LOOP;
  :g_heading := :g_heading ||'SUBORD,OH,S/R,QTY,SCHEDDATE'||',';
END;
/
print :g_heading

1 other thing, you most likely want to add an order by to your select for the value to be any real use.

[Updated on: Wed, 27 September 2006 06:56]

Report message to a moderator

Re: Using Bind variable [message #195205 is a reply to message #195185] Wed, 27 September 2006 06:57 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Try this code

v_startdate:=TO_CHAR(trunc(sysdate),'MM/DD/RRRR');
DECLARE
v_startdate varchar2(10):= TO_CHAR(trunc(sysdate),'MM/DD/RRRR');
BEGIN
select TO_CHAR(trunc(startdate),'MM/DD/RRRR') into v_startdate from table1 where rownum=1;
:g_heading := 'PLAN_ID,Purch,DESCR,ITEM,AVG_HIST,COLUMNHEADER,';
 FOR i IN 0..27 LOOP
  :g_heading := :g_heading||TO_CHAR(TO_DATE(v_startdate,'MM/DD/RRRR')+(7*i),'dd-Mon-yy')||',';
  
 END LOOP;
  :g_heading := :g_heading ||'SUBORD,OH,S/R,QTY,SCHEDDATE'||',';

END;


Hope it will work for u.

Sandy

Re: Using Bind variable [message #195206 is a reply to message #195205] Wed, 27 September 2006 06:59 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Sandy,
why would you convert a date to text, back to a date then back to text? Pointless.
Re: Using Bind variable [message #195207 is a reply to message #195201] Wed, 27 September 2006 07:00 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks JSI2001,
It worked.

Regards,
Mona
Previous Topic: Unable to access DBMS_PIPE from a procedure
Next Topic: case issue
Goto Forum:
  


Current Time: Tue Dec 06 06:19:12 CST 2016

Total time taken to generate the page: 0.07613 seconds