hi,
i am trying to create a dynamic sql to compare the old and new values in a table by usind a procedure as follows
The g_o_rec and g_n_rec are global variables declared at the package level
When I execute this, it always goes into exeception with the following error
sqlerrm ORA-00904: "G_N_REC"."PHON E": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."CONT ACT_PHONE": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."WEBP AGE_URL": invalid identifier
sqlerrm ORA-00904: "G_N_REC"."FAX" : invalid identifier
Any help is appreciated here.
Thanks
---------------------------------------
Procedure comp_old_new_ve ndor(g_o_rec in out dw_new.dw_vendo r%rowtype,
g_n_rec in dw_new.dw_vendo r%rowtype, l_changed in out varchar2)
is
l_compare varchar2(4000);
l_data_type varchar2(50);
l_result number;
l_new_val varchar2(2000);
begin
for i in ( select data_type, 'g_o_rec.'|| column_name 'g_n_rec.'||col umn_name l_col2, column_name from user_tab_column s where table_name = 'DW_VENDOR' and column_name NOT IN ('LOADED', 'VENDOR_ID')
)
loop
begin
execute immediate('sele ct nvl(max(1),0) from dual where ' || i.l_col1 || ' <> ' || i.l_col2 ) into l_result;
dbms_output.put _line('l_result ,,,,' || l_result);
exception
when others then
-- null;
dbms_output.put _line('sqlerrm ' || sqlerrm);
end;
end loop;
End comp_old_new_ve ndor;
3 4295
can you give us the DDL for the table dw_vender?
also i wouldn't have a variable at the package level with the same name as a parameter for a procedure within the package... needless confusion.
Thanks for the response
Initially the procedure call was
Procedure comp_old_new_ve ndor(l_o_rec in out dw_new.dw_vendo r%rowtype,
l_n_rec in dw_new.dw_vendo r%rowtype, l_changed in out varchar2)
begin
g_o_rec := l_o_rec;
g_n_rec := l_n_rec;
...
....
All the other statements are the same as in my initial posting
End comp_old_new_ve ndor;
This didn't work and hence tried it as in my intial posting
Here is the ddl for the dw_vendor.
create table dw_vendor
(VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(1250),
BUSINESS_TYPE_C ODE VARCHAR2(24),
CONTACT_FIRST_N AME VARCHAR2(300),
CONTACT_ID NUMBER,
CONTACT_LAST_NA ME VARCHAR2(300),
CONTACT_MID_NAM E VARCHAR2(300),
CONTACT_TITLE VARCHAR2(300),
STATE_CODE VARCHAR2(32),
ZIP_CODE VARCHAR2(24),
ADDRESS_TEXT VARCHAR2(1250),
CHANGE_DATE DATE,
CITY VARCHAR2(1250),
CONTACT_ADDRESS _TEXT VARCHAR2(1250),
CONTACT_EMAIL VARCHAR2(60),
CONTACT_PREFIX VARCHAR2(150),
CONTACT_STATE_C ODE VARCHAR2(24),
CONTACT_SUFFIX VARCHAR2(150),
CONTACT_ZIP_COD E VARCHAR2(24),
PHONE VARCHAR2(30),
CONTACT_PHONE VARCHAR2(30),
WEBPAGE_URL VARCHAR2(256),
FAX VARCHAR2(30),
TOLL_FREE_PHONE VARCHAR2(30),
EMAIL VARCHAR2(100),
CONTACT_FAX VARCHAR2(30),
U_VENDOR_NAME VARCHAR2(1250),
MEMBERSHIP_LEVE L NUMBER(3),
constraint pk_dw_vendor primary key (vendor_id)
)
nologging
/
Ok we have some work to do here.
are you trying to compare table definitions? or the data within a table for a given row? if it is the data, is it from updated rows or for new information being inserted to be compared to existing information?
as for what you have given, the select statement in the for loop is missing the l_col1 alias and the comma after it.
the execute immediate string is always going to return 0. the two columns will never be the same because you are prefixing them with different strings. it would be the same as saying where 1=2 (which is impossible) therefore nothing is returned, except max() forces oracle to return something (in this case null) and nvl(null,0) is 0.
nvl(max(1),2) is the same as nvl(1,2) since max(1) is always 1 and 1 is never null
i need a better definition of what you are trying to acheive please.
the two global variables being passed in are not being used
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Roel Wuyts |
last post by:
CALL FOR CONTRIBUTIONS
International Workshop on Revival of Dynamic Languages
http://pico.vub.ac.be/~wdmeuter/RDL04/index.html
(at OOPSLA2004, Vancouver, British Columbia, Canada, October 24-28, 200)
Organization committee:
Roel Wuyts (primary contact - roel.wuyts@ulb.ac.be), Gilad Bracha,
Wolfgang De Meuter, Stéphane Ducasse and Oscar Nierstrasz.
|
by: Guinness Mann |
last post by:
When you guys talk about "dynamic SQL," to what exactly are you
referring? Is dynamic SQL anything that isn't a stored procedure?
Specifically, I use ASP.NET to communicate with my SQL Server 2000,
using an SqlConnection object to open the database and an SqlCommand
object to transfer my SQL text to the database.
Is this the "dynamic SQL" that is such a bad thing? What is my
alternative? Wait until after my program is working and...
|
by: Materialised |
last post by:
Hi Everyone,
I apologise if this is covered in the FAQ, I did look, but nothing
actually stood out to me as being relative to my subject.
I want to create a 2 dimensional array, a 'array of strings'. I already
know that no individual string will be longer than 50 characters. I just
don't know before run time how many elements of the array will be needed.
I have heard it is possible to dynamically allocate memory for a 2
|
by: Stephen Gennard |
last post by:
Hello,
I having a problem dynamically invoking a static method that takes a
reference to a SByte*. If I do it directly it works just fine.
Anyone any ideas why?
I have include a example below...
--
|
by: serge |
last post by:
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always
either AND or OR but never mixed together.
We can use Northwind database for my question, it is very similar
to the structure of the problem on the database I am working on.
IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL
DROP TABLE REPORT_SELECTION
| |
by: Pascal Costanza |
last post by:
Dynamic Languages Day @ Vrije Universiteit Brussel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Monday, February 13, 2006, VUB Campus Etterbeek
The VUB (Programming Technology Lab, System and Software Engineering
Lab), ULB (deComp) and the Belgian Association for Dynamic Languages
(BADL) are very pleased to invite you to a whole day of presentations
about the programming languages Self, Smalltalk and Common Lisp by
experts in...
|
by: Mike Livenspargar |
last post by:
We have an application converted from v1.1 Framework to v2.0. The executable
references a class library which in turn has a web reference. The web
reference 'URL Behavior' is set to dynamic. We added an entry to the
executable's .exe.config file to specify the URL, and under the 1.1 framework
this worked well. Unfortunately, this is not working under the 2.0 framework.
I see in the Reference.cs file under the web service reference the...
|
by: Peterwkc |
last post by:
Hello all expert, i have two program which make me desperate bu after i have noticed the forum, my future is become brightness back.
By the way, my problem is like this i the first program was compiled and run without any erros but the second program has a run time error when the function return from allocate and the ptr become NULL. How to fixed this?
Second Program:
/* Best Method to allocate memory for 2D Array because it's
...
|
by: bearophileHUGS |
last post by:
I often use Python to write small programs, in the range of 50-500
lines of code. For example to process some bioinformatics data,
perform some data munging, to apply a randomized optimization
algorithm to solve a certain messy problem, and many different things.
For that I often use several general modules that I have written, like
implementation of certain data structures, and small general "utility"
functions/classes, plus of course...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |