Oracle SQL Query from EXCEL 2007 with prompt

Advertisement

Hello,
I have many excel reports where I am pulling information from our Oracle 9 db through Excel using the following method:
http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/
http://blog.mclaughlinsoftware.com/2009/11/30/sql-query-in-excel-2007/
However, I am having trouble when I try the following query due to the prompt:
SELECT
IM.ITEM_GROUP,
IM.ITEM,
IM.DESCRIPTION
FROM
LAWSON.ITEMMAST IM
WHERE
IM.ITEM = '&ITEM';
Does any one know how I can connect a prompt to an excel cell and then pass the query on to Oracle, or have a PL SQL prompt work from Excel?
Thanks,
Ben

Advertisement

The 'prompt' as you call it is a sqlplus feature, so does not belong to the SQL language
You would need to write a stored procedure returning a resultset.
create or replace procedure foo(rc in out sys_refcursor, p_item) as
begin
open rc for
'SELECT
IM.ITEM_GROUP,
IM.ITEM,
IM.DESCRIPTION
FROM
LAWSON.ITEMMAST IM
WHERE
IM.ITEM = '||p_item;
end;
and call that using ODBC or asp.net
Obviously this is profusely documented.
Sybrand Bakker
Senior Oracle DBA

October 11, 2015

Oracle SQL Query from EXCEL 2007 with prompt

Hello, I have many excel reports where I am pulling information from our Oracle 9 db through Excel using the following method: http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/ http://blog.mclaughlinsoftware.com/

Sync mail from Outloock 2007 with phone by PC Suit...

It is posible to sync my mail from Outloock 2007 with my phone Nokia 6300 by PC Suite? At the moment I can only sync notes, contacts and tsks.iTunes does not support syncing email account mailboxes and messages with an email client on your computer.

Exporting Oracle 10g reports into Excel 2007

Does anybody knows if there exits compatibility with the export function to Excel 2007? Some users will be installing this Excel version and i don't know if the formatting and displaying will be similar as using previous versions. They use the export

Export SQL query from link or button

I want to list several 'canned' SQL queries on one page as buttons or links; and have the result of selection be an immediate extract/download to excel of the data. How can I best do this? I suppose I could create separate hidden regions with their o

Using an Oracle SQL Function from JPA/TopLink

How do I get the return value from a simple (one string input parameter, returns a string) Oracle SQL Function? Thanks!If you mean calling a stored function in Oracle, you might try something like:     ValueReadQuery vrq = new ValueReadQuery();     S

Passing variables to SQL query from checkboxes

Hello, I have a cursor which gets different ids from an id table. The cursor is something like this: CURSOR id IS          SELECT distinct id_ FROM I                   order by id_;I then iterated through the cursor to made checkboxes to choose multi

ORA-12154 error when trying to connect to Oracle 11g DB from Excel 2010

All, I am trying to set up Excel 2010 64-bit to connect to an Oracle 11g database, but am having some trouble due to an "ORA-12154 could not resolve the connect identifier specified" error. I am able to TNSping the SID name, as well as connect t

Oracle sql query works

Can any body tell , how the sql select statement will works ? how it will come across the 3 phases 1.pharse 2.execute and 3. fetch what is the parameter in oracle 9i and 10g for compress the dumpfile in export and import?Hi, Welcome to forums.. !! Ca

Mysql equivalent oracle sql query is required

Hi all, please help me convert mysql quyery into sql query. select u.username, t.name as fullname, p.id as project_id, p.name as project,                          (select count(a.resource_id) from pd_resource_task_alloc as a left OUTER JOIN pd_resour

Photos not showing up when I make PDFs from Excel 2007 files

I make lists for my newspaper. I am working from home today where I use Excel 2007 and Adobe Acrobat Pro 9. I had to tweak my home Adobe settings to mimic ones I use at work for our newspaper's printer, but other than that, I am doing what I normally

It is possible to execute a SQL query from OMBPlus?

We have some simple SQL scripts we'd like to be able to incorporate into our existing TCL scripts, instead of having to run them outside of OMBPlus in sqlplus. Is is possible to run a SQL SELECT query from OMBPlus and/or TCL? And get a result set bac

App-V Reporting: SQL query for Excel Pivot Table to display App Usage (H/M/S)

I recently posted this in the Gallery.  If anyone has any modifications or enhancements please feel free to contribute!  One thing that I'd like to be able to add to it is the ability to only display information for applications/packages that are sti

Oracle sql query execute in the apex

hi: my requirement is, in region i have one text area item and query button. in this text area i am typing the any sql query and when i am pressing the query button it should display the query output report.. pls give solution fast as soon as possibl

Replacing a set of characters from a string in oracle sql query

I want to replace a set of characters ( ~    !     @    #     $     %     ^     *     /     \     +    :    ;    |     <     >     ?    _  ,) from a STRING in sql select 'TESTING ! ABC 123 #' FROM DUAL; What is the best way to do it? Please provide

Change the way information is displayed from SQL query in Excel

Hi, I'm trying to set up a spreadsheet that displays information directly from our SQL database using the Microsoft Query Wizard.  I have a very basic understanding on how to do this, so I can create a spreadsheet that gives me most of the informatio

SQL query alias names errors with dynamic lists

Hullo, Problem: - Made a query form book database. Have authors in separate table with unique ID's referring to them on book table. - Query fetches author name in "Surname, Firstname initial.Secondfirstname initial" format, --> like "Buk

Problems opening PDFs from SharePoint 2007 with Acrobat X Pro

We are currently migrating to Windows 7 x86, Acrobat X Pro, and Office Pro Plus 2010 SP1. Our old setup is Windows XP, Acrobat 8 Pro, and Office 2007. We are using SharePoint 2007. On our older machines, we are able to open and edit PDFs without any

Oracle Spatial Query from SQLPLUS deteriorates in performance

Hi, I am running an sql-procedure, which does spatial queries. When I run this procedure (from SQLPLUS) for the first time it takes around 45 sec to complete, but when I run the same procedure for the second time it takes 65 seconds and the performan

Oracle  deleting rows from tables starting with the name PQ

hai friends we are given access rights to delete only tables starting with PQ. HAVING PQ_NUM as primary key for all the PQ tables. totally we have 6 tables. PQ_01,PQ_02, PQ_03,PQ_04,PQ_05,PQ_06. ALL This tables will have one primary key. for example

Prevent user defined query from being activated with the CopyTo function

Hi, I have a user defined query defined in one of the quantity field (in the grid) in the goods receipt PO window. The query updates the quantity depending on the number of another line field. The query works great, but I dont want it to be activated