Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using Cursor and FOR LOOP to INSERT the data into table

Angelina84Dec 23 2013 — edited Jan 6 2014

Hi all,

I have SELECT statement that returns 3 rows:

PROCESSNAMEPROTDATE
IMM2013-12-18
Metrology2013-11-18
CT2013-12-04

SELECT  processName, MAX(NVL(protStartDate, protCreateDate)) AS protDate

    FROM TABLE(SEM_MATCH("{

            ?ipc rdf:type s:Protocol .

            ?ipc s:protocolNumber ?protNum .

            ?ipc s:protocolCreateDate ?protCreateDate .

            OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

            ?ipra rdf:type s:ProcessAggregate .

            ?ipra s:hasProtocol ?iprot .

            ?iprot s:protocolNumber ?protNum .

            ?ipra s:processAggregateProcess ?processName.

    }",sem_models("PROTS", "LINEARS"),NULL, SEM_ALIASES(SEM_ALIAS("","http://VISION/Data/SEMANTIC#"),SEM_ALIAS("s","http://VISION/DataSource/SEMANTIC#")),NULL))

        Group by processName

Now I need to INSERT these values into the table along with the other values.

these other values come from different table.


       INSERT INTO MODEL_CLASS_COUNTS (MODEL_NAME, CLASS_NAME, INS_COUNT, COUNT_DATETIME, PROCESS_NAME, PROT_DATE)

       VALUES

       ("$MODEL",     

            "${i}",

        (SELECT COUNT (DISTINCT S)  FROM TABLE(SEM_MATCH(

                        "{?s rdf:type :${i} . }",SEM_Models("$MODEL"),NULL, SEM_ALIASES(SEM_ALIAS("","http://VISION/DataSource/SEMANTIC#")),NULL))),

         SYSTIMESTAMP, %%here need to insert PROCESSNAME, PROTDATE%%

            );


t was giving me error:


PL/SQL: ORA-22905: cannot access rows from a non-nested table item

so i enclosed sparql query into single quotes.


The code is as follows:

declare

type c_type is REF CURSOR;

cur c_type;

v_process varchar2(200);

v_pdate varchar2(200);

begin

open cur for

       ' SELECT processName,  MAX(NVL(protStartDate, protCreateDate)) AS protDate   <-- it's complaining about this being too long identifier, i think...

        FROM TABLE

          (SEM_MATCH (

                    "{

                        ?ipc rdf:type s:Protocol .

                        ?ipc s:protocolNumber ?protNum .

                        ?ipc s:protocolCreateDate ?protCreateDate .

                        OPTIONAL {?ipc s:protocolSchedStartDate ?protStartDate }

                        ?ipra rdf:type s:ProcessAggregate .

                        ?ipra s:hasProtocol ?iprot .

                        ?iprot s:protocolNumber ?protNum .

                        ?ipra s:processAggregateProcess ?processName.

                    }",SEM_Models("XCOMPASS", "XPROCESS"),NULL,    

          SEM_ALIASES(SEM_ALIAS("","http://VISION/Data/SEMANTIC#"),

          SEM_ALIAS("s", "http://VISION/DataSource/SEMANTIC#")),NULL))

           Group by processName';  

loop

fetch cur into v_process, v_pdate;

exit when cur%NOTFOUND;

--here I need to insert v_process , v_pdate into my table along with other values...

dbms_output.put_line('values for process and prod_date are: ' || v_process || v_pdate );

end loop;

          

close cur;

end;

/

exit;

Now, I get an error:

ORA-00972: identifier is too long

Does anyone know way around this?

Comments

Christopher Jones-Oracle

I haven't seen it.  Do you have any trace files in some oradiag_xxxx directory?  I generally set the log directory by adding something like this to my sqlnet.ora file:

     adr_base=/tmp/mylogs

and then run 'mkdir /tmp/mylogs' before running my application

Does it happen on multiple machines?

Do you have a complete, runnable test case that you can share?

Stanislav Studený

Hi.

Sure, I'll provide all detailed traces from instantclient's SIGSEGV handler.

I'm working on reproducible (minimal) testcase.

All our web cluster machines are affected.

Stanislav

What's the PHP configuration? Do you have a reproducible test case?

Stanislav Studený

Hi.

Phpinfo here : https://drive.google.com/file/d/1cpwdNarX4AR6ejKE2BBT1AvzBhPmbHAx/view?usp=sharing

I'm still working on reproductible and public testcase. Codebase is quite big and I need to minimize to be as small as possible. Running simple single SQL (which is failing) is not sufficient, because it doesn't reproduce problem. It looks like several conditions must be met and also identified to provide successfull testcase.

Regards,

Stanislav

vansul

Try over php 7.1, may be issue with the compatibility. as its working fine with php 7.1 so Downgrade PHP 7.2 to 7.1

Stanislav Studený

Okay, after some time of settling down here is the testcase. Actually it's about binding NULL.
It will SIGSEGV all clients (instant and full) from version 12.2. to 21.1.
Reported in SR 3-25065306931 : 19.9. Instantclient SIGSEGV (__intel_ssse3_rep_memcpy) @ libclntsh.so.19.1

putenv("ORACLE_BASE=/u01/app/oracle");
putenv("LD_LIBRARY_PATH=/u01/app/oracle/client/lib:/usr/lib:/lib");
putenv("ORACLE_HOME=/u01/app/oracle/client");
putenv("NLS_LANG=AMERICAN_AMERICA.AL32UTF8");
putenv("NLS_SORT=BINARY");
putenv("TNS_ADMIN=/u01/app/oracle/client/network/admin");
putenv("ORA_CLIENTTRACE_DIR=/tmp");
putenv("EVENT_10842=server=all;user=W3;stmt=all;level=15");

$per_id = null;

$sql = "SELECT count(*) FROM dual WHERE (:p_per_id IS NULL OR X = :p_per_id) AND :p_right_level_code = 'RA'";
$bax = array(
'p_per_id' => $per_id
,'p_right_level_code' => 'RA'
);

$connection = oci_pconnect('DB_USERNAME', 'DB_PASSWORD', 'POOLED_DB_DSN', 'AL32UTF8');
if ($connection === false) {
if (isset($connection)) {
$error = oci_error($connection);
} else {
$error = oci_error();
}
die("ERROR connection : $error");
}

$ociStatement = oci_parse($connection, $sql);
if ($ociStatement === false) {
$error = oci_error($connection);

oci_close($connection);
$connection = null;

die("oci_prepare error: $error");
}

foreach ($bax as $param => &$value) {
if (!oci_bind_by_name($ociStatement, $param, $value, -1, SQLT_CHR )) {
$error = oci_error($ociStatement);

oci_free_statement($ociStatement);
oci_close($connection);
$connection = null;

die("oci_bin_by_name error: $error");
}
}

if (!oci_execute($ociStatement, OCI_NO_AUTO_COMMIT)) {
$error = oci_error($ociStatement);

oci_free_statement($ociStatement);
oci_close($connection);
$connection = null;

die("oci_execute error: $error");
}

oci_free_statement($ociStatement);
oci_close($connection);
$connection = null;

Stanislav Studený

Failing stacktrace is always :

#0 0x00007f6fc9838fe6 __intel_ssse3_rep_memcpy (libclntsh.so.21.1)

#1 0x00007f6fca180a66 ttcGetSendInfo (libclntsh.so.21.1)

#2 0x00007f6fcb93ee6f ttcacs (libclntsh.so.21.1)

#3 0x00007f6fcb93fb75 ttcdrv (libclntsh.so.21.1)

#4 0x00007f6fcb90c1bd nioqwa (libclntsh.so.21.1)

#5 0x00007f6fcb8de645 upirtrc (libclntsh.so.21.1)

#6 0x00007f6fcb8f14c1 kpurcsc (libclntsh.so.21.1)

#7 0x00007f6fcb8e2d3b kpuexec (libclntsh.so.21.1)

#8 0x00007f6fcb8ddd69 OCIStmtExecute (libclntsh.so.21.1)

Stanislav Studený

PHP 7.2 + OCI8 2.2.0

Don't set Oracle environment variables in the script. Behavior can be undefined, e.g. https://stackoverflow.com/questions/66006203/php-putenv-return-inconsistent-value/66017553#66017553
LD_LIBRARY_PATH is not read after the process starts on Linux. Make sure you really have a clean environment.
Your SQL statement gives: PHP Warning: oci_execute(): ORA-00904: "X": invalid identifier
If I change the SQL to use 'X' I don't see any crash with Instant Client 21.1 on Linux - and a fetch returns 1.

Stanislav Studený

Hi.
It doesn't matter, if i set env vars before PHP or inside php script - result is always the same - sigsegv.
LD_LIBRARY_PATH is ok and this is also managed by ld config files (/etc/ld.so.conf.d). Also ldd is correct on oci8.so
Yes, that's expected and mentioned SQL is intentionally wrong (since X doesn't exists in dual)
Do you use the same exact script? (foreach ($bax as $param => &$value) <-- note & before $value
I know, that DB OCI programmable interface have not so much controls due to the nature of this interface, but i would definitely not expect crash of the client - in any way.
Regards,
Stanislav

Stanislav Studený

The same exact script doesn't sigsegv on 12.1 and older client. And yes, it returns ORA-00904.
Regards,
Stanislav

Stanislav Studený

If you want, you can edit and use :
SELECT count(*) FROM dual WHERE (:p_per_id IS NULL OR :p_per_id = :p_per_id) AND :p_right_level_code = 'RA'
this will trigger SIGSEGV and is correct SQL.

Stanislav Studený

I can also reproduce using php-cli with in-line env vars :
bash-4.4# ORACLE_HOME=/u01/app/oracle/client ORACLE_BASE=/u01/app/oracle NLS_LANG=AMERICAN_AMERICA.AL32UTF8 TNS_ADMIN=/u01/app/oracle/client/network/admin LD_LIBRARY_PATH=/u01/app/oracle/client/lib:/usr/lib:/lib php /www_base/testcase.php
Aborted (core dumped)

(putenv in php testcase script fully commented)

I tried on OL8.
dnf install -y php-xml php-devel php-cli php php-mbstring php-gd php-common php-pear php-json php-process
dnf install -y oracle-instantclient-release-el8
dnf install -y oracle-instantclient-basic oracle-instantclient-devel
echo "instantclient,/usr/lib/oracle/21/client64/lib" | pecl install oci8-2.2.0
Created the oci8 config file, as shown:
# cat /etc/php.d/99-oci8.ini
extension=oci8.so
oci8.connection_class=W3POOL
;oci8.events=on
oci8.statement_cache_size = 250
oci8.persistent_timeout = 3600
oci8.ping_interval = 10

-------------------------------------------------------------------------------
I used the script:
<?php

error_reporting(E_ALL);
ini_set('display_errors', 'On');

$connection = oci_connect('cj', 'cj', 'mdt/orclpdb1:pooled', 'AL32UTF8');

$per_id = null;

$sql = "SELECT count(*) FROM dual WHERE (:p_per_id IS NULL OR :p_per_id = :p_per_id) AND :p_right_level_code = 'RA'";
$bax = array(
'p_per_id' => $per_id
,'p_right_level_code' => 'RA'
);

if ($connection === false) {
$m = oci_error($connection);
trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}

$ociStatement = oci_parse($connection, $sql);
if ($ociStatement === false) {
$m = oci_error($connection);
trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

foreach ($bax as $param => &$value) {
if (!oci_bind_by_name($ociStatement, $param, $value, -1, SQLT_CHR )) {
$m = oci_error($ociStatement);
trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);
}
}

if (!oci_execute($ociStatement, OCI_NO_AUTO_COMMIT)) {
$m = oci_error($ociStatement);
trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

oci_fetch_all($ociStatement, $r);
var_dump($r);

print("End\n")

?>

-------------------------------------------------------------------------------
Ran the script:

php t.php

array(1) {
["COUNT(*)"]=>
array(1) {
[0]=>
string(1) "1"
}
}
End

I tried both a 19.3 DB and a 21c DB.

At this stage, you need to continue working on it. You need to share enough info about your environment so I can spot what is going on. (E.g. to check that you don't have ORACLE_HOME set when you are using Instant Client). You have to 'prove' it to me by showing me.
At the same time, work on the test script and triple-check any possible weak point even if you 'know' it is not a problem: eliminate DRCP. Unroll the bind loop to eliminate the reference use; change the bind to let the type default; see if it is an issue with repeated use of the bind name, etc. etc.

1 - 15
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 3 2014
Added on Dec 23 2013
2 comments
17,314 views