[Solved] oracle sto...
 
Notifications
Clear all

[Solved] oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL

0 Posts
1 Users
0 Likes
8 Views
0
Topic starter
Igor Zhilin Asks: oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL
I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the user.

I use dbeaver as IDE. I know T-SQL and pl/pgsql - but I have no prior knowledge of Oracle PL/SQL.

This is the reason the question is pretty loaded.

The procedure that I have is:

Code:
CREATE OR REPLACE PROCEDURE FIND_TABLE_WITH_COLUMN_VALUE (arg_column_name IN VARCHAR2, arg_column_value IN VARCHAR2) 
IS 
    v_rowcount NUMBER;
    v_sql_statement VARCHAR2(4000);
BEGIN
    FOR L IN (
        SELECT 
            OWNER || '.' || TABLE_NAME AS OWNER_TABLE,
                'BEGIN 
                    SELECT 1 FROM ' || OWNER || '.' || TABLE_NAME || ' 
                    WHERE ' || COLUMN_NAME || '=''' || arg_column_value || ''';
                    :0:=SQL%ROWCOUNT;
                END;
                ' AS SQL_STATEMENT
        FROM ALL_TAB_COLUMNS 
        WHERE 1=1
            AND COLUMN_NAME = arg_column_name
    )
    LOOP 
        v_sql_statement := L.SQL_STATEMENT;
        EXECUTE IMMEDIATE v_sql_statement USING OUT v_rowcount;
        DBMS_OUTPUT.put_line(L.OWNER_TABLE);
        DBMS_OUTPUT.put_line(v_rowcount);
    END LOOP;
END;
/

And the errors:

Code:
SQL Error [6550] [65000]: ORA-06550: line 14, column 22:
PL/SQL: ORA-00904: "ARG_COLUMN_NAME": invalid identifier
ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 3:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 17, column 3:
PL/SQL: Statement ignored
ORA-06550: line 18, column 21:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 18, column 3:
PL/SQL: Statement ignored
ORA-06550: line 19, column 24:
PLS-00201: identifier 'V_SQL_STATEMENT' must be declared
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored
ORA-06550: line 20, column 24:
PLS-00201: identifier 'V_ROWCOUNT' must be declared
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

How is that possible?

  1. "ARG_COLUMN_NAME": invalid identifier - is this not the function argument that I specified?
  2. identifier 'V_SQL_STATEMENT' must be declared - is it not already specified in the IS part of the stored proc?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

Unreplied Posts

[Solved] Metapost: variable name with number at the end

Vladimir Asks: Metapost: variable name with number at the end
Why can’t I use variable names like a1, str2, x12? Does Metapost consider them as arrays so that a1 is like a[1] and so on? Even z-notation as z1, z2 and etc. does not work.

The code is

Code:
beginfig(1);
# := 5mm;
pair z1, z2, z3, z4;
z1 = (10#, #);
z2 = (#, 12#);
z3 = origin;
z4 = (11#, 13#);
draw z1--z2;
draw z3--z4;
endfig;
end;
bye.

The error is as follows:

Code:
! Illegal suffix of declared variable will be flushed.
<to be read again>
                   1
l.153 pair z1
             , z2, z3, z4;
?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.
 
 
 

[Solved] Get Particular Text only Need to Print

Balaji Asks: Get Particular Text only Need to Print
I have more content text in my XML and i have need to print particular content text only. How to do?

My MWE is:

Code:
documentclass{book}
usepackage{luacode}

begin{document}

begin{luacode*}
local domobject = require "luaxml-domobject"
local transform = require "luaxml-transform"
sample = [[
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE article PUBLIC "-//NLM//DTD Journal Publishing DTD v2.3 20070202//EN" "journalpublishing.dtd">
<article article-type="research-article" dtd-version="2.3" xml:lang="EN" xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">
<front>
<title-group>
<article-title>Eulerian Phase Transport</article-title>
<alt-title>Eulerian Phase Transport</alt-title>
</title-group>
<contrib-group>
<contrib contrib-type="author"><name><surname>Bonc</surname><given-names>Drea</given-names></name></contrib>
</contrib-group>
<history>
<received>20 June 2010</received>
<accepted>15 July 2010</accepted>
</history>
</front></article>]]
local dom = domobject.parse(sample)

local get_content = function(e)
    local authors = {}
        for _, contrib in ipairs(e:query_selector("contrib")) do
            local elink = contrib:query_selector("ext-link")
                if not(elink[1] == nil) then
                    orcid=elink[1]:get_text()
                    local fnm = (contrib:query_selector("name given-names")[1]):get_text() or ""
                    local snm = (contrib:query_selector("name surname")[1]):get_text() or ""
                    table.insert(authors, string.format("\author{%s %s}\ \aff{%s}", fnm, snm, orcid))
                end
        end
    return table.concat(authors, "")
end


local converted = transform.process_dom(dom)
print(converted)
transform.print_tex(converted)


end{luacode*}
end{document}

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Order resultset by configured value and then list all the rest

MarkW Asks: Order resultset by configured value and then list all the rest
I want to list jobs (jobs are custom post types) in country order. The country to prioritise in the resultset is defined by the user in the WP CMS. With the configured country being listed first I then want to control the ordering of the remaining rows.

e.g. if they select ‘gb’ I want the resultset to look something like this:

Code:
gb 
gb 
au 
es 
fr 
sw 
sw

I need to use pre_get_posts to achieve this. Hopefully that all makes sense.

Here is what I have just now. I’ve hard-coded the key/value for readability rather than display the CMS classes / values.

Code:
        add_action(
            'pre_get_posts',
            function(WP_Query $query) {
                if ($query->is_main_query()) {
                    $country_order = array(
                        'meta_query' => array(
                            array (
                                'key' => 'job_country',
                                'value' => 'gb',
                                'compare' => '=' 
                            )
                        )
                    );
                    $query->set('meta_query', $country_order);
                }
            }
        );

This returns only ‘gb’ and none of the others and I’m a bit stuck as to where to go from here. Any help will be much appreciated.

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Filter taxonomy by CPT

Nando Merino Asks: Filter taxonomy by CPT
I have the following code that shows all the categories within the “category” taxonomy, it works perfectly but I need to filter so that it shows only the categories of a certain type of CPT

Code:
function show_categories_opos() {
 $args = array(
           'taxonomy' => 'especialidad-oposicion',
           'orderby' => 'name',
           'order'   => 'ASC'
       );

$cats = get_categories($args);

?>
<div id="convocatorias">
<?php
foreach($cats as $cat) {
$category_link = sprintf( 
    '<a href="%1$s" alt="%2$s">Ver más <i class="fas fa-angle-right"></i></a>',
    esc_url( get_category_link( $cat->term_id ) ),
    esc_attr( sprintf( __( 'View all posts in %s', 'textdomain' ), $cat->name ) )
);
?>
<div class="fila"><div class="columna"><?php echo $cat->name; ?></div><div class="columna"><?php echo $category_link; ?></div></div>
 <?php
 }
 ?>
 </div>
 <?php
 }

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Reading shapefile using C#? [closed]

DevRaj Asks: Reading shapefile using C#? [closed]
I am new to ArcGIS and trying a few things..

  1. I’m trying to read shapefile (which is supplied as an input) using C# and

  2. I want to generate a PDF file which contains underlying metadata information (similar to the Identify tool) ?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Calculating length of common boundary lines of two polygon in QGIS

user3289662 Asks: Calculating length of common boundary lines of two polygon in QGIS
I have a map consisting various district. How can I estimate the length of the common boundary shared by any two corresponding districts/polygons in QGIS?

For example if a polygon “A” shares a common boundary with polygons, “B”, “C” and “D”. Then what would be the length of the boundary between polygon “A-B”, “A-C” and “A-D”?

I have calculated the neighboring polygon using the following in field calculator

Code:
aggregate( 
  layer:='file_districts',
  aggregate:='concatenate',
  expression:= DIST_ID,
  concatenator:=', ',
  filter:=touches($geometry, geometry(@parent))
)

Now i want to estimate the length of each of the neighbor polygon.


example

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Change QGIS 3 shortcut keys for layout view / print composer

Comrade Che Asks: Change QGIS 3 shortcut keys for layout view / print composer
I wanted to change the keyboard shortcuts in the QGIS 3 layout view / print composer, but I cannot find any buttons from this section, for example: “New layout – Ctrl + N”.

How to change it?


enter image description here


enter image description here

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Joining and Updating PostGIS data using QGIS

Jamie Asks: Joining and Updating PostGIS data using QGIS
I have a PostGIS database with which I am currently using QGIS to manage the layers. Say I have a layer in PostGIS that I want to do a join to to add new data in. Is it possible to do this join in-situ without having to download the layer locally, do the join and re-upload the layer, overwriting the previous one?

If this isn’t possible, is the best alternative to write a script to handle the downloading, joining and re-uploading all together?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Mask raster with bounding box from a different CRS

Erik Asks: Mask raster with bounding box from a different CRS
My apologies if this question has been asked before; but I could not find my specific question answered elsewhere.

I define a bounding box with bounds (xmin,xmax, ymin, ymax) in a rotated lon/lat system. If I’d supersample the points along this bounding box and transform the coordinates to a regular WGS84 system, it becomes clear (as you would expect for a rotated grid) that the bounding box ceases to be a box, and instead takes on a curved form in the other CRS:


rotated grid

Now my problem is the following: I want to mask (e.g., with rasterio) a dataset (given in regular lon/lat coordinates) with my given bounding box (given in rotated lon/lat coordinates).

A simple, but wrong, solution is to transform the bounding box coordinates to the regular lon/lat coordinates; as rasterio will then assume a straight line between the points of the polygon, i.e., it will mask following the red lines in the image below. So, the following is not the desired behavior (corner points are preserved correctly, edges are straight but should be curved!):


rotated grid wrong cut

One solution is to reproject my entire dataset into the rotated coordinate system. This is, however, not really the cheapest operation (for something I’ll have to do many times over, and want to make reasonably interactive). Another solution is to do as written above, i.e., supersample the points along the bounding box, transform each of those points to the other CRS, and mask along the supersampled bounding box points. This can also get quite expensive, and it’s hard to define when the curved cells are appropriately captured by the supersampling.

So I wonder if another clean solution exists.

BTW, the standard cropping/masking code is this

Code:
import rasterio
from rasterio.mask import mask

IMAGE_path = '....tif'
POL = ...
with rasterio.open(IMAGE_path) as src:
    cropped_image, _ = mask(src, 
                            POL, 
                            nodata=0,
                            crop=True,
                            all_touched=True)
    return cropped_image

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Mode Spatial Filter

N. Ahmadi sani Asks: Mode Spatial Filter
I want to run a Mode filter (for example: 3*3 pixel) on the classification result in Google Earth Engine. But I can not find the command of this filter.

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

Share: