通过php调用存储过程时出错.文字与格式字符串不匹配 [英] Error while calling stored procedure through php. Literal does not match format string

问题描述

我正在将Oracle 10g Express Edition与PHP codeigniter一起使用.

I am using Oracle 10g Express Edition with PHP codeigniter.

我有一张桌子tbl_movie:-

I have a table tbl_movie:-

CREATE TABLE tbl_movie (movie_id NUMBER(11) PRIMARY KEY,
  movie_title VARCHAR2(255) NOT NULL,
  movie_image VARCHAR2(255) NOT NULL,
  language_id NUMBER(11) NOT NULL REFERENCES tbl_language(language_id) ON DELETE CASCADE,
  showtime_id NUMBER(11) NOT NULL REFERENCES tbl_showtime(showtime_id) ON DELETE CASCADE,
  movie_sdate DATE NOT NULL,
  movie_edate DATE NOT NULL,
  movie_add_date DATE NOT NULL
);

现在，我已经通过Oracle命令行运行了一个过程:-

Now I have created a procedure by running it through the Oracle Command Line:-

CREATE OR REPLACE PROCEDURE 
        insertMovie (id IN NUMBER, title IN VARCHAR2, image IN VARCHAR2, language IN NUMBER, showtime IN NUMBER, sdate IN DATE, edate IN DATE, adate IN DATE, message OUT NUMBER) 
    IS
        BEGIN
            INSERT INTO tbl_movie (movie_id, movie_title, movie_image, language_id, showtime_id, movie_sdate, movie_edate, movie_add_date) 
            VALUES ( id, title, image, language, showtime, TO_DATE(sdate, 'YYYY-MM-DD hh24:mi:ss'), TO_DATE(edate, 'YYYY-MM-DD hh24:mi:ss'), TO_DATE(adate, 'YYYY-MM-DD hh24:mi:ss'));
            message := 1;
        EXCEPTION
          WHEN OTHERS THEN
            message := 0;
        END;
/

这是我的Codeigniter控制器中的代码:-

This is the code from my codeigniter controller:-

public function testMovie()
{
    $insertData['movie_id'] = 1;
    $insertData['movie_title']= 'test';
    $insertData['movie_image']= 'rfgt';
    $insertData['language_id']= 1;
    $insertData['showtime_id']= 1;
    $insertData['movie_sdate']= '2017-02-24 00:00:00';
    $insertData['movie_edate']= '2017-02-27 00:00:00';
    $insertData['movie_add_date']= '2017-02-20 00:00:00';
    $this->load->model('oracle_model');
    $return = $this->oracle_model->add_movie('movie',$insertData,'');   
}

这是用于调用过程的模型函数:-

And this is the model function which is for calling the procedure:-

function add_movie($entity,$insertData,$time)
{
    print_r($insertData);
    $conn   = oci_connect("xxxxxx", "xxxxxx","xxxxxxxxx");
    if(!$conn)
    {
        echo "connect failed".oci_error();
    }   
    else
    {   
        echo "connect done at".date('d-m-y');

        // calling stored procedure insertMovie
        $insertMovieProcedureSQL = "BEGIN insertMovie(:id, :title, :image, :language, :showtime, :sdate, :edate, :adate, :message); END;";
        $stmt = oci_parse($conn, $insertMovieProcedureSQL);

        //  Bind the input parameter
        oci_bind_by_name($stmt,':id',$insertData['movie_id']);
        oci_bind_by_name($stmt,':title',$insertData['movie_title']);
        oci_bind_by_name($stmt,':image',$insertData['movie_image']);
        oci_bind_by_name($stmt,':language',$insertData['language_id']);
        oci_bind_by_name($stmt,':showtime',$insertData['showtime_id']);
        oci_bind_by_name($stmt,':sdate',$insertData['movie_sdate']);
        oci_bind_by_name($stmt,':edate',$insertData['movie_edate']);
        oci_bind_by_name($stmt,':adate',$insertData['movie_add_date']);

        // Bind the output parameter
        oci_bind_by_name($stmt,':message',$message);

        oci_execute($stmt);
        // $message is now populated with the output value
        echo "$message\n";
    }   
}

运行该函数时，出现以下错误:-

When I am running the function, I am getting the following error:-

oci_execute(): ORA-01861: literal does not match format string ORA-06512: at line 1

当我尝试通过消除所有date数据类型列来运行该过程时，代码运行良好.我认为主要的问题是与日期"列有关.

When I tried to run the procedure by eliminating all the date datatype columns, the code is running fine. I think the main problem is with the Date columns.

但是当我从命令行运行此查询时，数据被成功插入:-

But when I run this query from the command line, data gets successfully inserted:-

INSERT INTO tbl_movie (movie_id, movie_title, movie_image, language_id, showtime_id, movie_sdate, movie_edate, movie_add_date) 
            VALUES ( 2, 'title', 'image', 2, 3, TO_DATE('2017-02-24 00:00:00', 'YYYY-MM-DD hh24:mi:ss'), TO_DATE('2017-02-27 00:00:00', 'YYYY-MM-DD hh24:mi:ss'), TO_DATE('2017-02-20 00:00:00', 'YYYY-MM-DD hh24:mi:ss'));

我做错了什么?我被困了将近4个小时.

What am I doing wrong? I am stuck for nearly 4 hours.

推荐答案

过程参数sdate等具有DATE数据类型.在您的过程中，将TO_DATE( ...., 'yyyy-mm-dd hh24:mi:ss')应用于它们.

The procedure parameters sdate etc. are of DATE data type. Within your procedure, you apply TO_DATE( ...., 'yyyy-mm-dd hh24:mi:ss') to them.

TO_DATE用于将字符串转换为日期，而不是将日期转换为日期.如果将TO_DATE应用于日期，则Oracle首先将日期转换为字符串，以便可以将其传递给TO_DATE.不幸的是，当Oracle进行隐式转换时，它没有使用与TO_DATE中相同的格式模型.而是使用调用该过程的会话的nls_date_format.这与TO_DATE中的模型不同-会导致您看到错误.

TO_DATE is used to convert strings to dates, not dates to dates. If you apply TO_DATE to a date, Oracle first converts the date to a string, so that it can be passed to TO_DATE. Unfortunately, when Oracle does that implicit conversion, it doesn't use the same format model you have in TO_DATE; rather, it uses the nls_date_format of the session that invoked the procedure. Which is not the same as the model in TO_DATE - which causes the error you saw.

有两种方法可以解决此问题.一种是不使用TO_DATE(sdate, ...)，而仅在INSERT语句中使用sdate. sdate和其他日期已经是日期(如果它们首先正确地作为日期传递给了该过程，则为日期).

There are two ways to solve this. One is to not use TO_DATE(sdate, ...) but use just sdate in the INSERT statement. sdate and the others are dates already (if they were passed correctly as dates to the procedure in the first place).

第二个是，如果实际上您是想将参数作为字符串传递的，则不要在过程声明中将它们声明为DATE；将它们声明为VARCHAR2. (并确保这些字符串完全使用正确的格式-您应该能够从前端进行控制.)

The second is, if in fact you meant that the arguments are passed in as strings, don't declare them as DATE in the procedure declaration; declare them VARCHAR2. (And make double-sure those strings are exactly in the correct format - you should be able to control that from the front-end.)

每当看到TO_DATE(....)应用于DATE输入时，都将出现与您看到的错误类似的错误.相反，每当您看到这样的错误时，就会将诸如TO_DATE(....)之类的可疑代码应用于日期.

Whenever you see TO_DATE(....) applied to a DATE input, expect an error like the one you saw. Conversely, whenever you see an error like that, suspect code like TO_DATE(....) applied to a date.

已添加:还有另一种可能性.如果实际上您是在将字符串传递给过程，则Oracle必须将它们转换为日期(因为它们被声明为日期).如果字符串不完全符合调用该过程的会话的nls_date_format格式，则参数从字符串到日期的初始转换将失败，并且甚至不会达到INSERT语句.

Added: There is also another possibility. If in fact you are passing strings to the procedure, Oracle must convert them to dates (because they are declared as such). If the strings aren't exactly in the format of the nls_date_format of the session invoking the procedure, this initial conversion of the parameters from strings to dates will fail and the INSERT statement won't even be reached.

这篇关于通过php调用存储过程时出错.文字与格式字符串不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！