Solved

Question to sdstuber Stored Procedure from previous qestion

Posted on 2011-09-07
8
416 Views
Last Modified: 2012-06-27
CREATE OR REPLACE procedure test_.GEO_PROCEDURE3(p_point in SDO_POINT_TYPE,v_VENUE_CODE IN varchar2)
AS
v_geom SDO_GEOMETRY;
v_sqlstr VARCHAR2(500);
BEGIN
v_geom := SDO_GEOMETRY(2001,8307,p_point, NULL, NULL);
v_sqlstr := 'update CRAFT_TEST1 set CRAFT_GEO_LOCATION = :1 where CODE = :2';
EXECUTE IMMEDIATE v_sqlstr using v_geom, v_VENUE_CODE;
END GEO_PROCEDURE3;
/

Can we modify the procedure to pass below sql output as inpurt parameter to update that particular record. Below query will part of procedure itself using cursor or something

select code, latlng test from craft_test1. this select statement should be  part of procedure
where test_geo_location is null and latlng is not null;

code             test_geo_location  

ATL110645  (33.9581032, -83.373449)

Thanks
0
Comment
Question by:vadicherla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36499929
I still don't know what you want this to do.The question text above looks like the same thing you wrote last time.


>>>> Can we modify the procedure to pass below sql output as inpurt parameter

in what sort of context?    Do you want to call your procedure from within a sql statement?  If so,  no.  You could call a function though, but not a procedure.


What do you mean by passing the sql output?

Do you want to pass the entire query in as a parameter?  Do you want to pass in a reference cursor?  An object type?   etc?


why are you trying to use execute immediate?


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36499934
I think you want to do something like this...
but you'll still have to explain what you mean by "passing the output"


CREATE OR REPLACE PROCEDURE test_.geo_procedure3(
    p_point        IN sdo_point_type,
    v_venue_code   IN VARCHAR2
)
AS
BEGIN
    UPDATE craft_test1
       SET craft_geo_location =
               sdo_geometry(
                   2001,
                   8307,
                   p_point,
                   NULL,
                   NULL
               )
     WHERE code = v_venue_code;
END geo_procedure3;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36499950
I should also note that in the future, calling out an Expert by name means other Experts will likely not respond.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vadicherla
ID: 36500872

From followng sql statement the test_geo_location is not geo column. Some process enters the data into test_geo_location(varchary datatype) using  some perl script.  i have to take output  from below slq and insert into SDO_GEOMEY column which is real goe cloumn using stored procedure.  Thei sql statement should part of procedure

select venue_code, latlng from teamsite_craft_test1 where craft_geo_location is null and latlng is not null'

code             test_geo_location  

ATL110645  (33.9581032, -83.373449)

The output of above query is input for stored procedure to insert to SDO_GEOMETRY column

from test_geo_location (varchar data type)  
to
CRAFT_GEO_LOCATION (GEO Column)

based on code column condition


Thanks
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36501849
you would use the sql results just as you would any other values.
nothing special here



declare
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36501850
you would use the sql results just as you would any other values.
nothing special here



declare
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
0
 

Author Comment

by:vadicherla
ID: 36503267
something like this ??


CREATE OR REPLACE PROCEDURE geo_procedured(v_venue_code IN VARCHAR2,v_point IN sdo_point_type)
AS
begin
     select venue_code, latlng
     into v_venue_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;
      geo_procedure3(v_code,v_point);
end geo_procedured;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36503321
close
your procedure wouldn't have parameters because you're looking up those values
instead it will have local variables
just use the code above but with a procedure name for it


CREATE OR REPLACE PROCEDURE geo_procedured
as
      v_code varchar2(20);
      v_point sdo_point_type;
begin
     select venue_code, latlng
     into v_code,v_point
     from teamsite_craft_test1 where craft_geo_location is null and latlng is not null;

     geo_procedure3(v_point,v_code);
end;
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Table jumps back to row containing the cursor when I release the scroll bar 11 72
Age Calculation from specific date 19 57
oracle query 4 30
When are cursors useful? 8 60
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question