Solved

Question to sdstuber Stored Procedure from previous qestion

Posted on 2011-09-07
8
413 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

808 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