04-27-2007
This question has been asked at least 50 times in the past year.
Please search the Forums for the word 'oracle' and you'll find how to create a here document to do it.
10 More Discussions You Might Find Interesting
1. UNIX for Advanced & Expert Users
Hi All
I would be thankful to you all if you will guide me the steps to call a stored proc. from unix shell script. that stored proc. could be parameterised or parameterless developed in SQL.
Any info. in this topic would help me.....
Thanks in advance.... (1 Reply)
Discussion started by: varungupta
1 Replies
2. UNIX for Advanced & Expert Users
Hi All,
I am getting coredump error, when I try to execute Oracle SQL*Loader from Shell script in Unix environment. But SQL*Loader from local machine runs fine with same database.
SQL*Loader: Release 9.2.0.6.0 - Production on Mon Apr 23 05:23:47 2007
Copyright (c) 1982, 2002, Oracle... (3 Replies)
Discussion started by: srinivas_paluku
3 Replies
3. Shell Programming and Scripting
Hi,
How can i compile the procedure code that is there in a script file (.sql) in unix. (0 Replies)
Discussion started by: krishna_gnv
0 Replies
4. Shell Programming and Scripting
In c:/zaheer/123-456/sql/ folder i have below 7 sql files.
cv_abc_a.sql
cv_abc_b.sql
cv_abc_c.sql
vtemp_d.sql
vtemp_e.sql
cv_abc_f.sql
Exviews.sql
In Exviews.sql file i have mentioned all cv and vtemp files to run like
start ${SQLDIR}/cv_abc_a.sql
start ${SQLDIR}/cv_abc_b.sql... (1 Reply)
Discussion started by: Zaheer.mic
1 Replies
5. Programming
Hi,
Can anyone please let me know where to check if a particular stored procedure exists.
If the procedure exists I want to display some message and if the procedure does not exists i want to exit with error message.
checking from dba_objects doesnt help. suprisingly the procedure i... (3 Replies)
Discussion started by: justchill
3 Replies
6. Programming
Hi,
Can any one help me how to write a sql procedure in a pro *c file for selecting the data from a database and inserting the rows into a queue in a .pc file.
thanx in advance. (1 Reply)
Discussion started by: jhon1257
1 Replies
7. Programming
Hello, I need some advice how to to create one big transactional table.
My table has following columns
person_id, trans_id, date, dep_id, material_id, input, outpu, total
I created procedure from which I will enter all transaction into that table. Problem is I don't have any idea how... (3 Replies)
Discussion started by: solaris_user
3 Replies
8. UNIX for Dummies Questions & Answers
Hi,
Could you please help me on this.
I have bulk of queries written in text file. I want to use those queries and want to execute from UNIX.
I don't want to run this file as a sql file as this file will change every week.
I want to run it in my environment as bulk sql statement from text... (1 Reply)
Discussion started by: abhii
1 Replies
9. UNIX for Dummies Questions & Answers
Hello All,
I am writing the below unix script to email the result of a small pl sql procedure:
#!/bin/bash
ORACLE_HOME=/opt/oracle/orcts/product/9.2.0; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
sqlplus -s user/pass@Db_instance<<EOF
set echo off
set feedback off
set pages 0... (9 Replies)
Discussion started by: Bunty bedi
9 Replies
10. Shell Programming and Scripting
Hi,
We have a procedure e.g. prc_synonym created in Oracle 12c Database. I want to do small change in procedure through Unix. I have that changed procedure (prc_synonym) in proc.sql file. Want to recompile that procedure through Unix so that changes should reflect in existing procedure in... (10 Replies)
Discussion started by: Aparna.N
10 Replies
LEARN ABOUT PHP
oci_parse
OCI_PARSE(3) OCI_PARSE(3)
oci_parse - Prepares an Oracle statement for execution
SYNOPSIS
resource oci_parse (resource $connection, string $sql_text)
DESCRIPTION
Prepares $sql_text using $connection and returns the statement identifier, which can be used with oci_bind_by_name(3), oci_execute(3) and
other functions.
Statement identifiers can be freed with oci_free_statement(3) or by setting the variable to NULL.
PARAMETERS
o $connection
- An Oracle connection identifier, returned by oci_connect(3), oci_pconnect(3), or oci_new_connect(3).
o $sql_text
- The SQL or PL/SQL statement. SQL statements should not end with a semi-colon (";"). PL/SQL statements should end with a semi-
colon (";").
RETURN VALUES
Returns a statement handle on success, or FALSE on error.
EXAMPLES
Example #1
oci_parse(3) example for SQL statements
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
// Parse the statement. Note there is no final semi-colon in the SQL statement
$stid = oci_parse($conn, 'SELECT * FROM employees');
oci_execute($stid);
echo "<table border='1'>
";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>
";
foreach ($row as $item) {
echo " <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>
";
}
echo "</tr>
";
}
echo "</table>
";
?>
Example #2
oci_parse(3) example for PL/SQL statements
<?php
/*
Before running the PHP program, create a stored procedure in
SQL*Plus or SQL Developer:
CREATE OR REPLACE PROCEDURE myproc(p1 IN NUMBER, p2 OUT NUMBER) AS
BEGIN
p2 := p1 * 2;
END;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$p1 = 8;
// When parsing PL/SQL programs, there should be a final semi-colon in the string
$stid = oci_parse($conn, 'begin myproc(:p1, :p2); end;');
oci_bind_by_name($stid, ':p1', $p1);
oci_bind_by_name($stid, ':p2', $p2, 40);
oci_execute($stid);
print "$p2
"; // prints 16
oci_free_statement($stid);
oci_close($conn);
?>
NOTES
Note
This function does not validate $sql_text. The only way to find out if $sql_text is a valid SQL or PL/SQL statement is to execute
it.
SEE ALSO
oci_execute(3), oci_free_statement(3).
PHP Documentation Group OCI_PARSE(3)