使用Oracle和PHP:在SQL Developer中有效，但是PHP文件导致ORA-00900:无效的语句 [英] Using Oracle and PHP: Works in SQL Developer but PHP file results ORA-00900: Invalid Statement

问题描述

当我从先前的帖子"使用Oracle用PIVOT将三个表合并到一个表中"，然后在SQL Developer中单击运行脚本"，一切正常，但是当我尝试从PHP文件执行相同的脚本时，出现"ORA-00900 Invalid SQL Statement"-错误. SQL开发人员的运行语句"也无法执行代码.看来我的代码不是"SQL语句"语法?

When I take the code from my earlier post "Using Oracle combine three tables to one with PIVOT" and hit "Run Script" in SQL Developer everything works just perfectly but when I try to execute same script from PHP file I get "ORA-00900 Invalid SQL Statement" -error. SQL Developers "Run Statement" fails as well to execute the code. It seems my code isn't in the "SQL Statement" syntax?

我的用于将sql脚本放入变量的PHP代码:

My PHP code for putting sql script in the variable:

$sql = "variable x REFCURSOR
DECLARE
    exam_ids   VARCHAR2(255);
BEGIN
    SELECT
        LISTAGG(''''
                  || exam_id
                  || ''' AS \"'
                  || exam_name
                  || '\"',',') WITHIN GROUP(
            ORDER BY
                exam_id ASC
        )
    INTO exam_ids
    FROM
        exam;

    OPEN :x FOR 'SELECT
        *
               FROM
        (
            SELECT
                u.user_id,
                u.user_name,
                e.exam_id,
                eu.exam_date
            FROM
                users u
                LEFT JOIN exam_user eu ON u.user_id = eu.user_id
                LEFT JOIN exam e ON e.exam_id = eu.exam_id
            ORDER BY
                u.user_id
        )
            PIVOT ( MAX ( exam_date )
                FOR exam_id
                IN ( ' || EXAM_IDS || ' )
            )
    ORDER BY
        1';
END;
/

print x";

然后我将$ sql变量传递给函数以得到结果:

Then I pass the $sql variable to function for the results:

function getSQLResult($sql, $conn) {
    $stmt = OCIParse($conn, $sql);

    if( $stmt === false ) {
        errorShutdown(__('...'), __('...'));
        die();
    } else {
        //Executes a statement
        if (OCIExecute($stmt)) {
            return $stmt;
        }
        else {
          $err = oci_error($stmt);
          echo '<pre>';
          print_r($err);
          echo '</pre>';
          return false;
        }
    }
}

那么谁能告诉我如何重构代码?

So can anyone show me how to refactor the code?

感谢您的帮助！

推荐答案

我想我明白了.似乎需要从该PL/SQL脚本中创建一个过程:

I think I figured that out. It seems that needed to make from that PL/SQL script a procedure:

CREATE OR REPLACE PROCEDURE getExamStatus(RC OUT SYS_REFCURSOR) AS
    exam_ids   VARCHAR2(255);
BEGIN
    SELECT
        LISTAGG(''''
                  || exam_id
                  || ''' AS \"'
                  || exam_name
                  || '\"',',') WITHIN GROUP(
            ORDER BY
                exam_id ASC
        )
    INTO exam_ids
    FROM
        exam;

    OPEN rc FOR 'SELECT
        *
               FROM
        (
            SELECT
                u.user_id,
                u.user_name,
                e.exam_id,
                eu.exam_date
            FROM
                users u
                LEFT JOIN exam_user eu ON u.user_id = eu.user_id
                LEFT JOIN exam e ON e.exam_id = eu.exam_id
            ORDER BY
                u.user_id
        )
            PIVOT ( MAX ( exam_date )
                FOR exam_id
                IN ( ' || EXAM_IDS || ' )
            )
    ORDER BY
        1';
END;
/

然后在数据库中运行该过程.之后，在PHP文件中，我不得不重构sql语句:

Then run that procedure in the database. After that in the PHP file I had to refactor the sql statement:

$sql = "BEGIN getExamStatus(:rc); END;";

函数:

function getSQLResult($sql, $conn) {
    $stmt = oci_parse($conn, $sql);

    if( $stmt === false ) {
        errorShutdown(__('...'), __('...'));
        die();
    } else {
        $rc = oci_new_cursor($conn);
        oci_bind_by_name($stmt, ':rc', $rc, -1, OCI_B_CURSOR);
        if(!oci_execute($stmt)) {
            return false;
            //return oci_error($stmt);
        }
        if(!oci_execute($rc)) {
            return false;
            //return oci_error($stmt);
        }
        $results = array();
        while (($row = oci_fetch_array($rc, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
            $results[] = $row;
        }
        oci_free_statement($stmt);
        oci_free_statement($rc);
        return $results;
    }
}

这篇关于使用Oracle和PHP:在SQL Developer中有效，但是PHP文件导致ORA-00900:无效的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！