Using output value of column from function for another column in SQL*Loader

functionsoracle-11gparametersql-loader

I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please let me know, if this is inappropriate to do so.

==============================

I am trying to define a SQL*Loader control file where col1 is determined by using a user function which takes a couple of parameters with values from the input file. This part is working fine.

I want to use the value returned by above function as a parameter for another user defined function and that does not work. The value sent to the second function is not the calculated value of the first column.

Is this possble? Can someone give me a sample?

Here is my control file

( col1  BOUNDFILLER         POSITION(1:8),
  col2  INTEGER EXTERNAL    "schema_own.get_col2_val(:col1, :col6)",
  col3              POSITION(29:33) CHAR,
  col4              "sysdate",
  col5              constant " ",
  col6              POSITION(9:11)  CHAR,
  col7              "sysdate",
  col8              POSITION(12:18) CHAR,
  col9              POSITION(19:28) CHAR,
  col10             POSITION(29:33) CHAR,
  col11             POSITION(52:63) CHAR,
  col12             "(col2)", --constant    "TEST",
  col13             "schema_own.get_col13_value(:col1, :col2)",
  LAST_UPDATE_TS            "sysdate",
  LAST_UPDATE_USER_I        constant "SQLLDR"
)

What seems to be happening is that the the second parameter in get_col13_value seems to be containing the FIRST character of the col6 instead of the number that col2 should have from the call to get_col2_val.

I tried col12 with both (col2) and (:col2) – same outcome.

Am I missing something?

Can someone also help me understand when to use col2 vs. :col2 in sql loader?

Best Answer

In don't think you can use the resullt of a function. The documentation for the loader can be found in the Utilities Manual. Applying SQL Operators to Fields says

The execution of SQL strings is not considered to be part of field setting. Rather, when the SQL string is executed it uses the result of any field setting (...)

Field setting means that the field names are assigned to the substrings of the current data of the record of the file that is loaded. How this works is described in Specifying the Position of a Data Field: If you have a POSITION(x:y) specification for a column named colthen the substring from position x to position y is the field value of col and it can be references as :col in an sql-string. If there is no POSITION specifiaction found as in the specification of your fields col2, col4, col5, col7, col12, col13, LAST_UPDATE_T and LAST_UPDATE_USER_I, then POSITION is defined some way depending on the previous field and the data type of the column. So because the col2-Field (that is referenced by :col2 in an sql-string) has no explicit field specification it is the field of INTEGER EXTERNAL type (of default lenght 1) following col1. So it actually is POSITION(9:9) and therefore the first character of:col6

If you want to set the column values of a table depending on other column values you can use a trigger on the table.

Related Solutions

MySQL – Create a function from another function

You could play some games with mysql.proc, the physical home of all Stored Procedures and Stored Functions.

First, here are the databases on my PC

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

Let's create a Stored Function in the database rolando called GetUnixTimeStamp:

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Let's try to sculpt the CREATE FUNCTION SQL Statement based on the function itself

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

What does this generate ?

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK so far, so good. Let's change the SQL to place this function in the database david

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK Great. We can make the SQL. Can I execute the creation of the new function ???

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Does the function exist ???

mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Biggest questiion of all : Does the newly copied function run ???

mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

HOORAY !!! This is how you can copy a function from one database to another.

FINAL QUESTION : Is this all worth it ???

SUMMARY

Here is the original SQL to generate function creation

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `david`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

This will create Stored Procedures as well

Here is the copy operation, which must take place from the OS level calling the mysql client

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Amazon-rds – Example of loading into RDS from S3 using SQL*Loader

I thought of two ideas how you could (at least theoretically) access the data if you are on Linux.

mount S3 bucket using some kind of S3 FUSE. E.g. first hit on Google https://code.google.com/p/s3fs/wiki/FuseOverAmazon
create named pipe: mkfifo oraclepipe then configure SQL*Loader to read from the pipe probably using conventional path. And feed S3 file to the pipe: wget -O oraclepipe http://s3.com/hash.

We were using the second method on 9i to compress export online. And I do not recall problems with this approach. We ran two sessions from the shell script:

    gzip < oraclepipe > database.exp.gz &
    exp "username/passwd" FULL=y BUFFER=2048000 FILE=oraclepipe

Best Answer

Related Solutions

MySQL – Create a function from another function

SUMMARY

Amazon-rds – Example of loading into RDS from S3 using SQL*Loader

Related Question