I am facing a problem regarding the execute immediate command. I have created a procedure as given below
SQL> set echo on ; SQL> set serveroutput on; SQL> declare 2 l_var varchar2(50); 3 sqlstring varchar2(3000); 4 begin [code].......
In this procedure the execute immediate command shows error ( if i avoid exception).I have tried other syntax too of this command but it is showing error only.
I have created a procedure, which should be executed on the below condition with EXECUTE IMMEDIATE COMMAND. But i am getting error.
The error shows the procedure/function name is not existing. But it is exist.
SQL> 1 Declare 2 a varchar2(20); 3 b varchar2(20); 4 c varchar2(1000); 5 begin 6 select to_char(sysdate,'day') into a 7 from dual; 8 select to_char(sysdate,'HH24') into b 9 from dual; 10 if 11 (a='friday' and b>=22) 12 or 13 (a='saturday' and b<=6) 14 or 15 (a='wednesday' and b>=9) 16 then 17 begin 18 EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;'); 19 end; 20 else 21 null; 22 end if; 23* end; SQL> / EXECUTE IMMEDIATE ('begin'||BACKUP_AUTO_execute_bat_file||'end;'); * ERROR at line 18: ORA-06550: line 18, column 32: PLS-00222: no function with name 'BACKUP_AUTO_EXECUTE_BAT_FILE' exists in this scope ORA-06550: line 18, column 4: PL/SQL: Statement ignored
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.
I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.
The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.
Here is the Java class defined in Oracle:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS import java.io.*; public class OSCommand{ public static void Run(){ try {
[code]....
And here is the procedure that calls the Java class:
create or replace procedure run_os_command as language java name 'OSCommand.Run()';
When working with SQL Server it's possible to execute several sql statements in one command of the DataAdapter in ADO.Net Now I try the same thing with an oracle database and recieve error ORA-00911: invalid character.
When remove ;character from query it again gives error- ORA-00933: SQL command not properly ended.
My query is like this-
update activity set activityname='Route Survey' where activityid=1; SELECT * FROM activity where activityid=1
I'm trying to install the Grid Infrastucture + Oracle db 11.2.0.3 on a Solaris 11. I'm used to work with Red-hat, so i don't remember in having such a problem. The issue is the following, when trying to run "runInstaller" as oracle user:
Checking Temp space: must be greater than 180 MB. Actual 1527 MB Passed Checking swap space: must be greater than 150 MB. Actual 2104 MB Passed Checking monitor: must be configured to display at least 256 colors >>> Could not execute auto check for display colors using command /usr/openwin/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before continuing with the installation,Continue? (y/n) [n] n
User Selected: NoDoing some researches, would be to install the SUNWxwplt package.i have installed all the required packages:
root@sol11:/mnt/sf_Compartilhamentos# pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibms SUNWpool SUNWpoolr SUNWsprot SUNWtoo SUNWlibm SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw SUNWxwplt system SUNWarc Lint Libraries (usr) system SUNWbtool CCS tools bundled with SunOS system SUNWfont-xorg-core X.Org Foundation X11 core fonts system SUNWfont-xorg-iso8859-1 X.Org Foundation X11 iso8859-1 fonts [code]....
xhost: unable to open display "192.168.0.20:0.0"I think i made all the necessary configurations (?).
When I execute multipath -ll command that time display only 3 path (orafra2,oradata2 and oradata1), not display other 2 path (orafra1 and data1). I have configure 5 path in /etc/multipath.conf file. What is the reason behind it.
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5 23 asdc 1 Location USA NM ABQ Four Seasons 87106 23 asdc 1 Stats 2300 91.7 8.2 85432 23 asdc 1 Audit 1996 June 17 1200 65 affc 2 Location USA TX AUS Hilton 92305 65 affc 2 Stats 5510 42.7 46 9999 65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime 23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200 65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
I want to set both nls_language and nls_date_language, and set them as different languages. fnd_global.set_nls_context() doesn't work. So I think maybe I can use the EXECUTE IMMEDIATE, and add them together into one statement.
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
I have an table 'tbcontrol' where for each row I can have one or more sql statements (DML or DDL) in an column.
Example:
task | sql_scripts 1 | create table t1 (c1 number); insert into t1 (c1) values 100; 2 | create table t2 (c2 number); alter table t1 modify c1 not null default 0; 3 | alter table t2 modify c2 not null default 0; alter table t1 add c10 varchar2(10); create table t3 (c3 number not null default 0;
I need to create a procedure where I can pass an task number to execute the sql statements which are in the column 'sql_scripts'. Something like this:
create or replace sp_run_scripts (v_task number) is v_scripts varchar2(2000); begin select sql_scripts into v_scripts from tbcontrol where task = v_task execute immediate v_scripts; end;
But, here I'll have some problems:
- The script will have semicolons (EXECUTE IMMEDIATE doesn't support); - If I put a begin/end like a PL/SQL script, I can only run DML statements because DDL statements have implicit commit. - I can't use a cursor for execute each statement at a time because the scripts have multiples-rows. Even if I search for semicolons to 'separate' the scripts in the cursor (using SUBSTR and INSTR functions), I'll have problems with the semicolons between '' (quotations marks).
Desired Output: ====== First Second Third 11 21 31
I have tried the below query SELECT DECODE (name,'Nexus', parameter) First, DECODE (name, 'GPlay', parameter) Second, DECODE (name, 'Demo', parameter) Third FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third 11 <Empty> <empty> <empty> 21 <empty?> <empty?> <empty?> 31
Is there any way to get the output in single line.
Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:
1) Select top 10 rows whose "Status = NEW'
2) Update the selected top 10 rows with "Status = PROCESSING"
3) Return those top 10 rows.
Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?
I am new to SQL*loader and I would like to know what is the maximum number of ROWS that can be loaded in a Conventional bind array while specifying the command line parameter.
tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.
Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.
CREATE TABLE Person_Lang ( Person_ID NUMBER NOT NULL, Language_ID NUMBER NOT NULL, Contact_Name VARCHAR2(255 CHAR), Main_Phone VARCHAR2(255 CHAR), Secondary_Phone VARCHAR2(255 CHAR),
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query DECLARE TYPE all_dest IS TABLE OF NUMBER; destIds all_dest; BEGIN SELECT dest_id from sb_packet WHERE src_id = 32; RETURNING dest_id bulk collect into destIds; END;
in eache record we are receiving information from differente city, we pretend to get an output where we have a row for each city (delimited by comma) that we have in column CITY
Input data
SELECT '1001001' as CLIENT_ID, 'LONDON, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1022201' as CLIENT_ID, 'MADRID, OSLO' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033001' as CLIENT_ID, 'PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL UNION SELECT '1033004' as CLIENT_ID, 'MADRID, OSLO, PARIS' as CITY, TO_DATE('20101105', 'YYYYMMDD') as DT_REG FROM DUAL
Output expected CLIENT_IDDT_REGCITY 100100105/11/2010 LONDON 100100105/11/2010 PARIS 102220105/11/2010 MADRID
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)= (SELECT B.id, B.fullname, MIN(B.nm_rankval) FROM B, A WHERE A.id = B.id AND A.name <> B.fullname AND B.nametyp = 'ON' GROUP BY B.id, B.fullname) ;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false) col name start_date ; type=date; format=dd-mmm-yy col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ') insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ') insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ') insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE 12701270001W165IR HO BV 12701270001W1332IR BV 12701270001W1367RE HO SC BV 12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
