Home » SQL & PL/SQL » SQL & PL/SQL » Inserting a PL/SQL block
Inserting a PL/SQL block [message #190022] Tue, 29 August 2006 01:30 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a sql file which exports a CSV file. I have to use a variable which carries a DECODED value.
Following is the file. Please advice as where can I insert the pl/sql block. I tried doing this but its giving error all the time.
******************************************************************************/
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF

DEFINE csvfile = &1
DEFINE rptid = &2

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500

SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &csvfile

--
-- Column Headings
--
--
-- Write out Report Indentifier
--
SELECT '&rptid'||','
  FROM DUAL;
-- Column Headings
SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,'    FROM DUAL;
--
-- Detail Information
--

SPOOL OFF
SET TERMOUT ON

PROMPT Data extraction complete.
PROMPT


My pl/sql block is :
DECLARE
v_date = DATE := TO_CHAR(SYSDATE,'dd-mon-yy');
BEGIN
SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                                2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                                3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                                4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                                5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                                6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                            TO_CHAR(SYSDATE-7,'dd-mon-yy')) INTO v_date FROM DUAL;
END;

I have to use the value of the variable v_date in the select statement later which is used to show the column heading.

Please advice.

Thanks,Mona
Re: Inserting a PL/SQL block [message #190023 is a reply to message #190022] Tue, 29 August 2006 01:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


i think this may be the reason for the error.

Quote:

v_date = DATE := TO_CHAR(SYSDATE,'dd-mon-yy');



check if this works.

 v_date date :=to_char(sysdate,'dd-mon-yy');



regards,
Re: Inserting a PL/SQL block [message #190025 is a reply to message #190023] Tue, 29 August 2006 01:38 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks, I missed that. But the error is not because of that. I have changed that. Please suggest me where exactly can I insert the PL/SQL statement since its spooling the result into CSV file.


Thanks,
Mona
Re: Inserting a PL/SQL block [message #190029 is a reply to message #190022] Tue, 29 August 2006 01:49 Go to previous messageGo to next message
gopi_ora
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,
One possible solution is ,instead of inserting the PL/SQL block create a stored function with the same block and call the funtion from the select statement.

Example:

create function chk_date return date is
v_date DATE := TO_CHAR(SYSDATE,'dd-mon-yy');
BEGIN
SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mon-yy'),
2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
TO_CHAR(SYSDATE-7,'dd-mon-yy')) INTO v_date FROM DUAL;
return v_date;
END;
/

Then in the sqlplus

select chk_date from dual;

Hope this help.

Thanks.
Re: Inserting a PL/SQL block [message #190034 is a reply to message #190029] Tue, 29 August 2006 01:58 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Actually you are right but I can't create any object like function or procedure. I have to do this through the sql file only.

Re: Inserting a PL/SQL block [message #190100 is a reply to message #190034] Tue, 29 August 2006 04:59 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Can anyone advice me what should I do. I have no other alternative but to use the variable in the sql file. I can't use any function.

Please advice.

Thanks,
Mona
Re: Inserting a PL/SQL block [message #190115 is a reply to message #190100] Tue, 29 August 2006 05:27 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
not sure if this is what you need =)


FRGT_UAT@foscopy:SQL>col head new_val vhea
FRGT_UAT@foscopy:SQL>SELECT DECODE(TO_CHAR(SYSDATE,'d'),1,TO_CHAR(SYSDATE,'dd-mo
n-yy'),
  2                                  2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
  3                                  3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
  4                                  4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
  5                                  5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
  6                                  6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
  7                              TO_CHAR(SYSDATE-7,'dd-mon-yy')) head
  8  from dual
  9  /

HEAD
---------
27-aug-06

FRGT_UAT@foscopy:SQL>define vhea
DEFINE VHEA            = "27-aug-06" (CHAR)
FRGT_UAT@foscopy:SQL>

Re: Inserting a PL/SQL block [message #190118 is a reply to message #190115] Tue, 29 August 2006 05:32 Go to previous message
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
Member
I am assuming that you need v_date alongwith column headings. so just check this below code. Hope this helps you.

scott@ORACLEDB> SELECT 'LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,' || dd
  2    FROM (SELECT DECODE (TO_CHAR (SYSDATE, 'd'),
  3                         1, TO_CHAR (SYSDATE, 'dd-mon-yy'),
  4                         2, TO_CHAR (SYSDATE - 1, 'dd-mon-yy'),
  5                         3, TO_CHAR (SYSDATE - 2, 'dd-mon-yy'),
  6                         4, TO_CHAR (SYSDATE - 3, 'dd-mon-yy'),
  7                         5, TO_CHAR (SYSDATE - 4, 'dd-mon-yy'),
  8                         6, TO_CHAR (SYSDATE - 5, 'dd-mon-yy'),
  9                         TO_CHAR (SYSDATE - 7, 'dd-mon-yy')
 10                        ) dd
 11            FROM DUAL),
 12         DUAL
 13  /

'LEADTIME,ITEM,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,'||DD
----------------------------------------------------------
LEADTIME,Item,SUBORD,NONEWSUPPLYDATE,AVG_FCST,OH,27-aug-06

scott@ORACLEDB> 
Previous Topic: Encryption problem while using Dbms_Crypto
Next Topic: query about INSERT
Goto Forum:
  


Current Time: Sun Dec 04 19:01:49 CST 2016

Total time taken to generate the page: 0.07574 seconds