Home » SQL & PL/SQL » SQL & PL/SQL » Loading the .csv file into the database table
Loading the .csv file into the database table [message #222115] Thu, 01 March 2007 23:14 Go to next message
lokesh.sql
Messages: 9
Registered: November 2006
Location: Pune
Junior Member
Hi All,
I have written a procedure for loading the data from .csv file
into the database table,using the External Table concept, the problem is i am not able to create the external table dynamically,in the procedure. i have done the simple operation given below in the procedure.
1. Loading /Creating the Data into the External Table...
2. Loading the data into Main_Table .
3.Dropping the external table.

Please help me out.
Thanks in advance,
Lokesh



SQL> CREATE OR REPLACE PROCEDURE PROC_LOAD_DATA(P_FILENAME VARCHAR2)

2

3 IS

4

5

6

7 BEGIN

8

9

10

11 ----- Loading /Creating the Data into the External Table...

12

13

14

15 EXECUTE IMMEDIATE

16

17 'create table ext_table ('||

18

' empno Number(10),'||

19

' ename Varchar2(20),'||

20

21 ' job Varchar2(20))'||

22

23 'organization external ('||

24

25 ' type oracle_loader'||

26

27 ' default directory DBDIR'||

28

29 ' access parameters ('||

30

31 ' records delimited by newline'||

32

33 ' fields terminated by '','''||

34

35 ' missing field values are null)'||

36

37 ' location (''test_emp.csv'')'||

38

39 ' )'||

40

41 'reject limit unlimited';

42

43

44

45 ---- Loading the data into Main_Table ( loading )

46

48

49 INSERT INTO Main_Table

50

51 SELECT * FROM EXT_TABLE;

52

53

56

57 COMMIT;

58

59

60

61 ---- Dropping the External Table

62

63

64

65 EXECUTE IMMEDIATE 'DROP TABLE EXT_TABLE';

66

67

68

69 EXCEPTION

70

71 WHEN OTHERS THEN

72

73 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);

74

75 END;

76 /



Warning: Procedure created with compilation errors.



SQL> show errors;

Errors for PROCEDURE PROC_LOAD_DATA:



LINE/COL ERROR

-------- ---------------------------------------------------------

49/16 PL/SQL: SQL Statement ignored

51/32 PL/SQL: ORA-00942: table or view does not exist

Re: Loading the .csv file into the database table [message #222116 is a reply to message #222115] Thu, 01 March 2007 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via roles do not apply within PL/SQL procedures.
Re: Loading the .csv file into the database table [message #222274 is a reply to message #222115] Fri, 02 March 2007 10:57 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your external table does NOT exists during proc compile (it will be created during execution).

So you have use dynamic SQL for all references to that table:

EXECUTE IMMEDIATE 'INSERT INTO main_table SELECT * FROM ext_table';

IMHO you can CREATE external table OUTSIDE of your procedure as well.

HTH.
Previous Topic: Procedure to avoid duplicates
Next Topic: Create public Database link
Goto Forum:
  


Current Time: Sun Dec 04 10:15:10 CST 2016

Total time taken to generate the page: 0.09582 seconds