Error inserting BLOB type into Oracle Bank

Question

I’m having a problem inserting a javascript signature into the database. You’re making me wrong again:

oci_execute(): ORA-01704: too long string literal

I don’t know what it might be, I’ve been reading that the varchar the limit is 4000bytes, but the blog is much more and I still can not insert the signature, I will leave the excerpt PHP here with the insert.

$cracha = $_POST['cracha'];
$nomecompleto = $_POST['nomecompleto'];
$setor = $_POST['setor'];
$cargo = $_POST['cargo'];
$assinatura = $_POST['signature_responsavel'];
$data1 = date('Y-m-d');
$emprestimo = $_POST['emprestimo'];
$estabelecimento = $_POST['estabelecimento'];

//aqui tento transformar para base64 mas não adianta

$data2 = "$assinatura";

list($type, $data2) = explode(';', $data2);
list(, $data2)      = explode(',', $data2);
$data2 = base64_decode($data2);

$assinatura1 = $assinatura;


$sql = " INSERT INTO web.sistemarh_transf (cracha,descricao,nomecompleto,loja,cargo,data_entrada,setor,tamanho,quantidade,status,assinatura) 
            VALUES ('$cracha','$descricao','$nomecompleto','$estabelecimento','$cargo',TO_DATE('$data1', 'yyyy/mm/dd'),'$setor','$tamanho',$quantidade,'$emprestimo','$assinatura1')";

$inserir = oci_parse($conexao,$sql);

//aqui sigo a dica de um forum, mas não adianta, o que pode estar errado? Pois em outras tabelas tem inserções de 17,18k de caracteres, nesta está dando problema
$blob = oci_new_descriptor($conexao, OCI_D_LOB);

oci_execute($inserir, OCI_DEFAULT) or die ("Unable to execute query");
oci_commit($conexao);

signature code on the previous form:

<div class="form-group">
    <label for="email"> Assinatura</label>
    <br>

    <div id="content">
        <div id="signatureparent">
            <div id="signature"></div>
            </div>
            <div id="tools"></div>
        </div>
        <div id="scrollgrabber"></div>
        <script src="./libs/jquery.js"></script>
    </div>
    <br>   
    <script src="./libs/jSignature.min.noconflict.js"></script>
    <textarea id="imgData" name="signature_responsavel" style="width:960px; display:none;"></textarea>
</div>

Answer 1

You will need to change your logic in order to pass the long text to a variable and insert the variable.

Behold:

    CREATE TABLE TESTE_LONG (
     nr_linh NUMBER(5,0) NOT NULL,
     ds_linh LONG NULL
     );

    Table created.

    Insert into TESTE_LONG values ( 1,2 '000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000');

    ERROR at line 2:
    ORA-01704: string literal too long

    SELECT COUNT(*) FROM TESTE_LONG;

    COUNT(*)
    ----------
    0




     declare  ww_TEXTO LONG;
     BEGIN
       ww_texto :='000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000
0000'

     Insert into TESTE_LONG values ( 1 2, Ww_texto );
 end;
 /

    PL/SQL procedure successfully completed.

    SQL> SELECT COUNT(*) FROM TESTE_LONG;

    COUNT(*)
    ----------
    1