Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Execute PL/SQL function from Unix script (.sql file)
Top Forums UNIX for Dummies Questions & Answers Execute PL/SQL function from Unix script (.sql file) Post 302315532 by reptile on Tuesday 12th of May 2009 03:28:16 PM
Old 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

How to execute a .sql file with shell script

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

Shell Script to execute SQL's

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

TO execute .sql 2005 query file in shell script

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

Creating a .sh script to execute an SQL file

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

How to use sql data file in unix csv file as input to an sql query from shell

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

Execute SQL query in unix script

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

Call a pl sql function from unix

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

Execute multiple SQL scripts from single SQL Plus connection

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

Passing sql as parameter to unix function

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

Need help to write a function in shell scripting to execute sql files

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 DEBIAN

sqlobject-admin

SQLOBJECT-ADMIN(1)					      General Commands Manual						SQLOBJECT-ADMIN(1)

NAME

       sqlobject-admin - manage your database as defined with SQLObject classes

SYNOPSIS

       sqlobject-admin [options] command

DESCRIPTION

       The sqlobject-admin tool included with SQLObject allows you to manage your database as defined with SQLObject classes.

       Some of the features include creating tables, checking the status of the database, recording a version of a schema, and updating the
       database to match the version of the schema in your code.

COMMANDS

       The following commands are recognized:

       help
	   Show the help text.

       create
	   Find the tables and create them. Existing tables are simply skipped.

       sql
	   Show the SQL to create all the tables.

       drop
	   Drop tables. Missing tables are skipped.

       execute
	   Execute an arbitrary SQL expression.

       list
	   List out all the found classes.

       status
	   Show if tables are present in the database.

       record
	   Record historical information about the database status

       upgrade
	   Update the database to a new version (as created by record)

OPTIONS

       -c CONNECTION, --connection=CONNECTION
	   Connection string for the database .

       -f FILENAME, --config-file=FILENAME
	   Configuration file from which to get the connection to the database.

       -m MODULE, --module=MODULE
	   A module to look in for classes.

       -p PACKAGE, --package=PACKAGE
	   A package to look in.

       --class=CLASSMATCH
	   Restricting the found classes to the matching classes.

       --egg=EGG_SPEC
	   Egg description that should be loaded.

AUTHORS

	   This manual page was written by Carl Chenet <chaica@ohmytux.com>.

LICENSE

	   This package is licensed under the GNU Lesser General Public License. You can
	   find the full text of the LGPL in /usr/share/common-licenses/LGPL in your
	   Debian system.

								    06/03/2010							SQLOBJECT-ADMIN(1)
All times are GMT -4. The time now is 06:02 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy