Php and oracle OCI query

Question

I have a prolem with this code

$stmt = oci_parse($db, $sql);
$isQueryOk = oci_execute($stmt);
if ($isQueryOk) {
    while (($row = oci_fetch_assoc($stmt)) != false) {
        array_push($results, $row);
    }
    echo json_encode($results);
} else {
    $msg = "Error FETCHING ALL [$sql] on " . mb_strtoupper($dbTable) . "!";
}

The problem is that if oci_fetch_assoc($stmt) return 20000 rows, the while (($row = oci_fetch_assoc($stmt)) != false) { array_push($results, $row); } takes to much time. Is there a way that i can return echo json_encode($results); without the WHILE cycle.

Thanks in advance.

Use oci_fetch_all: php.net/manual/en/function.oci-fetch-all.php — Marcos Alexandre Sedrez
– Marcos Alexandre Sedrez, Commented Oct 18, 2016 at 12:00
i've tried but cannot format as key=>value pair. Returns ex: {EMPRESA: ["CLCA", "CMIP", "CPP"], VAL: ["CLCA", "CMIP", "CPP"]} and i need [{EMPRESA: "CLCA", VAL: "CLCA"}, {EMPRESA: "CMIP", VAL: "CMIP"}, {EMPRESA: "CPP", VAL: "CPP"}] — Leonel Matias Domingos
– Leonel Matias Domingos, Commented Oct 18, 2016 at 12:16

Marcos Alexandre Sedrez · Accepted Answer · 2016-10-18 12:42:19Z

2

OR try to use another way to push your array. "Note: If you use array_push() to add one element to the array it's better to use $array[] = because in that way there is no overhead of calling a function." http://php.net/manual/en/function.array-push.php

$results[] = $row;

answered Oct 18, 2016 at 12:42

Marcos Alexandre Sedrez

5373 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:30:46Z

1

I'm not sure it'll be significantly faster, but as Marcos Sedrez wrote you can try using oci_fetch_all. You'll need to pass it a flag to return by row (instead of by column, the default) to match your current output format:

oci_fetch_all($stmt, $output, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
json_encode($output);

See the documentation for further information.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Oct 18, 2016 at 12:29

timclutton

13.1k3 gold badges37 silver badges49 bronze badges

Comments

dasun · Accepted Answer · 2023-05-03 10:17:27Z

-1

The error message is not output anywhere, so if there was an error it won't be visible

try this one

$stmt = oci_parse($db, $sql);
if ($stmt === false) {
    $error = oci_error($db);
    $msg = "Error PARSING [$sql] on {$dbTable}: {$error['message']}";
} else {
    $isQueryOk = oci_execute($stmt);
    if ($isQueryOk) {
        $results = array();
        while (($row = oci_fetch_assoc($stmt)) !== false) {
            array_push($results, $row);
        }
        echo json_encode($results);
    } else {
        $error = oci_error($stmt);
        $msg = "Error EXECUTING [$sql] on {$dbTable}: {$error['message']}";
    }
}

if (isset($msg)) {
    error_log($msg);
    http_response_code(500);
    echo 'Internal Server Error';
}

edited May 3, 2023 at 10:17

answered Apr 11, 2023 at 10:23

dasun

413 bronze badges

Collectives™ on Stack Overflow

Php and oracle OCI query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related