This has been tested on Solaris using dtksh and Oracle 10g.
Construct the query so it returns rows as name=value, so they can be used in the shell script.
Read the query output a line at a time.
for each row returned, if it is not null, use eval to make it a shell variable.
Hi-
I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment...
I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Hi,I have a single sql file containing many create table ddl's.Example:
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only... (5 Replies)
Is there anyway to spool my select statement into spool files of max 10000 records each?
eg I have a select statement that will return 45000 records.
A normal spool command will output the 45000 into just one spool file.
How can I make sqlplus do this?
00001 - 10000 records --- spool... (3 Replies)
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... (1 Reply)
Hi All,
Please help me to write a shell script to execute the below sql query.
select c.account_no,b.bill_no,a.pay_type,(b.total_due + b.recvd + b.adjusted + b.disputed + b.transferred) as amt_not_billed,d.cash_on_delivery,
(select j.bill_no from
billinfo_T y,
bill_t j
where... (1 Reply)
I am using HP-UX:
I have written a ksh script where I need to connect to sqlplus and execute few sql scripts. Part of this code is -
sqlplus user/temp1234 <<!
set serverout on
set feedback off
set pages 1000
set colsep ,
set echo off
spool /home/supp1/pks/output.csv... (8 Replies)
Hi,
I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data.
Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Hi Experts:
I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks
any help would be appreciated
:wall: (1 Reply)
Hello ,
Im calling every single file inside my script like 1.sql,2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting errorUnexpected EOF] , can anyone please help me out in this..
How i can use for loop to invoke my... (6 Replies)
Discussion started by: preethi87
6 Replies
LEARN ABOUT PHP
pg_query_params
PG_QUERY_PARAMS(3)PG_QUERY_PARAMS(3)pg_query_params - Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL commandtext.SYNOPSIS
resource pg_query_params ([resource $connection], string $query, array $params)
DESCRIPTION
Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text.
pg_query_params(3) is like pg_query(3), but offers additional functionality: parameter values can be specified separately from the command
string proper. pg_query_params(3) is supported only against PostgreSQL 7.4 or higher connections; it will fail when using earlier versions.
If parameters are used, they are referred to in the $query string as $1, $2, etc. The same parameter may appear more than once in the
$query; the same value will be used in that case. $params specifies the actual values of the parameters. A NULL value in this array means
the corresponding parameter is SQL NULL.
The primary advantage of pg_query_params(3) over pg_query(3) is that parameter values may be separated from the $query string, thus avoid-
ing the need for tedious and error-prone quoting and escaping. Unlike pg_query(3), pg_query_params(3) allows at most one SQL command in the
given string. (There can be semicolons in it, but not more than one nonempty command.)
PARAMETERS
o $connection
- PostgreSQL database connection resource. When $connection is not present, the default connection is used. The default connection
is the last connection made by pg_connect(3) or pg_pconnect(3).
o $query
- The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not
allowed.) If any parameters are used, they are referred to as $1, $2, etc. User-supplied values should always be passed as param-
eters, not interpolated into the query string, where they form possible SQL injection attack vectors and introduce bugs when han-
dling data containing quotes. If for some reason you cannot use a parameter, ensure that interpolated values are properly escaped.
o $params
- An array of parameter values to substitute for the $1, $2, etc. placeholders in the original prepared query string. The number
of elements in the array must match the number of placeholders. Values intended for bytea fields are not supported as parameters.
Use pg_escape_bytea(3) instead, or use the large object functions.
RETURN VALUES
A query result resource on success or FALSE on failure.
EXAMPLES
Example #1
Using pg_query_params(3)
<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");
// Find all shops named Joe's Widgets. Note that it is not necessary to
// escape "Joe's Widgets"
$result = pg_query_params($dbconn, 'SELECT * FROM shops WHERE name = $1', array("Joe's Widgets"));
// Compare against just using pg_query
$str = pg_escape_string("Joe's Widgets");
$result = pg_query($dbconn, "SELECT * FROM shops WHERE name = '{$str}'");
?>
SEE ALSO pg_query(3).
PHP Documentation Group PG_QUERY_PARAMS(3)