I want to pass a variable in unix to oracle exp command
I have a file with owner and password
I did
export user=$(head -$riga $file | tail -1)
echo $user
let riga+=1
export psw=$(head -$riga $file | tail -1)
echo $psw
exp $user/$psw file=exp_$current.dmp
but this doesn't work.
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is taking
here is the detailed program ... why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
connect by prior parent=child
union
[Code]...
select to_date('13:14:00', 'HH24:MI:SS') FROM DUAL;
what is output of this?
and why this result is coming?
I am trying convert number value in date. I know somewhere I doing mistake. But I cant get it.
Here is my Partial Code
create or replace
PROCEDURE "REPORT_ARTICLEMOSTVIEWED2"
(
[Code]....
Error starting at line 5 in command:
EXEC REPORT_ARTICLEMOSTVIEWED2(null,null,null,null,:RC)
Error report:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "IIS_ORACLE_11GR2_LIVE.REPORT_ARTICLEMOSTVIEWED2", line 22
ORA-06512: at line 1
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
RC
How do I put condition for Null value in this procedure And set dateTo = sysdate if v_day,v_month,v_year are null.
how can I pass the sh variable (.i.e file name stored in sh variable called($F)) as a input of below mention procedure (YODEL_XL_ INS_SDG_ COMMER_ PROD)
for F in *.dat; do
#
echo $F
#
#sqlldr apps/apps control=$CONTROL data=$F
# Below Part is used for Add the file name into table
[code]...
I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input
i.e.
The following sql when executed at sqlplus prompt gives correct results.
select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');
How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.
i.e I should be able to call the procedure as follows
sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;
Should the cursor inside the procedure be built as follows
cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';
Iam unable to build the sql for the cursor.
I'm trying to pass a JSON string into a stored procedure that has a single VARCHAR2 variable.
begin
SAVE_JSON('{"app_id":"800414","merch_id":"84657156","user_id":"6985","P25_DBA_NAME":"TEST"}');
end;
/
I'm getting the ORA-20001: ORA-06502: PL/SQL: numeric or value error
4.1.2theme 24
I have a .js process in the before header of a page which has this pl/sql process using htp script javascript function {code}
function check(flag_val){ if(flag_val=="N")
document.getElementById("btn").disabled = true;
else
document.getElementById("btn").disabled = false; }
{code}
and then there is the actual pl/sql process which has a cursor
{code}
declareflg varchar2(1);
..begin selectflag into v_flagfrom table where rownum = 1;
-- actual conditions are different but this returns one value.
Then below using htp package I had html table with something like
<td align="right">
<button class="btn btn-success" type="button" id="btn" onclick="check(flg);"
title="Accept Lead" style=" width:50px;">Flag value</button> </td>
{code}
For some reason the script function is not called if I use onload. If I change the onload to onclick it works. Also, if I pass check(flg) it does not work. If I use check(''N'') then is works fine. question is how do I pass a pl/sql variable flg in this case to a javascript function as a parameter? I basically, want the value of flg checked on page load and then if it is N then disable the button. Or else keep it enabled.
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
[code]....
All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.
I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.
Here's my attempt (the names have been changed to protect the innocent!):
declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO
[code]....
is there a way where i can pass the value from unix to forms?
View 1 Replies View RelatedI am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.
int sysrun(char *command)
{
return system(command);
}
This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/
Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.
SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0
PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
[code]...
I have to pick a text file created in UNIX server directory and send it as an mail attachment.The problem here is, when the file size exceeding more than 10 MB. I think there is a way to zip a file in UNIX with some .gz extension or something like that.
how to zip a file in unix server using PL/SQL code. So that I will pick the zipped file and send it as an attachment.
Iam planning to upgrade my Oracle Database from 10G to 11G .
Its on HP Unix Environment 11.23 and
Oracle CRS 10.2.0.4
Oracle ASM 10.2.0.4
Oracle RDBMS 10.2.0.4
Its in RAC env...and Active-Active DB running controlled by tnsnames.ora .
how to edit profile on unix [x86-32] during 1og oracle installation. edit /export/home/oracle/.profile
View 3 Replies View Relatedcreate or replace procedure log_file
(
odsm_profile IN VARCHAR2
)
AS
handle utl_file.file_type;
begin
[code].....
i have error when executing this pl/sql procedure in timesten in UNIX
this is the excution error:
Command> exec log_file('hi');
8507: ORA-29280: invalid directory path
8507: ORA-06512: at "SYS.UTL_FILE", line 41
8507: ORA-06512: at "SYS.UTL_FILE", line 478
8507: ORA-06512: at "SIUA.LOG_FILE", line 8
8507: ORA-06512: at line 1
The command failed.
how to convert date into UNIX time in oracle ?
View 1 Replies View RelatedI am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.
The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.
select
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code].......
This code produces the result:
What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.
I did try to group by PENTERED(which is the unix time stamp column)
select pentered,
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code]......
This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.
I'm trying to install Oracle 11gR2 RAC on AIX, do we need to turn on Multicasting, can we install without multicasting? Also can I have the ASM disk with external redundancy for OCR and Voting Disk?
View 3 Replies View RelatedBelow is my code for calling a report from form in windows environment. I want to use the same report to be called from Unix environment . When m using application server tbanner@.... instead of 'repsrv@ahad-pc' is throwing error its in UNIX environment
REP-51002: Bind to Reports Server tbannerdb.kfupm.edu.sa failed
Declare
v_show_documentVARCHAR2 (2000) := '/reports/rwservlet?';
v_connectVARCHAR2 (200) := 'userid=abdulahad/inshalla@test';
v_report_serverVARCHAR2 (30) := 'repsrv@ahad-pc';
v_report_nameVARCHAR2(100) := 'test3.rdf';
[code].......
I have a UNIX shell script as following,
#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........
I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.
rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37
The oracle Procedure's SQL is as following,
SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........
The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..
Do I need to convert the Date ? ( But i_last_update_datetime is DATE)
I'm on Oracle 11g R1. I've a requirement where user will be putting CSV files on Unix server and I've to create a job which runs periodically to check if any new file is added by user in the folder. If it finds a new file (s), then it needs to identify it and insert its name in an Oracle table. Once file's name is noted, it has to move file from that directory to another one.
View 2 Replies View RelatedI have a requirement where i need to execute a unix shell script "a.sh" having permissions user1,group1 and no access to public. This should be executed from Oracle function.
So i wrote a routine in Java and it works fine when i execute a command like Date etc.
But when i execute "a.sh" from oracle client, its returning error that "could not be executed-permission denied". This is because once login happened through the "sqlplus" the shell takes user as "oracle" and group as "dba" but the script "a.sh" having only permissions user1,group1. Unfortunately we are not allowed to change the permission for "a.sh" to give execute permission to all(public).
After searching in internet, i understand that one way is to make the password (/etc/passwd) for the user "user1" as no password and can use "su" command so that it wont ask password while invoking it.
Is there any other way apart from this doing changes in password file at UNIX level when executing a script from oracle client?
if further information is required.
Oracle: version: 11.2.0.1
UNIX : AIX -5.9
I installed a printer(HP Laserjet 3390) in UNIX(Solaris).Followed the instructions while installing as to the path it should be present for Reports to read it etc.,Also modified the uiprint.txt file with the desired .hpd file.Logging on to the UNIX machine, I am using the below command
rwrun.sh report=xyz.rdf userid=XXXX/XXX@xxx paramform=no p_orig_org='0825' desformat=pdf destype=printer desname=xxxx batch=yes
This command invokes the printer and
^[%12345X@PJL
@PJL SET RESOLUTION=300
@PJL SET PAGEPROTECT=OFF
@PJL SET ENTER LANGUAGE=PCL
^[%1A^[E^
@PJL RESET
^[%12345X
is what it prints.Realizing that this is because of the missing parameter,tried to change paramform=yes but I am not sure how to pass the parameter.
I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..
I know we can remove the text file from UNIX and create file again with same name...
Is there any other way??
my problem is this. I created the java classes using itext to create a document library. Rtf, or. Odt. I integrated classes in Oralce form and everything works. I created a file. Odt application server on a folder and now only remains for me to open the file. The roads are two:
1. Directly from the server, you can?
2. From the client using the API or openoffice launching the executable with cmd?
Oracle Forms is a command to transfer files on the client and run the executable swriter of openoffice?
In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used
export LANG="universal.UTF-8" in order to make it English. which works fine.
But my question is how to retain this special character intact as, it is in DB?
So in UNIX script
export LANG="universal.UTF-8"
sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000
spool /path/out.txt
Select name from tablename where is=12;
spool off;
exit
ENDSQL
Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.
I am trying to create a DB manually.
1) How to create a UNIX services as we use oradim in windows.
2) While trying to use DBCA command it shows the below message.
SET DISPLAY ENVIRONMENT THEN RE-RUN.
My login is already in admin group only.
An application uses one user as a container of objects(this user is stripped of ALL privileges), and other users that can access objects in this unique schema depending on their privileges.
There can be multiple installations of the application in one database, therefore many such schemes. An environment variable APP_UNIQUE_SCHEMA points to a current unique schema.
So instead of typing
select * from my_unique_schema.my_table I want to set a current schema for sqlplus session in glogin.sql.
It would have been nice if I could have entered in glogon.sql the following line
alter session set current_schema=$APP_UNIQUE_SCHEMA;
BUT it does not work. sqlplus does not resolve the reference to an environment variable. I cannot figure out how to get the value of an environment variable in sqlplus.
Am tring to export a data from oracle forms to excel. I use ole2 option. it is working perfectly in my desktop. But when i move the same to our application server, it is not working. I belive OLE is related to Microsoft office and our server is unix based.
View 1 Replies View Related