Inline pragma before cursor




I have a stored proc that calls a user-defined function "fun" like this (greatly simplified):

Code: procedure foo(p_cursor out ref cursor) begin open p_cursor for select a.* from a where fun(a.b) = 1 end foo;

I want to call fun with "pragma inline(fun, 'YES')".
According to the Oracle documentation, the inline pragma will affect only the following statements if placed right before them:

Code: Assignment CASE EXIT-WHEN Call CONTINUE-WHEN LOOP Conditional EXECUTE IMMEDIATE RETURN

So, in my case, where should I put the pragma I do have a call, but it's wrapped in the SQL within a cursor definition.
What is the right way (if this is feasible at all)

Posted On: Monday 31st of December 2012 01:12:25 AM Total Views:  252
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




VPD/FGAC on Three Tier Environment

Has anyone tried or implemented VPD/FGAC on a Three tier environment using a non-oracle application server(Weblogic) Please give me or point me to some notes or documentations where I can get more information on how to implement this.
VIEWS ON THIS POST

34

Posted on:

Wednesday 7th November 2012
View Replies!

Question for All

1. As I have understood from the book a Materialized View stores the data at the time when it is created and not at the time it is querried like normal view.I want to know then what is the use of creating MV when this can also be done with the help of creating table with select statement having joins.I know there has to be advantage thats why Oracle has created it. 2. What is the need of multiple datafile for a single tablespace.We can just set it to auto extend and take regular backups. Can anyone help me.
VIEWS ON THIS POST

74

Posted on:

Wednesday 7th November 2012
View Replies!

Oracle-Windows XP

I have oracle 8.1.7 as per oracle it is not certified for XP. Does any body has install oracle 8i on XP Is there any work around to install it. We had a linux which is not certified by oracle but still we able to install it. Will any body share there experience ... Windows XP home edition.
VIEWS ON THIS POST

38

Posted on:

Wednesday 7th November 2012
View Replies!

top to bottom..

hi, When i try to delete any user it gives me this error.. VBO-5530 User cannot be dropped.. ORA-00604: error occurred at ORA-00942: table or view does not exist what to do\t..
VIEWS ON THIS POST

81

Posted on:

Wednesday 7th November 2012
View Replies!

Physical standby experiences

Hi Folks What kind of problems have you faced while managing a standby database can you share it here. regards Hrishy
VIEWS ON THIS POST

205

Posted on:

Wednesday 7th November 2012
View Replies!

Some RMAN Q's!

SOME RMAN Q'S:- 1. Backup of RMAN catalog: Using OS backup or another RMAN catalog Read that if using OS Backup, BMR not possible in case of loss of catalog! 2. Channels : Auto or Manual allocation - which is best - is it hardware/MML&MMS vendor dependent 3. Any BP's on Rman..links etc.
VIEWS ON THIS POST

143

Posted on:

Wednesday 7th November 2012
View Replies!

Run catbundle.sql with PSU 11.2.0.2.1 on database created with dbca

Hi Guys! I have to install the PSU 11.2.0.2.1 on linux x86_64. There are no database already installed and i' ll create them with a dbca template generated with an oracle home version 11.2.0.1. The note of the read me of the PSU said : You must execute the steps in Section 3.3.2 (that says to execute catbundle.sql), "Loading Modified SQL Files into the Database" for any new database only if it was created by any of the following methods: Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing) Using a script that was created by DBCA that creates a database from a sample database Is not perfectly clear if i have to execute the catbundle script if i' ll create database using dbca templates...any suggestion
VIEWS ON THIS POST

208

Posted on:

Wednesday 7th November 2012
View Replies!

RAC on HP

Has anyone implemented Oracle 9.2 RAC on HP, We'd like to setup a test environment, I've read documents on RAC and somehow more questions came up than answer. Can anyone shared their experiences on this topic Ed
VIEWS ON THIS POST

35

Posted on:

Wednesday 7th November 2012
View Replies!

quicky

I would like to create a script to update each row that has a "NONAME" value in the LNAME field. I would like to modify this value to be blank. The name of the table is MASTNAME and it is an Oracle 8.1.6 database. Any suggestions Should I use a cursor Could someone provide me an example
VIEWS ON THIS POST

102

Posted on:

Wednesday 7th November 2012
View Replies!

QMN0 increasing memory usage

I notice that the QMN0 process has increased memory usage from 70MB to 90MB in 2 weeks time and keeps growing day by day. This process contributes to the increase of total PGA memory usage. I'm not using the feature of advanced queuing. Please enlighten me. Tks.
VIEWS ON THIS POST

103

Posted on:

Wednesday 7th November 2012
View Replies!

How to call an oracle function with a user defined type as parameter

OracleCommand cmd2 = new OracleCommand(); cmd2.Connection = conn; cmd2.CommandText = ""; cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("org_id", \t); cmd2.Parameters.AddWithValue("date_from", 26-11-2008 16:56:29); cmd2.Parameters.AddWithValue("date_to", 26-11-2008 16:56:29); cmd2.Parameters.AddWithValue("id", 10); cmd2.Parameters.Add("crs_kzlpers", OracleType.Cursor).Direction = ParameterDirection.Output; etc.. The \t parameter has a should look something like this: "organization_id(1,2,3,4,5)" I tried: cmd.Parameters.Add("id", OracleType.\t).Value = 10); before, but I got stuck at the \t cause the user defined type is not known there... Does anyone have a solution for this
VIEWS ON THIS POST

258

Posted on:

Wednesday 7th November 2012
View Replies!

Complete Newb

I admit it up front, I'm a complete newb to ASP. So that being said, one of two things would be REALLY helpful to this little newb right now. I have a site that I completely coded in PHP, so I'm not a stranger to MySQL or programming. I am however, completely at a loss with ASP in this one area. I managed to get my site look down and fully going with master pages and all, even got my connection working to MySQL and posting my news from my database. I have a book on order with B&N about ASP and MySQL, but it's going to take about 4 days for it to arrive (as they don't have any in stock atm). What I'm trying to do, is pass a simple variable into the call to MySQL to give me a listing of zip codes. Here's what I have so far: this.CatSelected.Text=Request.QueryString["desc"]; \t\t\t\tthis.ZIP.Text=Request.QueryString["ZIP"]; \t\t\t\tStringconnectionString=System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ccdir"].ConnectionString; \t\t\t\tOdbcConnectioncon=newOdbcConnection(connectionString); \t\t\t\ttry { con.Open(); using(OdbcCommandcmd=newOdbcCommand("select*fromziplinkswhereorigzip="ZIP.Text,con)) \t\t\t\t\t\t\t\tgetZips.DataSource=cmd.ExecuteReader( \t\t\t\t\t\t\t\t\tCommandBehavior.CloseConnection| \t\t\t\t\t\t\t\t\tCommandBehavior.SingleResult); \t\t\t\t\t\tgetZips.DataBind(); \t\t\t\t} \t\t\t\tcatch(Exceptionerr) \t\t\t\t{ \t\t\t\t\t\tResponse.Write("Errorreadingdatabase."+err.Message); \t\t\t\t} \t\t\t\tfinally { con.Close(); } Yes, everything is closed up nicely btw ;) And it is receiving the variables I'm passing over, so it is getting "desc" and "ZIP". My problem is I have zip familiarity with how to pass that ZIP.Text as a legit variable for MySQL to read. So if anyone can point me to a good resource that I can read while I wait for my book, would be a huge help, and if you can even point me in right direction of how to do that, I'm forever grateful. Needless to say, there's not much on the web (after spending about 3 hours Googling for answers) in regards to ASP and MySQL. And before anyone recommends MS SQL, my current database is about 10megs, I'm not up for converting it :)
VIEWS ON THIS POST

37

Posted on:

Wednesday 7th November 2012
View Replies!

sqldatasource and mysql

How do you connect a sqldatasource and mysql DB with the wizard. i looked at ODBC connection but found no driver for mysql
VIEWS ON THIS POST

274

Posted on:

Wednesday 7th November 2012
View Replies!

Problem with DB2 parameter query

people,
VIEWS ON THIS POST

133

Posted on:

Wednesday 7th November 2012
View Replies!

Using ASP.NET login controls with MySQL instead of SQL

Well I am trying to figure out how to get my MySQL user table to be setup in ASP.NET so that I can login using the login controls that ASP.NET provides. What I have done is made the login crap already, and then I added a user in the SQL database just so I could test if it worked. Well yes the login worked and re-directed to the correct page. Now what I want to do is instead of using the SQL database I want to use the MySQL database, because it already has all of my users and site info on there. Is there a way that I could change the default login controls to use MySQL instead of SQL Or is there no such luck for me\t
VIEWS ON THIS POST

221

Posted on:

Wednesday 7th November 2012
View Replies!

Need help with Insert Parameter Query

I am pretty new to Asp.net and mySql and have not been able to get a insert statement to run. Can anyone tell me what I am doing wrong\t I can connect to read the records, but cannot insert a new record. \t\t\t\tDim conCalendar As OdbcConnection \t\t\t Dim cmdAdd As OdbcCommand \t\t\t Dim strInsert As String \t\t\t\t\t\t\t \t\t\t conCalendar = New OdbcConnection("DSN=mydsn") \t\t\t strInsert = "Insert into tblcalendar(fldKey, fldDate, fldEventName, fldStartTime, fldEndTime, fldGroup, fldDetails) Values(,,,,,,)" \t\t\t cmdAdd = New OdbcCommand(strInsert, conCalendar) \t\t\t \t\t\t cmdAdd.Parameters.AddWithValue("@Key", Now()) \t\t\t cmdAdd.Parameters.AddWithValue("@Date", AddNew.EventDate) \t\t\t cmdAdd.Parameters.AddWithValue("@EventName", AddNew.EventName) \t\t\t cmdAdd.Parameters.AddWithValue("@StartTime", AddNew.StartTime) \t\t\t cmdAdd.Parameters.AddWithValue("@EndTime", AddNew.EndTime) \t\t\t cmdAdd.Parameters.AddWithValue("@Group", AddNew.Group) \t\t\t cmdAdd.Parameters.AddWithValue("@Details", AddNew.Details) \t\t\t \t\t\t conCalendar.Open() \t\t\t cmdAdd.ExecuteNonQuery() \t\t\t conCalendar.Close()
VIEWS ON THIS POST

199

Posted on:

Wednesday 7th November 2012
View Replies!

need help: Export large data from table to excel

I have a large table(45 columns and morn than 80k rows), how can I export it to Excel file I try codes from http://www.codeproject.com/KB/aspnet/Export_large_data_to_xl.aspx, but got data parse error...
VIEWS ON THIS POST

141

Posted on:

Wednesday 7th November 2012
View Replies!

oracle has 'DESCRIBE' to get all the details of the table like wise does t/sql has any thing.

I would like to know if t/sql has any thing like DESCRIBE which gives all the table info along with the datatype, precision etc. Belated Happy New year to all,
VIEWS ON THIS POST

185

Posted on:

Wednesday 7th November 2012
View Replies!

MS SQL and ASP.NET connection?

hi, m trying to connect MS SQL 2000 from VS.NET 2003. but couldn't find any guide on the net for the beginners. Can anyway give me sample test connection code. what i have done so far is: 1. Run the VS.NET 2003 program 2. Selected Visual Basic Projects, from there ASP.NET Web applications (it gave me .aspx file and others) 3. Now, i don't know how to connect this ASPX code with Database i have. 4. Finally, i want to check whether it is connected or not by printing "CONNECTED" or Not Connected
VIEWS ON THIS POST

131

Posted on:

Wednesday 7th November 2012
View Replies!

How to connect with ingres database?

ingres is a product of ca(computer associates) can we connect it via vb.net if yes then please explain me in detail... or give me some relative hint.
VIEWS ON THIS POST

147

Posted on:

Wednesday 7th November 2012
View Replies!

Reduce Loading time

hi, i'm facing one problem, i have to display the table content in datagridasp.netpage, that particular table contain more than 8000 record, due to that loading time is high in local itself, if i deploy in remote means it take more than this time to load this page, how can i overcome this problem,
VIEWS ON THIS POST

65

Posted on:

Wednesday 7th November 2012
View Replies!

XML data load using SQL*Loader

Hi all, Can any one please suggest how to load XML data into a table using SQL*LOADER utility. Scenario is: I am getting a dat file in a flat file, where it contains XML data for one column. While I am trying to load that dat file using SQL Loader, its giving me "the field in data file exceeds the maximum length of the column "xyz". But actually the size of the column "xyz" is varchar2(4000) and the datalength what I am getting is less than 3000 bytes.
VIEWS ON THIS POST

93

Posted on:

Sunday 11th November 2012
View Replies!

Who can help me?

When I install Oracle 8.17 on Linux 7.2,these errors occursed :Error in invoking target relink of makefile /home/oracle/data/app/oracle/product/8.1.7/prrecomp/lib/ins_precomp.mk. What can I do
VIEWS ON THIS POST

155

Posted on:

Monday 12th November 2012
View Replies!

SQL Query w/ Joins

I need to see if i can create a singe query to give me the result set that i need, but can't get there. This is what i tried first, but could use any help someone is willing to offer.
VIEWS ON THIS POST

37

Posted on:

Thursday 15th November 2012
View Replies!

release date for oracle7.3

Can some one please tell me the release date for Oracle7.3 on any platform - NT/Solaris...Unix\t
VIEWS ON THIS POST

157

Posted on:

Friday 16th November 2012
View Replies!

Ref:(Snapshots)

Where can I see the tablespace information of a snapshots, I mean what is the object name which tells the tablespace information of the snapshots. Is it possible to export the snapshots to another database through export and import method.
VIEWS ON THIS POST

56

Posted on:

Friday 16th November 2012
View Replies!

Oracle9.2.0.1 Problem

Hi! I'm new to this forum and this is my first Q .. I installed Oracle9.2 on WinXP. I started Listener and DB services in Windows Services. Their status is STARTED. But when i start Listener manually, i get the following errors. ------------------- LSNRCTL> start listener Starting tnslsnr: please wait... Service OracleOraHome92TNSListener already running. TNS-12560: TNSrotocol adapter error TNS-00530: Protocol adapter error 32-bit Windows Error: 1056: Unknown error ---------------------------- # LISTENER.ORA Network Configuration File: J:\oracle\ora92\network\admin\listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1521)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = J:\Oracle\ora92) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = COMPANY) (ORACLE_HOME = J:\Oracle\ora92) (SID_NAME = COMPANY) ) ) ------------------------------------------------------ # TNSNAMES.ORA Network Configuration File: J:\oracle\ora92\network\admin\tnsnames.ora # Generated by Oracle configuration tools. INST1_HTTP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1534)) ) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = MODOSE) (PRESENTATION = http://HRService) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) COMPANY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = COMPANY) ) ) ---------------------------------------- # SQLNET.ORA Network Configuration File: J:\oracle\ora92\network\admin\sqlnet.ora # Generated by Oracle configuration tools. SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) ---------------------------- i attached above the config. files of my Oracle 9.2.0.1 I get TNS - 12560 error when i try to login using scott/tiger in SQLPlus. I searched the forums and internet and couldn't find any solution.. Can anyone help me with this error or anyone guide me to a clean installation on XP for Oracle9.2.. I've Java1.5, Eclipse 3.3, Tomcat 6.0 also installed.
VIEWS ON THIS POST

109

Posted on:

Friday 16th November 2012
View Replies!

Remove quotes from hard-coded value sql syntax

Is it possible to remove the single quotes from the below code snippet, and allow the below sql select statement to NOTinclude the single quotes
VIEWS ON THIS POST

99

Posted on:

Wednesday 21st November 2012
View Replies!

Integrate ASP.NET with Sage Line 50

Hi , Would anyone know if there is a way to integrate the SAGE database in to a ASP app. I need to make calls to pickup stock levels etc. I have seen a third party tool that that is: A) to expensive and B) too much unwanted functionality. Any ideas
VIEWS ON THIS POST

134

Posted on:

Wednesday 21st November 2012
View Replies!

Assembly registering with C# for OracleClient.

First of all I am not using Visual Studio.Net. Everything is notepad and command line. I am totally new to this so please excuse my stupidity! I have stolen some code out of a book to connect to an Oracle database but have amended it to use another provider as my Oracle client software is too advanced to use the OleDb connection that the book used. I have therefore tried to amend the code to use the Microsoft Data Provider for Oracle but keep receiving this error message when I try and compile it: error CS0234: The type or namespace 'OracleClient' does not exist in the class or namespace 'System.Data' (are you missing an assembly reference). I dont know what or how to include one. Can someone show me in real simple terms! I am not using Visual Studio so need to know how to do this manually. Here is my code (the code that the book suggested has been commented out and then I've added my stuff - sorry about the mess. Thank you): /* LtOleDbConnectionOracle.csillustrateshowtousean OleDbConnectionobjecttoconnecttoanOracledatabase */ usingSystem; usingSystem.Data; usingSystem.Data.OracleClient; classLtOleDbConnectionOracle { publicstaticvoidMain() { //formulateastringcontainingthedetailsofthe //databaseconnection stringconnectionString= "datasource=netservicename;userid=scott;password=tiger"; //createanOleDbConnectionobjecttoconnecttothe //database,passingtheconnectionstringtotheconstructor //OleDbConnectionmyOleDbConnection= //newOleDbConnection(connectionString); OracleConnectionoOracleConn=newOracleConnection(); oOracleConn.ConnectionString="DataSource=netservicename;userid=scott;password=tiger"; //createanOleDbCommandobject OleDbCommandmyOleDbCommand=myOleDbConnection.CreateCommand(); //settheCommandTextpropertyoftheOleDbCommandobjectto //aSQLSELECTstatementthatretrievesarowfromthesec_user_groupstable myOleDbCommand.CommandText= "SELECTuser_code,group_name"+ "FROMsec_user_groups"+ "WHEREuser_code='SJJ58'"; //openthedatabaseconnectionusingthe //Open()methodoftheSqlConnectionobject //myOleDbConnection.Open(); oOracleConn.Open(); //createanOleDbDataReaderobjectandcalltheExecuteReader() //methodoftheOleDbCommandobjecttoruntheSELECTstatement OleDbDataReadermyOleDbDataReader=myOleDbCommand.ExecuteReader(); //readtherowfromtheOleDbDataReaderobjectusing //theRead()method myOleDbDataReader.Read(); //displaythecolumnvalues Console.WriteLine("myOleDbDataReader[\"user_code\"]="+ myOleDbDataReader["empno"]); Console.WriteLine("myOleDbDataReader[\"group_name\"]="+ myOleDbDataReader["ename"]); //closetheOleDbDataReaderobjectusingtheClose()method myOleDbDataReader.Close(); //closetheOleDbConnectionobjectusingtheClose()method //myOleDbConnection.Close(); oOracleConn.Close(); } }
VIEWS ON THIS POST

901

Posted on:

Wednesday 21st November 2012
View Replies!