plsql

PL/SQL varchar(10) to varchar(9)

How would I write a loop that has a select in the “in” clause of the loop that selects a col of type varchar(10), but then inserts those values in a col that wants them to be varchar(9)? Basically I’m trying to “typecast” from one precision to another, if that makes any sense. Example: FOR V_TEN IN (SELECT THIS_IS_VARCHAR_TEN FROM TABLE...

Problem binding with OPEN-FOR-USING statement

I have a cursor in an ORACLE function that I am dynamically generating using the arguments of the function. I define the text of the cursor and in certain cases I include the following statement: sql_stmt := sql_stmt || 'AND MOD ( LOG_ID, :logsetmax ) = :logset '; I open the cursor and pass arguments in using the ORACLE OPEN-FO...

PLSQL not all variables bound

Hi all, i keep getting the following errror, 'ORA-01008: not all variables bound', im guessign its all based on my pPostcode param but im not sure. I am a beginner the the whole PLSQL secne and any help would be greatly apriciated here is my procedure: procedure all_customer_postcode(pPostcode in carer.postcode%type ...

What does the colon sign ":" do in a SQL query?

What does ":" stand for in a query ? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for ":"? Chosen Answer: Chose the answer for the link and the upvotes. But also check Jeffrey Kemp's answer ...

Oracle build order and PL/SQL package dependencies

I'm trying to build up a list of PL/SQL package dependencies so that I can help set up an automated build script for my packages to run on the test server. Is there a way to start with a single package (a "root" package identified by name, ideally) and then find all of the dependencies, and the order they must be compiled in? Dependenci...

What is the general view on using Staging tables? We use them a lot for Order imports from External vendors.

hi Gurus, We have been having a lot of internal debate regarding staging tables. Some are view staging tables as archaic and will prevent the ability to build re-usable services, etc. It is also being communicated that these will prohibit the business to grow and handle expanded business channels. I am not necessarily for or against ...

Improving the performance of keeping the three recent records of each account query

Hi, I've a table in an Oracle (10g XE) database, and I'm going to clean it up and only keep the three recent records of each account. Here is what I'm doing right now: CREATE TABLE ACCOUNT_TRANSACTION_TMP NOLOGGING AS SELECT * FROM ACCOUNT_TRANSACTION WHERE 1=2; DECLARE CURSOR mbsacc_cur (account_id_var account_transaction.account...

PL/SQL Using variables in an Explicit Cursor statement

I'm declaring an explicit cursor, however, I need to order the sql differently depending on user interaction. The column to order by is passed to the procedure as a parameter, however, I'm having difficulty incorporating it into the sql for the cursor (as near as I can tell, it is interpreting the variable name as the name of the column...

Updating an individual procedure in an Oracle package

Using Oracle 10g. I'm writing a script to apply the delta changes made on our development system to our stage system. Is there a way to modify or insert an individual procedure within a package without including the entire package contents in my script? I think the answer is no, but I wanted to be sure because it's going to be pretty ...

UTL_SMTP.Write_Data not sending any text if a colon is included

I have created a Oracle function to send an email using the UTL_SMTP package. When using the methods Write_Data or Data, if my text does not contain a colon, the sent email will contain the inputted text in the email's body. However, if the text contains a colon, the text is not included in the email. Every example of this i've seen o...

How do you parse a simple XML snippet in Oracle PL/SQL and load it in a global temp table?

In SQL Server it is easy to parse a vachar variable that contains a simple XML snippet constructed with attributes and load it into a temp table - see example below: declare @UpdateXML VARCHAR(8000) set @UpdateXML='<ArrayOfRecords> <Record Field01="130" Field02="1700" Field03="C" /> <Record Field01="131" Field02="1701" Field03="C" ...

Oracle PL/SQL: referencing to a column name in inner joined query

I have the following SQL statement: SELECT * FROM cars car LEFT JOIN (SELECT * FROM cars auto LEFT JOIN steeringwheels sw ON auto.steeringwheelid = sw.ID WHERE material = 'leather') innertable ON innertable.ID = car.ID LE...

How to obtain Local IP Address of PC within Oracel ApEx

Hoping this is possible and someone can assist but I need a means of obtaining the local IP Address (Windows box listed in ipconfig command info) from within Oracle ApEx. Is this possible as I have tried both owa_util.get_cgi_env('REMOTE_ADDR') as well as sys_context( 'userenv', 'ip_address' ) Just can't seem to get my local pc ip addr...

How do you get PL/SQL to parse XML attributes instead of XML elements in Oracle?

Please see PL/SQL snippets below: create table t23 ( field01 number, field02 number, field03 char(1) ); Example Snippet #1 declare x varchar2(2000) := ' <ArrayOfRecords> <Record> <Field01>130</Field01> <Field02>1700</Field02> <Field03>C</Field03> </Record> <Record> <Field01>131</Field01> <Field02>1701</Field02> <...

Executing a dynamic sql statement into a SYS_REFCURSOR

Hi all, is it possible to execute a dynamic piece of sql within plsql and return the results into a sys_refcursor? I have pasted my attempt soo far, but dosnt seam to be working, this is the error im getting throught my java app ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512...

literal string works but variables take forever.

I have a query that works when I have fixed values. ie: select count(*) from address a where a.primary_name like upper('cambourne court') and a.secondary_name like upper('flat 9'); However replace the upper('flat 9') with a variable which is second_name:=upper('flat 9') and the search now returns all 111 addresses in '...

Calling a PHP Function within Oracle PL/SQL

Hi, I am using Oracle Application Express and I was wondering whether it's possible to call php inside a pl/sql page process? Can I use htp.p or htp.prn in pl/sql? For example, let's say I have a php function on the server that returns the remote_addr of the local machine - I basically would like to capture this function value and add...

PHP/Oracle, returning inserted id's from Merge Into

I am currently working on a PHP project with an Oracle database. To update a table, the php code I'm working with uses a SQL "MERGE INTO" method to loop through a table and see if values for multiple records exist in another table. If they don't exist yet, the values are inserted into my table. If the values already exist, nothing hap...

Crystal/Oracle parameterized query to use variable

I currently have a Database Command object in my Crystal Report that looks something like SELECT * FROM table WHERE field = {?EndDate} I want to change it so it looks more like IF {?EndDate} = DATE '1900-01-01' MyVariable = ADD_MONTHS(LAST_DAY(SYSDATE), -1) ELSE MyVariable = {?EndDate} SELECT * FROM table W...

Using %rowtype with a join and duplicate columns

Given (ignore the lack of primary keys, foreign keys, etc - this isn't about table design and is just an example): Order: ---------- ID NUMBER; VENDOR NUMBER; PART NUMBER; Parts: ------------ ID NUMBER; VENDOR NUMBER; DESCRIPTION VARCHAR2(1000 CHAR); cursor c1 is select * from order o left join parts p on o.part = p.id; c_row c1%rowt...