how do we call a c function from an sql script

Question

Hi,

how do we call a C function from an SQL script?

int get_next_fbill_b2kId_seq_num(b2kIdType seq_val,bankIdPtrType bank_id)
{
    validate_dc_alias(dcAlias);
    tbaDateType sysDate;
    tbaGetSystemDateTime(sysDate,NULL,NULL);  /* returns in TBA date format */
    sysDate[10] = EOS;
    get_seq_value(next_num_char, 0, FBILL_B2KID_SRL_NUM,bank_id,TBAFATAL);
    m_sprintf (seq_val, "%s%s%s", dcAlias, sysDate+8,next_num_char);

    return(SUCCESS);
}

This is my function defined in a cxx file. I want to call this in an SQL script. How can I do this?

Answer 1

You could create and add an extended stored procedure but this feature is deprecated and may be removed in future versions.

The current prescribed way to do this is via CLR integration but you would have to use a .NET managed language such as C#.

Answer 2

If your function is plain C, you need to create an executable and invoke it via ! or HOST.

If you are in a .Net environment, you can also create a .Net assembly containing your code, and call your procedure as if it was a PL/SQL procedure.

Answer 3

I assume the OP uses Oracle because he/she writes about PL/SQL.

It is possible to call an external c procedure. http://www.shutdownabort.com/quickguides/c_extproc.php