1

i'm gonna to explain you my problem. I have to convert a t-sql script into a pl/sql script. There is my code in t-sql : 

CREATE TABLE #temp_tb ( temp_row nvarchar(max)) 
if @@error <> 0 goto lbl_end

DECLARE @bulk_cmd varchar(1000)
set @bulk_cmd = 'BULK INSERT #temp_tb FROM ''c:\Communication\Test\MSG_IN\'+'$(nomfic)'+''' WITH 
( 
CODEPAGE = ''RAW'',
ROWTERMINATOR = '''+CHAR(10)+''',
DATAFILETYPE = ''WIDECHAR'' 
)'

exec (@bulk_cmd)
if @@error <> 0 goto lbl_end

And after a lot of oracle doc and forum, i write this :

CREATE GLOBAL TEMPORARY TABLE temp_tb ( temp_row NCHAR(MAX)) ON COMMIT DELETE ROWS;
BEGIN
EXCEPTION
    WHEN OTHERS THEN GOTO lbl_end;
END;                               

DECLARE bulk_cmd varchar2(1000);

bulk_cmd := 'BULK INSERT temp_tb FROM ''c:\Communication\Test\MSG_IN\'+'$(nomfic)'+''' WITH 
( 
CODEPAGE = ''RAW'',
ROWTERMINATOR = '''+CHAR(10)+''',
DATAFILETYPE = ''WIDECHAR'' 
)';

BEGIN
  exec (bulk_cmd);
EXCEPTION
    WHEN OTHERS THEN GOTO lbl_end;
END;

But, i've an error here NCHAR(MAX). I've to put a large value like 100000 ? If i put this, he retunrs me an error after the EXECEPTION Syntax error...

In other hand, if someone can say me how to adapt me bulk collect... I don't find any doc on bulk collect to integrated file like me...

I hope i'm clear, because it's really difficult for me to explain... I'm beginner in Oracle(PL/SQL)...

Thank's for help guys, again

EDIT : I launch this script by a .bat. I took parameters, that's filled '$(nomfic)' with the values in function of the parameters.

S. Guillaume
  • 67
  • 1
  • 1
  • 12
  • check this link out http://weblogs.sqlteam.com/jamesw/archive/2010/07/28/datatypes-translation-between-oracle-and-sql-server-part-1.aspx – SubqueryCrunch Sep 23 '15 at 09:18
  • Ok so i know the 'max value' is 2000. Thank's, any idea for the bulk collect in my case ? – S. Guillaume Sep 23 '15 at 09:20
  • check out the last answer here http://stackoverflow.com/questions/7749947/insert-a-text-file-into-oracle-with-bulk-insert – SubqueryCrunch Sep 23 '15 at 09:25
  • Thank's ! But my file is an .txt it's adaptable ? Because my bulk_cmd have to stay like this in a variable. So i can't adapt the bulk insert (T-SQL) into a bulk collect (PL/SQL)? – S. Guillaume Sep 23 '15 at 09:33
  • 3
    Lots of things wrong here. Temp tables in Oracle work differently to SQL Server. Your script shouldn't create a global temporary table - you create it once and then re-use it whenever required. Also, your first anonymous block doesn't do anything. Also, `exec` is not a valid PL/SQL command - to run dynamic SQL you would use `EXECUTE IMMEDIATE` but that's not required here. Your "BULK INSERT" statement won't work either. – Jeffrey Kemp Sep 23 '15 at 11:14
  • @JeffreyKemp Thank's to you ! So how can a create a table temporary ? Local temporary table ? Thanks for the Exec... i miss it... My file to integrated can have a 10 000-100 000 lines, so how can i manage this by the bulk collect ? I'm lost... Thank's for your reply – S. Guillaume Sep 23 '15 at 12:42
  • 2
    You have the correct command for creating a temp table in Oracle. "Temporary tables in Oracle are [global] **permanent objects** that hold **temporary data** that is session local." [Emphasis added] http://stackoverflow.com/questions/8912871/global-temp-tables-sql-server-vs-oracle/8914523#8914523 – Shannon Severance Sep 24 '15 at 00:05
  • 2
    To load a file from the file system you would use something external tables (recommended) or SQL Loader. BULK COLLECT is for loading data to/from PL/SQL arrays and has nothing to do with files. – Jeffrey Kemp Sep 24 '15 at 00:13
  • @ShannonSeverance @JeffreyKemp Thank's to you guys. I follow you advices and i adapt my code like this : `CREATE OR REPLACE DIRECTORY ext_tab_data AS 'c:\Hardis\reflex\Communication\HM\MSG_IN\'; CREATE TABLE tem_tb( temp_row NCHAR(2000) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE ( temp_row VARCHAR2(1000) ) ) LOCATION ('$(nomfic)'+'.txt') );` But SQL developer return the ORA-00910 error code, but i respect the length of the NCHAR data types... i don't understand where is my mistake.. – S. Guillaume Sep 24 '15 at 07:50
  • I'm not sure what `LOCATION ('$(nomfic)'+'.txt'` means but it doesn't look right. Does that resolve to a literal within your script or are you expecting SQL Developer to handle it? Also external tables read files off the database server - is 'c:\Hardis\reflex\Communication\HM\MSG_IN\' on the server and accessible to the Oracle process? If so and your only problem is a "value too large" error, it seems that's in a part of your code we can't see. – William Robertson Oct 20 '15 at 16:46

0 Answers0