ORA-06576: not a valid function or procedure name in Qt c-CodePudding

im developing in Qt by c

i have connected ORACLE to Qt Creator.

but when i call PROCEDURE in Qt, i got this error "ORA-06576: not a valid function or procedure name "

i wrote code like this follw

PROCEDURE***

CREATE OR REPLACE PROCEDURE INPUT_CUSTOMER 
(
    ck IN NUMBER ,
    clinic IN varchar2,
    dentist IN varchar2,
    license IN varchar2,
    number_c IN varchar2,
    amount IN NUMBER 
)
IS
BEGIN
    INSERT INTO CUSTOMER c(c.CUSTOMER_KEY, c.CLINIC_NAME, c.LICENSE_NUMBER, c.DENTIST_NAME, c.PHONE_NUMBER, c.ORDER_AMOUNT)
    VALUES  (ck, clinic, license, dentist, number_c, amount);
    COMMIT;
END INPUT_CUSTOMER ;

CODE in Qt***

    QSqlQueryModel *inputQuery = new QSqlQueryModel;
    inputQuery->setQuery
            (QString("CALL INPUT_CUSTOMER (%1, %2, %3, %4, %5, %6)")
             .arg(ck).arg(clinic).arg(license).arg(dentist).arg(number).arg(0));
    if (inputQuery->lastError().isValid())
        qDebug() << inputQuery->lastError();

plz check my code

CodePudding user response：

Your procedure is fine.

Your QT code is building the query using a template string and string concatenation and this is the cause of the error (and is bad practice as it leads to SQL injection vulnerabilities).

Instead, you should be building the query using bind variables:

QSqlQuery query(db);
query.prepare("CALL INPUT_CUSTOMER (:ck, :clinic, :license, :dentist, :number, :amount)");
query.setForwardOnly(true);
query.bindValue(":ck", ck);
query.bindValue(":clinic", clinic);
query.bindValue(":license", license);
query.bindValue(":dentist", dentist);
query.bindValue(":number", number);
query.bindValue(":amount", 0);
bool query_executed_ok = query.exec();

(I am not a QT developer, this was taken from the documentation and answers to other similar questions so there may be syntax errors but it is designed to give you a guide to the correct process and allow you to fix the QT issues.)

Also note:

If you have three PROCEDURE and each one contains a COMMIT statement then you cannot run all three and then, if an exception occurs in a latter one, ROLLBACK them all as the changes from the first two will already be COMMITted.

As a general rule, you should not use COMMIT in a PROCEDURE or FUNCTION but should leave it up to the caller to COMMIT the transaction so they can bundle multiple actions together.

Instead you should COMMIT in QT when the transaction has been completed.

You can also use the column data types in the procedure:

CREATE OR REPLACE PROCEDURE INPUT_CUSTOMER 
(
    ck       IN CUSTOMER.CUSTOMER_KEY%TYPE,
    clinic   IN CUSTOMER.CLINIC_NAME%TYPE,
    dentist  IN CUSTOMER.LICENSE_NUMBER%TYPE,
    license  IN CUSTOMER.DENTIST_NAME%TYPE,
    number_c IN CUSTOMER.PHONE_NUMBER%TYPE,
    amount   IN CUSTOMER.ORDER_AMOUNT%TYPE 
)
IS
BEGIN
    INSERT INTO CUSTOMER(CUSTOMER_KEY, CLINIC_NAME, LICENSE_NUMBER, DENTIST_NAME, PHONE_NUMBER, ORDER_AMOUNT)
    VALUES  (ck, clinic, license, dentist, number_c, amount);
END INPUT_CUSTOMER;
/

fiddle