Home » SQL & PL/SQL » SQL & PL/SQL » passing value in a pl/sql block
passing value in a pl/sql block [message #241909] Thu, 31 May 2007 00:24 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to create this external table in pl/sql block passing the name of the directory and csv file.
I am trying right now only to pass name of directory but since this is working as a string(v), it doesn't
understands the passed value of inputdir.
DECLARE   
   v           VARCHAR2(1500);
   inputdir          VARCHAR2(100) := '&1';
BEGIN
   v:='CREATE TABLE ext
       (
          ITEM       VARCHAR2(25),
          TRANSDESC  VARCHAR2(50),
          WEEK1_QTY  NUMBER(15),
          WEEK2_QTY  NUMBER(15),
          WEEK3_QTY  NUMBER(15),
          WEEK4_QTY  NUMBER(15),
          WEEK5_QTY  NUMBER(15),
        )
       ORGANIZATION EXTERNAL
        (DEFAULT DIRECTORY inputdir
      ACCESS PARAMETERS
       (
      RECORDS DELIMITED BY NEWLINE
      SKIP 1
      FIELDS TERMINATED BY ","
      OPTIONALLY ENCLOSED BY ''"''
       )
      LOCATION(''di.csv'')
     )';
  EXECUTE IMMEDIATE v;
  END;
/



Please advice

Regards,
Mona
Re: passing value in a pl/sql block [message #241912 is a reply to message #241909] Thu, 31 May 2007 00:49 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Please ignore the above message as I have done this.
 DECLARE
    v           VARCHAR2(1500);
    inputdir          VARCHAR2(100) := '&1';
    inputfile          VARCHAR2(100) := '&2';
 BEGIN
    v:='CREATE TABLE exttemp
        (
           ITEM       VARCHAR2(25),
           TRANSDESC  VARCHAR2(50),
           WEEK1_QTY  NUMBER(15),
           WEEK2_QTY  NUMBER(15),
           WEEK3_QTY  NUMBER(15),
           WEEK4_QTY  NUMBER(15),
           WEEK5_QTY  NUMBER(15),
           WEEK6_QTY  NUMBER(15),
           WEEK7_QTY  NUMBER(15),
           WEEK8_QTY  NUMBER(15),
           WEEK9_QTY  NUMBER(15),
           WEEK10_QTY NUMBER(15),
           WEEK11_QTY NUMBER(15),
           WEEK12_QTY NUMBER(15),
           WEEK13_QTY NUMBER(15),
           WEEK14_QTY NUMBER(15),
           WEEK15_QTY NUMBER(15),
           WEEK16_QTY NUMBER(15),
           WEEK17_QTY NUMBER(15),
           WEEK18_QTY NUMBER(15),
           WEEK19_QTY NUMBER(15),
           WEEK20_QTY NUMBER(15),
           WEEK21_QTY NUMBER(15),
           WEEK22_QTY NUMBER(15),
           WEEK23_QTY NUMBER(15),
           WEEK24_QTY NUMBER(15),
           WEEK25_QTY NUMBER(15),
           WEEK26_QTY NUMBER(15),
           WEEK27_QTY NUMBER(15),
           WEEK28_QTY NUMBER(15),
           WEEK29_QTY NUMBER(15),
           WEEK30_QTY NUMBER(15)
         )
        ORGANIZATION EXTERNAL
         (
        DEFAULT DIRECTORY ' ||inputdir ||
        ' ACCESS PARAMETERS
         (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY ","
        OPTIONALLY ENCLOSED BY ''"''
         )
        LOCATION(' || chr(39) || inputfile || chr(39) || ' ) )';
    EXECUTE IMMEDIATE v;
    END;



Regards,
Mona
Re: passing value in a pl/sql block [message #241914 is a reply to message #241912] Thu, 31 May 2007 00:55 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the error?

Regards
Michel
Previous Topic: procedure parameter
Next Topic: Program from V$Session not going away
Goto Forum:
  


Current Time: Sun Dec 11 08:02:01 CST 2016

Total time taken to generate the page: 0.10945 seconds