duanmengsuo9302
2018-07-12 11:19
浏览 96

使用Oracle和PHP:在SQL Developer中工作但PHP文件结果ORA-00900:无效语句

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?

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";

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?

Thanks for any help!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongtan1009 2018-07-13 06:02
    已采纳

    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;
    /
    

    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;";
    

    And the function:

    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;
        }
    }
    
    点赞 打赏 评论

相关推荐 更多相似问题