create view php / oracle

Question

create view php / oracle

Navigation

#1 by (0 votes)

0

I want to generate a view through php for an oracle database where the select is done through the result of another query.

This query mounts the select to the view:

$sql1 = "SELECT TESTE FROM VIEW_TESTE";
$result1 = odbc_exec($conOraSql , $sql1);
while ($row1 = odbc_fetch_array($result1)) {
    $arrData[] = $row1["TESTE"]."<br />";   
}
$select = implode('',$arrData);

The result of this query:

SELECT 'VAL1' COL1, 'VAL2' COL2, 'VAL3' COL3, 'VAL4' COL4, 'VAL5' COL5, 'VAL6' COL6, 'VAL7' COL7, 'VAL8' COL8 FROM VIEW TABELA1 
UNION ALL 
SELECT 'VAL1' COL1, 'VAL2' COL2, 'VAL3' COL3, 'VAL4' COL4, 'VAL5' COL5, 'VAL6' COL6, 'VAL7' COL7, 'VAL8' COL8 FROM VIEW TABELA2 
UNION ALL
SELECT 'VAL1' COL1, 'VAL2' COL2, 'VAL3' COL3, 'VAL4' COL4, 'VAL5' COL5, 'VAL6' COL6, 'VAL7' COL7, 'VAL8' COL8 FROM VIEW TABELA3

And with this result, I wanted to mount a view this way, but without success, any tips?

$sql2 = "CREATE OR REPLACE FORCE VIEW \"USUARIO\".\"VIEW_TESTE2\" (\"COL1\", \"COL2\", \"COL3\", \"COL4\", \"COL5\", \"COL6\", \"COL7\", \"COL8\") AS $select";
$result2 = odbc_exec($conOraSql , $sql2);

php oracle select sql-view

asked by anonymous 25.11.2015 / 18:06

1 answer

Do date conversion C # [duplicate] how to remove all the data remnants in the google search cache?

score 0 · Accepted Answer

For the above question, here is the solution I found:

<?php
    #--> conexao oracle
    require_once("Connections/Conn_Oracle.php");
    #--> monta a consulta
    $sql = "CREATE OR REPLACE FORCE VIEW \"USUARIO\".\"VIEW1\" (\"TESTE\") AS 
    SELECT 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8 FROM ' || a.OWNER || ' WHERE COL1 = 'ALGO' AND COL5 IN (''ALGO2'', ''ALGO3'', ''ALGO4'') UNION ALL '  TESTE FROM ALL_TABLES a ORDER BY a.OWNER";
    $result = odbc_exec($conOraSql , $sql);
    #--> verifica o resultado
    if(!$result) { echo odbc_error()."<br />"; } else { echo 'VIEW1 CRIADA'."<br />"; }
    #-->
    #--> consulta a view 1
    $sql1 = "SELECT TESTE FROM VIEW1";
    $result1 = odbc_exec($conOraSql , $sql1);
    while ($row1 = odbc_fetch_array($result1)) {
        $arrData[] = $row1["TESTE"];    
    }
    $select = implode('',$arrData);
    #-->
    #--> monta a view2
    $sql2 = ""CREATE OR REPLACE FORCE VIEW \"USUARIO\".\"VIEW2\" (\"COL1\", \"COL2\", \"COL3\", \"COL4\", \"COL5\", \"COL6\", \"COL7\", \"COL8\") AS $select";
    $result2 = odbc_exec($conOraSql , $sql2);;
    #--> verifica o resultado
    if(!$result2) { echo odbc_error()."<br />"; } else { echo 'VIEW2 CRIADA'."<br />"; }
?>