?
Solved

Question to sdstuber Stored Procedure from previous qestion

Posted on 2011-09-07
8
Medium Priority
?
421 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 2000 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
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

 

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 2000 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

752 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