Sponsored Content
Top Forums Shell Programming and Scripting Passing Oracle function as file input to sqlplus Post 302991884 by durden_tyler on Friday 17th of February 2017 12:19:04 PM
Old 02-17-2017
Quote:
Originally Posted by amvip
...
It seems I really cant run both the inserts and the function in the same file and expect my script to catch the ref cursor returned by the function.
...
...
Of course you can! Smilie

In the following, the database part remains as it was earlier.
Sorry I don't have ksh at the moment. The shell script should not require any changes though.

Code:
$
$ cat -n insert_and_fetch.sql
     1  set head off
     2  set echo off
     3  set define off
     4  set feedback off
     5  set serveroutput on
     6  set timing off
     7  set pages 0
     8  insert into fn_test values(5555,4646,353454);
     9  commit;
    10  var l_refcursor refcursor
    11  exec :l_refcursor := test_fn
    12  print l_refcursor
    13
$
$ cat -n main_script.sh
     1  #!/usr/bin/bash
     2  sqlplus -s user/password@database << !
     3  @insert_and_fetch.sql
     4  !
     5
$
$ . main_script.sh
      5555       4646     353454
         1          2          3
         3          4          5
$
$

You can also do it without that here-document.

Code:
$
$ cat -n main_script_1.sh
     1  #!/usr/bin/bash
     2  exit | sqlplus -s user/password@database @insert_and_fetch.sql
     3
$
$
$ . main_script_1.sh
      5555       4646     353454
      5555       4646     353454
         1          2          3
         3          4          5
$
$

"exit" is a Bash shell built-in. If you don't have it in your shell, then the following should work as well.

Code:
$
$ cat -n main_script_2.sh
     1  #!/usr/bin/bash
     2  echo exit | sqlplus -s user/password@database @insert_and_fetch.sql
     3
$
$
$ . main_script_2.sh
      5555       4646     353454
      5555       4646     353454
      5555       4646     353454
         1          2          3
         3          4          5
$
$

 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

2. Shell Programming and Scripting

Passing global variable to a function which is called by another function

Hi , I have three funcions f1, f2 and f3 . f1 calls f2 and f2 calls f3 . I have a global variable "period" which i want to pass to f3 . Can i pass the variable directly in the definition of f3 ? Pls help . sars (4 Replies)
Discussion started by: sars
4 Replies

3. UNIX for Dummies Questions & Answers

Reading from a file(passing the file as input parameter)

hi I have a shell script say primary.sh . There is a file called params my scenario is primary.sh should read all the values and echo it for example i should pass like $primary.sh params output would be Abc ... (2 Replies)
Discussion started by: ssuresh1999
2 Replies

4. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./script.sh sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

5. Shell Programming and Scripting

Passing variable as an input file to AWK comand

Hi, I would like to compare 2 files using awk, which I can do by using: awk 'NR==FNR{a;next} (NR > 32 && $2 in a) {print $0}' File1 and File2. If the name of the File1 is in another file (for example, column 4 in File 3) then how can I pass this column 4 to the awk command. Thanks in... (1 Reply)
Discussion started by: ezhil01
1 Replies

6. Shell Programming and Scripting

Passing variables to an input file

Hi All, I have to insert 2 values to a text file in specific places. I have been able to extract each variable value via a script but am not able to send these variable values to the text file. Pasted is the script for extracting the variable values: for i in `ls -1` ... (2 Replies)
Discussion started by: danish0909
2 Replies

7. UNIX for Dummies Questions & Answers

Passing Input To Function

May i please know why is it printing the script name for $0 when i pass those parameters to function. #!/bin/bash -x usage() { echo "In Usage Function" echo $0 echo $1 echo $2 } echo "printing first time" echo $0 echo $1 echo $2 usage $0 $1 $2 Output: (2 Replies)
Discussion started by: Ariean
2 Replies

8. Shell Programming and Scripting

Passing variable from file to Oracle

cat a1 scott robert tom test script : #!/usr/bin/ksh for NAME in `cat a1` do VALUE=`sqlplus -silent "nobody/bobody01@testq" <<END set pagesize 0 feedback off verify off heading off echo off select username from dba_users where username=upper('$NAME'); END` if ; then echo... (3 Replies)
Discussion started by: jhonnyrip
3 Replies

9. Shell Programming and Scripting

Coredump when passing file name to ksh function

Hi, I'm playing with ksh. I'm trying to do a simple task: read file name from cli and call a function which calculated number of lines in file. I'm getting coredump every time when I try to read that file. Korn shell version $ print ${.sh.version} Version AJM 93u+ 2012-08-01 Main... (5 Replies)
Discussion started by: solaris_user
5 Replies

10. Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Hi, I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop. The first SQL file is basically a connection test and it is supposed to sort... (2 Replies)
Discussion started by: newbie_01
2 Replies
newgrp(1)							   User Commands							 newgrp(1)

NAME
newgrp - log in to a new group SYNOPSIS
Command /usr/bin/newgrp [-| -l] [group] sh Built-in newgrp [argument] ksh Built-in *newgrp [argument] ksh93 Built-in +newgrp [argument] DESCRIPTION
Command The newgrp command logs a user into a new group by changing a user's real and effective group ID. The user remains logged in and the cur- rent directory is unchanged. The execution of newgrp always replaces the current shell with a new shell, even if the command terminates with an error (unknown group). Any variable that is not exported is reset to null or its default value. Exported variables retain their values. System variables (such as PS1, PS2, PATH, MAIL, and HOME), are reset to default values unless they have been exported by the system or the user. For example, when a user has a primary prompt string (PS1) other than $ (default) and has not exported PS1, the user's PS1 is set to the default prompt string $, even if newgrp terminates with an error. Note that the shell command export (see sh(1) and set(1)) is the method to export variables so that they retain their assigned value when invoking new shells. With no operands and options, newgrp changes the user's group IDs (real and effective) back to the group specified in the user's password file entry. This is a way to exit the effect of an earlier newgrp command. A password is demanded if the group has a password and the user is not listed in /etc/group as being a member of that group. The only way to create a password for a group is to use passwd(1), then cut and paste the password from /etc/shadow to /etc/group. Group passwords are antiquated and not often used. sh Built-in Equivalent to exec newgrp argument where argument represents the options and/or operand of the newgrp command. ksh Built-in Equivalent to exec to/bin/newgrp argument where argument represents the options and/or operand of the newgrp command. On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways: 1. Variable assignment lists preceding the command remain in effect when the command completes. 2. I/O redirections are processed after variable assignments. 3. Errors cause a script that contains them to abort. 4. Words, following a command preceded by ** that are in the format of a variable assignment, are expanded with the same rules as a variable assignment. This means that tilde substitution is performed after the = sign and word splitting and file name genera- tion are not performed. ksh93 Built-in Equivalent to exec to/bin/newgrp argument where argument represents the options and/or operand of the newgrp command. On this man page, ksh93(1) commands that are preceded by one or two + (plus signs) are treated specially in the following ways: 1. Variable assignment lists preceding the command remain in effect when the command completes. 2. I/O redirections are processed after variable assignments. 3. Errors cause a script that contains them to abort. 4. They are not valid function names. 5. Words, following a command preceded by ++ that are in the format of a variable assignment, are expanded with the same rules as a variable assignment. This means that tilde substitution is performed after the = sign and field splitting and file name genera- tion are not performed. OPTIONS
The following option is supported: -l | - Change the environment to what would be expected if the user actually logged in again as a member of the new group. OPERANDS
The following operands are supported: group A group name from the group database or a non-negative numeric group ID. Specifies the group ID to which the real and effective group IDs is set. If group is a non-negative numeric string and exists in the group database as a group name (see getgr- nam(3C)), the numeric group ID associated with that group name is used as the group ID. argument sh and ksh only. Options and/or operand of the newgrp command. ENVIRONMENT VARIABLES
See environ(5) for descriptions of the following environment variables that affect the execution of newgrp: LANG, LC_ALL, LC_CTYPE, LC_MES- SAGES, and NLSPATH. EXIT STATUS
If newgrp succeeds in creating a new shell execution environment, whether or not the group identification was changed successfully, the exit status is the exit status of the shell. Otherwise, the following exit value is returned: >0 An error occurred. FILES
/etc/group System group file /etc/passwd System password file ATTRIBUTES
See attributes(5) for descriptions of the following attributes: /usr/bin/newgrp, ksh, sh +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWcsu | +-----------------------------+-----------------------------+ |Interface Stability |Committed | +-----------------------------+-----------------------------+ |Standard |See standards(5). | +-----------------------------+-----------------------------+ ksh93 +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWcsu | +-----------------------------+-----------------------------+ |Interface Stability |Uncommitted | +-----------------------------+-----------------------------+ SEE ALSO
login(1), ksh(1), ksh93(1), set(1), sh(1), Intro(3), getgrnam(3C), group(4), passwd(4), attributes(5), environ(5), standards(5) SunOS 5.11 2 Nov 2007 newgrp(1)
All times are GMT -4. The time now is 03:43 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy