05-12-2009
Execute PL/SQL function from Unix script (.sql file)
Hi guys,
I am new on here, I have a function in oracle that returns a specific value:
create or replace
PACKAGE BODY "CTC_ASDGET_SCHED" AS
FUNCTION FN_ASDSCHEDULE_GET
RETURN VARCHAR2
AS
BEGIN
DECLARE
ASDSchedule varchar2(6);
ASDComplete VARCHAR2(50);
BEGIN
(Some code here)
RETURN ASDComplete;
END;
END;
END CTC_ASDGET_SCHED;
So the above basically should return a value eg. 090512,
now in my .sql script (which I execute from a Korn Shell script) I have in Unix I call this function as follows:
SET AUTOPRINT OFF;
VAR VAL VARCHAR2(50);
VAR ASDSCHEDULE VARCHAR2(50);
VAR NAME VARCHAR2(50);
BEGIN
:VAL := MTMREP.CTC_ASDGET_SCHED.FN_ASDSCHEDULE_GET;
:NAME := MTMREP.M01100_MTMIMPORT.P01102_ImportControl(:VAL,'PF');
END;
/
quit;
I'm trying to pass the value which will be stored in (VAL) from the above first function (eg. 090512), I then need to pass this value in the 2nd procedure above called P01102_ImportControl but it still gives me an error.
The procedure P01102_ImportControl is as follows:
PROCEDURE P01102_ImportControl (ASDSchedule IN VARCHAR2, ASDVersion In VARCHAR2) AS
Begin
-- some code
End P01102_ImportControl;
Here is the error:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ppadev1 SQL> :NAME := MTMREP.M01100_MTMIMPORT.P01102_ImportControl(:VAL,'PF');
*
ERROR at line 3:
ORA-06550: line 3, column 10:
PLS-00222: no function with name 'P01102_IMPORTCONTROL' exists in this scope
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
Can someone please help me with this or give me some idea how to do this so it works. Thanks in advance.
Last edited by reptile; 05-12-2009 at 04:45 PM..
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
hi everybody...
can anyone help me in executing the .sql file with shell scripting....
thanx in advance (2 Replies)
Discussion started by: abuanas
2 Replies
2. Shell Programming and Scripting
Hi people, i need youre help on this.
I need to create an Shell SCript to list in a directory some sql files and then execute them one by one .
Also i need to have Logs from the executed Sql's to send to our costummers.
Can anyone help me on this
Regards,
osramos (2 Replies)
Discussion started by: osramos
2 Replies
3. Shell Programming and Scripting
Hi,
I know in oracle a .sql file is called by @ <path> /<filename>.
But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
Discussion started by: n2ekhil
0 Replies
4. Shell Programming and Scripting
Hello
I'm hoping someone may be able to help. I'm absolutely brand new to these shell scripts and have tried to bash bits together from the little learnt but my final script still doesn't work. Sorry if something similar is already posted but couldn't find anything existing close enough to help... (1 Reply)
Discussion started by: Dan27
1 Replies
5. Shell Programming and Scripting
Hi ,
I used the below script to get the sql data into csv file using unix scripting.
I m getting the output into an output file but the output file is not displayed in a separe columns .
#!/bin/ksh
export FILE_PATH=/maav/home/xyz/abc/
rm $FILE_PATH/sample.csv
sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies
6. Shell Programming and Scripting
Hi
I am new in unix. oracle and unix are installed in my sytem.i need the script
which could connect to the oracle using username ,password and schema and can run the select * from tab query.
Thanks
vijay (8 Replies)
Discussion started by: vijays3
8 Replies
7. Shell Programming and Scripting
hi,
I want to know how to call a pl sql function testfunction(param1,..) that returns a value and grab that value in a shell variable.
Thnx in advance
---------- Post updated 03-30-10 at 11:58 AM ---------- Previous update was 03-29-10 at 03:49 PM ----------
thnx a lot jim (0 Replies)
Discussion started by: austinhell3_16
0 Replies
8. Shell Programming and Scripting
Hi!
I would like to do a single connection to sqlplus and execute some querys.
Actually I do for every query one connection to database
i.e
echo 'select STATUS from v$instance;
exit' > $SQL_FILE
sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT
echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies
9. Shell Programming and Scripting
Hi,
I have a function which connects to the db and runs the sql.
it works fine when I run it like: function "select empname from emp;"
but when I try to pass the sql string to a variable which in turn in fed to the function , it throws error.
please advise.
Thanks,
Arnie. (1 Reply)
Discussion started by: itsarnie
1 Replies
10. Shell Programming and Scripting
Hi,
I am new to shell scripting and i need to write a automation script to execute sql files. I need to check the table if it is there in system tables and need to write a function to call the .sql files.
For ex. I have a.sql,b.sql,c.sql files, where the sql file contains DELETE and INSERT... (1 Reply)
Discussion started by: Samah
1 Replies
LEARN ABOUT PHP
oci_bind_array_by_name
OCI_BIND_ARRAY_BY_NAME(3) OCI_BIND_ARRAY_BY_NAME(3)
oci_bind_array_by_name - Binds a PHP array to an Oracle PL/SQL array parameter
SYNOPSIS
bool oci_bind_array_by_name (resource $statement, string $name, array &$var_array, int $max_table_length, [int $max_item_length = -1],
[int $type = SQLT_AFC])
DESCRIPTION
Binds the PHP array $var_array to the Oracle placeholder $name, which points to an Oracle PL/SQL array. Whether it will be used for input
or output will be determined at run-time.
PARAMETERS
o $statement
- A valid OCI statement identifier.
o $name
- The Oracle placeholder.
o $var_array
- An array.
o $max_table_length
- Sets the maximum length both for incoming and result arrays.
o $max_item_length
- Sets maximum length for array items. If not specified or equals to -1, oci_bind_array_by_name(3) will find the longest element
in the incoming array and will use it as the maximum length.
o $type
- Should be used to set the type of PL/SQL array items. See list of available types below:
o SQLT_NUM - for arrays of NUMBER.
o SQLT_INT - for arrays of INTEGER (Note: INTEGER it is actually a synonym for NUMBER(38), but SQLT_NUM type won't work in
this case even though they are synonyms).
o SQLT_FLT - for arrays of FLOAT.
o SQLT_AFC - for arrays of CHAR.
o SQLT_CHR - for arrays of VARCHAR2.
o SQLT_VCS - for arrays of VARCHAR.
o SQLT_AVC - for arrays of CHARZ.
o SQLT_STR - for arrays of STRING.
o SQLT_LVC - for arrays of LONG VARCHAR.
o SQLT_ODT - for arrays of DATE.
RETURN VALUES
Returns TRUE on success or FALSE on failure.
EXAMPLES
Example #1
oci_bind_array_by_name(3) example
<?php
$conn = oci_connect("hr", "hrpwd", "localhost/XE");
if (!$conn) {
$m = oci_error();
trigger_error(htmlentities($m['message']), E_USER_ERROR);
}
$create = "CREATE TABLE bind_example(name VARCHAR(20))";
$stid = oci_parse($conn, $create);
oci_execute($stid);
$create_pkg = "
CREATE OR REPLACE PACKAGE ARRAYBINDPKG1 AS
TYPE ARRTYPE IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
PROCEDURE iobind(c1 IN OUT ARRTYPE);
END ARRAYBINDPKG1;";
$stid = oci_parse($conn, $create_pkg);
oci_execute($stid);
$create_pkg_body = "
CREATE OR REPLACE PACKAGE BODY ARRAYBINDPKG1 AS
CURSOR CUR IS SELECT name FROM bind_example;
PROCEDURE iobind(c1 IN OUT ARRTYPE) IS
BEGIN
-- Bulk Insert
FORALL i IN INDICES OF c1
INSERT INTO bind_example VALUES (c1(i));
-- Fetch and reverse
IF NOT CUR%ISOPEN THEN
OPEN CUR;
END IF;
FOR i IN REVERSE 1..5 LOOP
FETCH CUR INTO c1(i);
IF CUR%NOTFOUND THEN
CLOSE CUR;
EXIT;
END IF;
END LOOP;
END iobind;
END ARRAYBINDPKG1;";
$stid = oci_parse($conn, $create_pkg_body);
oci_execute($stid);
$stid = oci_parse($conn, "BEGIN arraybindpkg1.iobind(:c1); END;");
$array = array("one", "two", "three", "four", "five");
oci_bind_array_by_name($stid, ":c1", $array, 5, -1, SQLT_CHR);
oci_execute($stid);
var_dump($array);
?>
PHP Documentation Group OCI_BIND_ARRAY_BY_NAME(3)