PL/SQL stored proc from ksh just inserts thumb and does nothing
Greeting everyone. Ok, I have spent the past few days googling for this and I keep hitting a wall. Many results brought me here, but the solutions were not quite right for this.
Basically my script (ksh) is run with an arg for a csv. My script so far appears to be storing the values from my input file as variables.
These are then used in my stored proc with the intent of loading these into my aq. Problem is that it runs, but I am getting no errors or any feedback to indicate that its failing or where its failing. I can tell that its not working as there is no activity or records in the queue.
Previous iterations of this have been used using a wrapper script calling a .sql file. In those instances the values I needed were hard coded into the sql. Since I'm in QC and not dev I needed a method to test many various records, so this is what I came up with.
Code:
#!/bin/ksh
IFS=','
while read imsi msisdn segment country
do
sqlplus -s user/pass@sid << EOF > foo.log
declare
v_qname VARCHAR2(20) := 'RS_AQ_AOTA';
v_source VARCHAR2(10) := 'D1';
v_sysid VARCHAR2(4) := 'RSS1';
v_imsi VARCHAR2(20) := $imsi;
v_msisdn VARCHAR2(10) := $msisdn;
v_regid NUMBER := 100000000000000010;
v_sedid NUMBER := 100000000228103870;
v_tedid NUMBER := 100000000000000157;
v_segment VARCHAR2(3) := $segment;
v_dest VARCHAR2(3) := 'CTL';
v_dlstatus VARCHAR2(1) := 'N';
v_country VARCHAR2(3) := $country;
v_carrier VARCHAR2(5) := 'USAWW';
v_zone VARCHAR2(3) := ' ';
v_evid NUMBER := 900000000000000004;
v_timestamp date := sysdate;
BEGIN
rss_aq.rss_enqueue(v_qname,
v_source, v_sysid, v_imsi, v_msisdn,
v_regid, v_SEDid,v_TEDID, v_segment,
v_dest, v_dlstatus, v_country,v_zone, v_timestamp, 0, 'F', v_carrier, v_evid);
commit;
EXCEPTION
when others then
dbms_output.put_line('Error code: '||sqlcode);
dbms_output.put_line('Error msg: '||sqlerrm);
END;
set serveroutput on
EOF
exit;
done < $1
Thanks to all in advance!
Last edited by dezdiggler; 05-11-2016 at 01:28 PM..
:)
hi all !
Please help me
When I select data from oracle with proc * C prog.
I count the number of rows
For example the total rows is 1000000
but the number of result return is a limit number 5000 for ex
So How can I know this limit (5 Replies)
Hi All,
Want to know if is it possible to run / execute any stored procedures (sybase) from unix command prompt.?
Thanks for your help in Advance.
Regards,
Arvind S. (0 Replies)
Hi All,
I want to run/execute a stored procedure (sybase) from unix command prompt not by login in isql utility which is provided my Sybase guys.
Is there way ..?
Thanks in advance for your help !!!
Regards,
Arvind S. (0 Replies)
Greetings,
I need to make an open server call to a shell script from inside a Sybase Stored procedure.
Coul any one please provide a sample code?
TIA (0 Replies)
I have a file that reads File (X.txt)
Contents of record 1:
rdrDESTINATION_ADDRESS (String) "91 971502573813"
rdrDESTINATION_IMSI (String) "000000000000000"
rdrORIGINATING_ADDRESS (String) "d0 movies"
rdrORIGINATING_IMSI (String) "000000000000000"
rdrTRAFFIC_EVENT_TIME... (0 Replies)
Hi,
I am writing a script that needs to call a stored proc which would update a column in a table based on a condition.
I need to also capture the number of rows updated.
However, When I execute the script I keep getting this error:
./test_isql.sh: syntax error at line 33: `end of file'... (3 Replies)
I have a very simple set up
I am connecting to a MS SQL db using SQSH statement from a shell script
In this sqsh connection i am trying to execute a stored proc
However I want to capture the value returned by the stored proc.
I haven't really come across anything useful so far which would... (0 Replies)
Discussion started by: shishirkotkar
0 Replies
9. Post Here to Contact Site Administrators and Moderators
Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File, somehow i tried to find a solution but when i try to run the script i am not getting the output file with... (1 Reply)
Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File.somehow i try to find a solution but whwn i try to run the script i am not getting the output file with... (1 Reply)
Discussion started by: Arun619
1 Replies
LEARN ABOUT PHP
oci_fetch_object
OCI_FETCH_OBJECT(3)OCI_FETCH_OBJECT(3)oci_fetch_object - Returns the next row from a query as an objectSYNOPSIS
object oci_fetch_object (resource $statement)
DESCRIPTION
Returns an object containing the next result-set row of a query. Each attribute of the object corresponds to a column of the row. This
function is typically called in a loop until it returns FALSE, indicating no more rows exist.
For details on the data type mapping performed by the OCI8 extension, see the datatypes supported by the driver
PARAMETERS
o $statement
-A valid OCI8 statement identifier created by oci_parse(3) and executed by oci_execute(3), or a REF CURSOR statement identifier.
RETURN VALUES
Returns an object. Each attribute of the object corresponds to a column of the row. If there are no more rows in the $statement then FALSE
is returned.
Any LOB columns are returned as LOB descriptors.
DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment vari-
ables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.
Oracle's default, non-case sensitive column names will have uppercase attribute names. Case-sensitive column names will have attribute
names using the exact column case. Use var_dump(3) on the result object to verify the appropriate case for attribute access.
Attribute values will be NULL for any NULL data fields.
EXAMPLES
Example #1
oci_fetch_object(3) example
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description VARCHAR2(30));
INSERT INTO mytab (id, description) values (1, 'Fish and Chips');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
// Use upper case attribute names for each standard Oracle column
echo $row->ID . "<br>
";
echo $row->DESCRIPTION . "<br>
";
}
// Output is:
// 1
// Fish and Chips
oci_free_statement($stid);
oci_close($conn);
?>
Example #2
oci_fetch_object(3) with case sensitive column names
<?php
/*
Before running, create the table with a case sensitive column name:
CREATE TABLE mytab (id NUMBER, "MyDescription" VARCHAR2(30));
INSERT INTO mytab (id, "MyDescription") values (1, 'Iced Coffee');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, "MyDescription" FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
// Use upper case attribute names for each standard Oracle column
echo $row->ID . "<br>
";
// Use the exact case for the case sensitive column name
echo $row->MyDescription . "<br>
";
}
// Output is:
// 1
// Iced Coffee
oci_free_statement($stid);
oci_close($conn);
?>
Example #3
oci_fetch_object(3) with LOBs
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_object($stid)) != false) {
echo $row->ID . "<br>
";
// The following will output the first 11 bytes from DESCRIPTION
echo $row->DESCRIPTION->read(11) . "<br>
";
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>
SEE ALSO oci_fetch(3), oci_fetch_all(3), oci_fetch_assoc(3), oci_fetch_array(3), oci_fetch_row(3).
PHP Documentation Group OCI_FETCH_OBJECT(3)